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

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

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

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

1. Ознакомиться с текстовым форматом ячеек.

2. Используя лекционный материал, любые печатные издания и пособия по EXCEL, а также справочную систему самого EXCEL, изучить назначение и действие текстовых функций. Выписать в лекционную тетрадь (распечатать) все функции для работы с текстом - 23 штуки: название функции, список аргументов и получаемый результат. Привести примеры работы этих функций.

3. Изучить правила создания числовых пользовательских форматов и коды для их построения.

4. Изучить правила создания имен для группы ячеек и способы использования этих имен.

5. Изучить возможности EXCEL для связывания данных в разных таблицах, а также работу функции ВПР

6. Ознакомиться с правилами и возможностями сортировки числовых и текстовых данных.

7. Изучить назначение и действие логических функций: И, ИЛИ, ЕСЛИ. Выписать в лекционную тетрадь правила записи логических функций и примеры их использования.

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

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

9. На первом листе рабочей книги создать справочник № 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	Индекс	Ин: ЦЦ { ЦЦ }	Название красителя

10. Наложить защиту на данные справочника № 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 руб