ID: 72408

Название работы: Работа с текстовыми данными

Категория: Лабораторная работа

Предметная область: Информатика, кибернетика и программирование

Описание: Цель работы: изучить основные сведения по работе с текстовыми данными, научиться обрабатывать текст. Уметь связывать данные в разных таблицах, освоить работу логических функций. Изучить пользовательские форматы данных. Научиться использовать средство Условное форматирование для выделения диапазона данных.

Язык: Русский

Дата добавления: 2014-11-22

Размер файла: 180.5 KB

Работу скачали: 2 чел.

Лабораторная работа № 3.

Работа с текстовыми данными.

Цель работы: изучить основные сведения по работе с текстовыми данными, научиться обрабатывать текст. Уметь связывать данные в разных таблицах, освоить работу логических функций. Изучить пользовательские форматы данных. Научиться использовать средство Условное форматирование для выделения диапазона данных. Освоить работу с поименованными областями. Изучить способы сокрытия и защиты данных.

Задание (теоретическая часть):

1.  Ознакомиться с текстовым форматом ячеек.
2.  Используя лекционный материал, любые печатные  издания и пособия по EXCEL, а также справочную систему самого EXCEL, изучить назначение и действие текстовых функций.  Выписать в лекционную тетрадь (распечатать) все функции для работы с текстом - 23 штуки: название функции, список аргументов и получаемый результат. Привести примеры работы этих функций.
3.  Изучить правила создания числовых пользовательских форматов и коды для их построения.
4.  Изучить правила создания имен для группы ячеек и способы использования этих имен.
5.  Изучить возможности EXCEL для связывания данных в разных таблицах, а также работу функции ВПР 
6.  Ознакомиться с правилами и возможностями сортировки числовых и текстовых данных.
7.  Изучить назначение и действие логических функций: И, ИЛИ, ЕСЛИ. Выписать в лекционную тетрадь правила записи логических функций и примеры их использования.
8.  Изучить способы сокрытия и защиты данных. Освоить защиту одной ячейки, рабочего листа и книги целиком. Ознакомиться со способом сокрытия формул в таблице.

Задание (практическая часть: 4 таблицы – 1 справочник и 3 связанные с ним таблицы, 2 графика):

1.  На первом листе рабочей книги создать справочник № 1 в соответствии с предложенным заданием по номеру в списке.

Общие требования к справочнику № 1.

В первом столбце справочника должны быть размещены уникальные данные в соответствии с представленным в задании кодом (Ц – цифра в коде). Для этого необходимо использовать пользовательский формат данных. Для всего справочника следует создать поименованные области (для правильной работы функции ВПР). Количество строк в справочнике должно быть не менее 7.

Задание для справочника № 1.

№ вар	1-я колонка		2 колонка
		Название колонки с кодом		Формат кода
1	Код менеджера	Ц  ЦЦ (Ц)	Обслуживающий менеджер
2	№ фирмы	№ Ц/ЦЦ  Ц	Наименование фирмы-поставщика
3	Код пряжи	ЦЦ(тк. ЦЦ))	Название пряжи
4	Индекс	: Ц—ЦЦ(Ц)	Полное наименование ткацкого оборудования
5	№ фирмы	ЦЦ—ЦЦ	Фирма - покупатель
6	Артикул	ЦЦ/ЦЦ)	Наименование трикотажа
7	Код модели	(Ц) ЦЦ № Ц	Название модели
8	Код фасона	Ц  /ЦЦ/  Ц	Фасон обуви
9	Индекс ткани	Ц/Ц - ЦЦ	Категория ткани
10	Артикул ткани	Ц-ЦЦ/Ц	Название ткани
11	Код	ЦЦ{Ц}  (Ц)	Состав пряжи
12	Личный номер мастера	[Ц]  ЦЦЦ	Старший мастер в смене
13	Код станка	Ц—ЦЦ—Ц	Название станка
14	Код фирмы	: Ц : Ц х ЦЦ	Наименование фирмы-посредника
15	Код цвета	ЦЦЦ/от.ЦЦ	Наименование цвета пряжи
16	Артикул	Ар. Ц  (ЦЦЦ)	Наименование кожи
17	Код изделия	(ЦЦ)  Ц/Ц	Название изделия
18	Личный код	ЦЦ [ ЦЦ ]	Контролер - браковщик
19	Код по каталогу	Ц — ЦЦ — Ц)	Модель по каталогу
20	Индекс	Ин: ЦЦ { ЦЦ }	Название красителя

1.  Наложить защиту на данные справочника № 1 таким образом, что бы никто кроме автора этой таблицы не смог внести никакие изменения в таблицу.

Задание для таблицы № 2.

№ п/п	Текст			Коды	Данные из справ. № 1 (функция ВПР)	Число	Число	Расчетный столбец и формула для его расчетов
1	Ф	И	О	Код менеджера:	Обслуживающий менеджер	Количество изделий	Стоимость изделия	Стоимость всей партии	Количество * стоимость изделия +5% для менеджера Иванова, +7% для остальных менеджеров
2	Ф	И	О	№ фирмы	Фирма-поставщик	Стоимость заказа	Сумма предоплаты	Остаток суммы за заказ	Стоимость заказа + предоплата +500 руб для предоплаты > 50%, +100 руб. для всех остальных
3	Ф	И	О	Код пряжи	Название пряжи	Количество бобин	Вес бобины	Стоимость партии пряжи	Кол-во*вес *250 руб, если вес < 1 кг, *300 руб – если вес >= 1 кг.
4	Ф	И	О	Индекс оборудования	Наименование оборудования	Изделий за смену	Количество смен за месяц	Всего изделий за месяц	Изделия*смены +3% если > 15 смен +5% если <= 15 смен
5	Ф	И	О	№ фирмы	Фирма - покупатель	Стоимость фурнитуры	Цена ткани	Стоимость заказа	Цена ткани + фурнитура +100 руб, если фурнитура дешевле ткани, прочее:  ткань + фурнитура
6	Ф	И	О	Артикул трикотажа	Наименование трикотажа	Стоимость пряжи	Стоимость изготовления	Стоимость изделия	Пряжа+изготовление +200 руб при ст-ти изгот-ия < 350 руб, +300 руб  в остальных случаях
7	Ф	И	О	Код модели	Название модели	Вес пряжи в изделии	Стоимость 1 кг пряжи	Стоимость изделия	Вес*стоимость +30% для веса пряжи < 400 гр., +50% остальное
8	Ф	И	О	Код фасона	Фасон обуви	Стоимость раскроя	Количество пар в заказе	Стоимость раскроя партии	Стоимость* количество +2000 руб для сапог, +500 руб – все остальное
9	Ф	И	О	Индекс ткани	Состав ткани	Метров в рулоне	Количество рулонов	Общий метраж ткани на складе	Метраж*рулоны +1метр, если < 3 рулонов, +5 метров - все остальное
10	Ф	И	О	Артикул ткани	Название ткани	Метраж на 1 изделие	Стоимость 1 метра ткани	Стоимость ткани на 1 изделие	Метраж*стоимость +500 руб., если шелк, +300 руб  все остальное.
11	Ф	И	О	Код состава	Состав пряжи	Количество мотков	Стоимость 1-го мотка	Общая стоимость партии пряжи	Количество*стоимость +200 руб., если < 10 мотков, +400 руб. – все остальное
12	Ф	И	О	Личный номер мастера	Старший мастер в смене	Вывязано изделий	Вес одного изделия	Расход пряжи  за смену	Изделия* вес +3%, если ст. мастер – Иванов, +5% для остальных мастеров
13	Ф	И	О	Код станка	Название станка	Стоимость станка	Количество станков	Стоимость оборуд-ия	Стоимость* кол-во +10%, если кол-во станков< 3, +5% - все остальные
14	Ф	И	О	Код фирмы	Наименование фирмы-посредника	Изделий в заказе	Стоимость изделия	Стоимость заказа	Количество*стоимость +10% для фирмы “Наши деньги”, +30%- все остальные фирмы.
15	Ф	И	О	Код цвета	Наименование цвета пряжи	Стоимость мотка	Количество мотков	Стоимость всей пряжи в партии	< 50 мотков  - кол-во*(стоим.+10 руб.), прочие – кол-во*(стоимость+5 руб).
16	Ф	И	О	Артикул кожи	Наименование кожи	Объем в м2  партии	Стоимость 1-го м2 кожи	Стоимость поставки кожи	Объем*стоимость +10% для замши, +5% - все остальное
17	Ф	И	О	Код изделия	Название изделия	Вес изделия в граммах	Стоимость материала  за 1 кг	Себестоимость изделия	(Вес+20%)* стоимость – для свитеров (Вес+10%)* стоимость – все остальное
18	Ф	И	О	Личный код	Контролер - браковщик	Количество брака	Цена 1 упаковки	Стоимость отбракованного	Количество*(цену+10 руб.), для Петрова, остальные –Количество*(цену+15 руб.)
19	Ф	И	О	Код по каталогу	Название модели по каталогу	Стоимость модели	Стоимость доставки	Стоимость заказа	Стоимость модели+доставка +10% если цена мод.< цены дост. +5% если цена мод.>= цены дост.
20	Ф	И	О	Индекс красителя	Название красителя	Количество упаковок	Вес упаковки	Стоимость  партии	., Для краситель акриловый –Вес*количество*600 руб, прочее – Вес*количество*800 руб

1.  На втором листе рабочей книги создать развернутую таблицу № 2 в соответствии с заданием.

Общие требования к таблице № 2. Фамилии, имена и отчества следует указывать полностью. В качестве исходных данных можно взять список своей группы. Коды в столбец № 5 следует вносить вручную. Столбцы № 5 и 6 (данные из справочника № 1) должны быть связаны со справочником № 1 при помощи функции ВПР таким образом, что бы при изменении кода в столбце № 5, автоматически изменялись бы и данные в столбце № 6. Расчетный столбец таблицы должен заполняться при помощи функции ЕСЛИ. Данные для двух столбцов с числовыми данными должны быть представлены при помощи числового пользовательского формата, содержащего обязательный для каждой ячейки текст, например: 10 штук, 256 рублей, 34 $USA (числа и формат ячеек придумать самостоятельно в соответствии с заданием).

Количество строк в таблице должно быть не менее 20. Данные из справочника № 1 должны повторяться для нескольких строк таблицы № 2.

1.  В таблице № 2 скрыть расчетные формулы.
2.  При помощи средства Условное форматирование выделить целиком фамилию, имя и отчество по двум представленным ниже условиям. При условном форматировании можно использовать дополнительный столбец, который затем следует скрыть.
3.  Скопировав таблицу № 2, на третий лист, преобразовать ее в итоговую таблицу № 3.

Общие требования к таблице № 3. Три столбца с фамилией именем и отчеством заменить на один столбец в формате Фамилия И.О. Этот столбец должен быть связан с данными из таблицы № 2 таким образом, что бы любые изменения в анкетных данных приводили бы к автоматическому изменению данных в таблице № 3. Удалить из таблицы все столбцы с исходными числовыми данными, оставив один лишь расчетный столбец. Отсортировать данные в таблице по расчетному столбцу.

1.  На основании таблицы № 3 построить круговую диаграмму на отдельном листе, на диаграмме обязательно вывести фамилии с инициалами и расчетные данные.

Задание на Условное форматирование для таблицы № 2.

№ вар	Условие № 1	Условие № 2
1	наличие в фамилии буквы а или и	имя длиннее отчества
2	фамилия оканчивается на -ев	имя и отчество начинаются с одной буквы
3	фамилия короче отчества	отчество оканчивается на -еевич
4	в фамилии есть буква с	имя длиннее фамилии
5	длина фамилии более 9 символов	фамилия содержит две буквы е
6	наличие в фамилии буквы о	длина отчества более 10 символов
7	имя и фамилия начинаются с одной буквы	отчество заканчивается -на
8	фамилия начинается с гласной буквы	длина имени менее 7 символов
9	в фамилии есть буква ш	имя начинается с гласной буквы
10	фамилия оканчивается на -ов	фамилия короче имени
11	в фамилии есть буква т	имя заканчивается на -ина
12	первая буква  совпадает с последней	отчество длиннее имени на две буквы
13	фамилия начинается с буквы А,Б или В	длина фамилии более 8 символов
14	в фамилии есть буква р	отчество заканчивается на -евна
15	фамилия оканчивается на -на	длина отчества менее 10 символов
16	фамилия начинается с буквы М,Н или О	отчество короче фамилии
17	длина фамилии более 7 символов	отчество начинается с гласной буквы
18	в фамилии есть буква л	первые буквы фамилии и отчества одинаковы
19	фамилия оканчивается на -ин	отчество содержит две буквы е
20	фамилия начинается с буквы Р,С или Т	длина имени более 8 символов

1.  Используя данные справочника № 1 и таблицы № 2, на пятом листе создать таблицу № 4 (смотри задание ниже).

Общие требования к таблице № 4. При вычислениях в таблице № 4 должны использоваться функции СЧЕТЕСЛИ и СУММЕСЛИ. При изменении данных в исходных таблицах, должны изменяться данные и в таблице № 4. Количество строк в таблице № 4 должно совпадать с количеством строк в справочнике № 1.

1.  По данным таблицы № 4 создать гистограмму на отдельном листе. Вывести на гистограмму текстовые данные (из столбца № 1) и подписи данных (из столбца № 2).
2.  Общие требования к внешнему виду таблиц и диаграмм взять из лабораторной работы № 2. Дать созданным таблицам названия, соответствующие хранимым в них данным. Ячейки таблицы должны иметь пользовательский формат, соответствующий формату хранимой в ней информации. Все таблицы должны быть аккуратно и грамотно оформлены и радовать глаз, как самого студента, так и преподавателя.

Задание для таблицы № 4.

№ вар	Содержание столбцов
1	Обслуживающий менеджер	Количество всех партий для менеджера	Стоимость всех партий для менеджера
2	Фирма-поставщик	Количество заказов для фирмы	Стоимость заказов для фирмы
3	Код пряжи	Количество  партии  этой пряжи	Общий вес партии  этой пряжи
4	Наименование оборудования	Всего смен за  месяц на этом оборудовании	Всего изделий за месяц на этом оборудовании
5	Фирма - покупатель	Количество заказов для этой  для фирмы	Стоимость фурнитуры в заказах для фирмы
6	Артикул трикотажа	Количество заказов по этому артикулу	Количество изделий по артикулу
7	Название модели	Общее количество заказов по изделию	Общее количество  изделий
8	Код фасона обуви	Общее количество заказов по коду обуви	Общее количество пар обуви
9	Индекс ткани	Количество рулонов ткани на складе	Общий метраж ткани на складе
10	Артикул ткани	Количество рассчитанных изделий	Стоимость рассчитанных изделий
11	Состав пряжи	Количество мотков пряжи на складе	Стоимость пряжи на складе
12	Старший мастер в смене	Общее количество смен	Общее количество вывязанных изделий
13	Код станка	Общее количество станков в цеху	Стоимость  станков в цеху
14	Наименование фирмы-посредника	Количество заказов через эту фирму	Стоимость заказов через эту фирму
15	Наименование цвета пряжи	Количество заказов этой пряжи	Стоимость пряжи
16	Наименование кожи	Количество  партии кожи	Общая стоимость партии кожи
17	Код изделия	Количество заказов этого  изделия	Общая себестомость изделий
18	Контролер - браковщик	Количество отбракованого	Стоимость отбракованого
19	Название модели по каталогу	Количество  заказанных моделей	Общая стоимость заказанных моделей
20	Название красителя	Количество упаковок  заказанного красителя	Общий вес заказанного красителя

КОНТРОЛЬНЫЕ ВОПРОСЫ (пока не все!!!):

1.  Как обрабатывается текст в EXCEL?
2.  В каком формате следует хранить текстовые данные и почему?
3.  Чем отличается текстовый формат от общего?
4.  Какие символы являются признаками текстовых данных в EXCEL?
5.  Чем отличается справочник от обычной таблицы?
6.  Как присвоить группе ячеек имя? Зачем это нужно?
7.  Как посмотреть уже существующие имена для диапазонов ячеек?
8.  Как вставить в формулу или функцию имя диапазона?
9.  Зачем нужны пользовательские форматы?
10.  Для каких данных применяется пользовательский формат?
11.  Какие коды используются в пользовательском формате?
12.  Зачем нужна сортировка данных?
13.  Как сравниваются текстовые данные в EXCEL при сравнении и при сортировке?
14.  Различаются ли при сортировке заглавные и прописные английские буквы?
15.  Различаются ли при сортировке заглавные и прописные русские буквы?
16.  Зависит ли сортировка текстовых данных от выбранного шрифта, его начертания и жирности?
17.  Как подсчитать длину строки текста в ячейке?
18.  Как удалить из строки текста все ненужные пробелы?
19.  Каким образом можно объединить несколько текстовых данных из разных ячеек в одну?
20.  Можно ли перевести текст, записанный заглавными русскими буквами в прописные и наоборот?
21.  Как вырезать и скопировать в другую ячейку первое слово из строки теста?
22.  Как подсчитать количество вхождение в строку буквы ”Л”?
23.  Как оставить от исходного слова одну первую букву и добавить к ней точку?
24.  Зачем нужна защита ячеек?
25.  Какие виды защиты (степени защиты) существуют в EXCEL?
26.  Как защитить одну ячейку в таблице? Как защитить всю таблицу, весь лист, всю книгу?
27.  А как потом снять эту защиту?
28.  Зачем нужно скрывать формулы в таблице?
29.  Каким образом можно спрятать формулы в таблице?
30.  Как можно вернуть спрятанные формулы обратно?
31.  Что можно сделать, если на книгу была наложена защита, а пароль был утерян?
32.  Что такое логические функции?
33.  Какие значения могут принимать логические функции?
34.  Какие логические функции используются в EXCEL?
35.  Как объединить два различных условия в одно, что бы работало хотя бы одно из них?
36.  Как объединить два различных условия в одно, что бы проверялось выполнение сразу двух условий?
37.  Как работает логическая функция ЕСЛИ?
38.  Как быстро легко и правильно написать функцию ЕСЛИ, вложенную в другую функцию ЕСЛИ?
39.  Зачем нужно связывать между собой данные из разных таблиц?
40.  Каким образом связываются данные, находящиеся в различных таблицах?
41.  Как вставить в ячейку исходные данные, расположенные в другой таблице. А в другой книге?
42.  Зачем нужны функции ВПР и ГПР? Как они работают?
43.  Зачем в функции ВПР нужен последний параметр – ложь или истина?
44.  А что будет, если этот последний параметр в функции ВПР не указать?
45.  Зачем нужны функции СУММЕСЛИ и СЧЕТЕСЛИ?
46.  Как работают функции СУММЕСЛИ и СЧЕТЕСЛИ?
47.  Зачем нужна опция формула в Условном форматировании?
48.  Как при Условном форматировании сослаться на содержимое (значение) те той ячейки, на которую накладывается это условное форматирование, а на данные из другой ячейке (или нескольких ячеек)?
49.  Как использовать средство Условное форматирование для выделения не одной ячейки, а нескольких ячеек в строке или целой строки в таблице?
50.  Как скрыть столбец или строку? А как потом показать скрытый столбец или строку?
51.  Как можно узнать, есть ли в таблице скрытые столбцы или строки?
52.  Как на круговой диаграмме можно вывести текстовые данные, например фамилии сотрудников?
53.  Как на круговой диаграмме можно вывести числовые данные?
54.  Как на гистограмме можно подписать числовые данные над каждым столбцом?