Контрольная работа по «Информационные технологии в экономике»

После выбора функции выполните щелчок на кнопке Далее для перехода в следующее диалоговое окно Мастера функций, в котором должны быть заданы аргументы. В этом диалоговом окне программа информирует пользователя о том, какие аргументы должны быть указаны обязательно (обязательные аргументы), а какие — нет. По завершении ввода аргументов следует нажать кнопку Готово, и результат вычисления будет представлен в соответствующей ячейке. Теперь перейдем от теории к практике. В столбце Е должно быть указано наименование товара. Вряд ли Вы откажетесь от автоматической вставки данных с помощью формулы. Для этого введите в ячейку Е2 формулу:

= ЕСЛИ($D2=""; ""; ПРОСМОТР ($D2; Номер; Товар)

Думаем, что приведенная формула  требует небольших пояснений. Функция  ЕСЛИ обеспечивает проверку содержимого ячейки D2. Если в ячейке  D2 данные отсутствуют, то ячейка Е2 также останется незаполненной. Если ячейка D2 содержит номер товара, произойдет поиск номера товара в диапазоне Номер, а в ячейку D2 будет занесено соответствующее значение диапазона Товар. Для этого мы  и используем функцию  ПРОСМОТР.   Вставьте данную формулу в ячейку Е2 с помощью Мастера функций (для активизации Мастера  функций воспользуйтесь одноименной кнопкой в панели инструментов Стандартная), если Вы не уверены, что аргументы в ячейке указаны правильно. Обратите внимание, что для ячейки D2 задана комбинированная ссылка, благодаря которой при копировании формулы будет изменен номер строки.

Столбец F предназначен для указания количества товара, заказанного клиентом, а столбец G (поле Цена за ед.) — для указания цены единицы товара. Значения цен можно вставить с помощью формулы, аналогичной формуле в ячейке Е2. Формула в ячейке G2 должна иметь такой вид:

= ЕСЛИ($D2=""; ""; ПРОСМОТР ($D2; Номер; Цена)

Целесообразно скопировать формулу  из ячейки Е2 в ячейку G2 и затем только изменить имя диапазона. Значение (пробел), представленное в ячейке как результат применения формулы, убедит Вас в ее правильности. Наверное, Вы уже заметили, что иногда при указании адреса ячейки в ссылке используется символ $. О том, в каких случаях следует применять данный символ, читайте в следующих строках.

Копирование формул. При копировании формул содержащиеся в них ссылки автоматически заменяются новыми только в том случае, если они являются относительными. По умолчанию в Excel применяются относительные ссылки. Они используются в формулах для указания адреса ячейки относительно позиции ячейки, содержащей формулу. При изменении позиции формулы относительные ссылки будут содержать адреса других ячеек. Если при перемещении или копировании формулы адреса ячеек не должны изменяться, следует использовать абсолютные ссылки. Абсолютную  ссылку можно установить, задав имя или вставив символ $ в адрес ячейки (например $А$1). Можно создать комбинацию  абсолютной и относительной ссылок, например $А1. В этом случае при копировании формулы в ссылке изменяется только строка, а столбец все время остается тем же. Итак, продолжим выполнять наш пример. В ячейке Н2 следует указать код фирмы-заказчика. Код придется ввести с клавиатуры, поскольку невозможно предвидеть, какой будет очередность заказов, и задать автоматическое (с помощью одной из формул Excel) заполнение ячеек этого столбца. Автоматически мы заполним ячейки столбцов Название фирмы и Скидка. Теперь анализируемой ячейкой будет ячейка Н2. Введите в ячейку I2 формулу:

= ЕСЛИ($Н2=""; ""; ПРОСМОТР ($Н2; Код; Фирма)

В столбце Сумма заказа следует указать общую стоимость заказа без учета скидок. Для этого умножьте значения в полях Количество и Цена. С помощью логической функции  ЕСЛИ  можно задать отмену заполнения ячеек в том случае, если запись не введена, что позволит избежать появления значений ошибки. Таким образом, формула в ячейке J2 должна иметь следующий вид:

= ЕСЛИ(F2=""; ""; F2*G2)

Если в ячейке  F2 указано количество единиц заказываемого товара, то ячейка J2 должна содержать произведение значений ячеек F2 и G2. В противном случае она должна остаться незаполненной.

Величину  скидки (столбец Скидка) также можно определять автоматически. Для этого достаточно ввести в ячейку К2 формулу:

= ЕСЛИ($Н2=""; ""; ПРОСМОТР ($Н2; Код; Скидка)

В завершение осталось определить сумму, подлежащую выплате. Для этого введите в ячейку L2 следующую формулу:

= ЕСЛИ($J2=""; ""; J2-J2*K2)

Значительная часть работы по заполнению таблицы уже сделана. Осталось выполнить форматирование: центрировать значения, назначить стили (денежный и процентный), а также заполнить ячейки, в которые должны  быть введены значения, голубым цветом, а ячейки, в которых значения будут определены на основе формул, — желтым. Выделите ячейки  В2— L2 и выберите в меню Правка команду Заполнить/Вниз. Таким образом мы задали копирование значений ячеек сроки 2 в остальные ячейки. Теперь все готово к вводу записей. Введите несколько записей и проверьте правильность определения значений. После ввода нескольких записей для первых трех месяцев (дней, недель, лет) может возникнуть необходимость подвергнуть данные  списка фильтрации, например, чтобы определить, какой клиент пользовался услугами фирмы наиболее часто или насколько популярным был тот или иной товар. Используйте для этого функцию автоматической фильтрации. После вставки автофильтра выбор нужных записей не составит труда.

Месяц	Дата	Номер заказа	Номер товара	Наименование  товара	Количество	Цена  за ед.	Код заказчика	Название  фирмы	Сумма заказа	Скидка	Уплачено
Январь	02.01.98	3601	101	юбка	10	$120.00	2001	карусель ООО	$1 200.00	5.00%	$1 140.00
Январь	03.01.98	3602	202	пинджак	20	$800.00	2002	Магнит ООО	$16 000.00	5.00%	$15 200.00
Январь	04.01.98	3603	101	юбка	15	$120.00	2201	Магнит ЗАО	$1 800.00	0.00%	$1 800.00
Январь	05.01.98	3604	201	жакет	30	$500.00	2301	Центальный ООО	$15 000.00	0.00%	$15 000.00
Январь	06.01.98	3605	103	платье	10	$780.00	2302	Молодежный ООО	$7 800.00	0.00%	$7 800.00
Январь	07.01.98	3606	301	толстовка	30	$150.00	2401	Вершина ООО	$4 500.00	2.00%	$4 410.00
Январь	08.01.98	3607	102	брюки	30	$1 150.00	2402	Пульс ЗАО	$34 500.00	2.00%	$33 810.00
Январь	09.01.98	3608	204	плащ	40	$1 460.00	3101	Премьер ООО	$58 400.00	2.00%	$57 232.00
Февраль	16.02.98	3609	104	костюм	15	$1 819.00	3102	Чеховский ООО	$27 285.00	2.00%	$26 739.30
Февраль	17.02.98	3610	203	бриджи	20	$299.00	3103	Велюр ООО	$5 980.00	3.00%	$5 800.60
Февраль	18.02.98	3611	302	футболка	10	$50.00	3201	Формат ООО	$500.00	0.00%	$500.00
Февраль	19.02.98	3612	303	майка	10	$30.00	3202	Квадрат ООО	$300.00	2.00%	$294.00
Февраль	20.02.98	3613	102	брюки	20	$1 150.00	3202	Квадрат ООО	$23 000.00	2.00%	$22 540.00
Февраль	21.02.98	3614	103	платье	10	$780.00	3202	Квадрат ООО	$7 800.00	2.00%	$7 644.00

 

Управление данными в Excel

(Занятие № 3)

Создание Бланка заказа

 Поскольку безбумажный офис для многих остается несбыточной мечтой, а большинство людей больше доверяют бумажным, нежели электронным документам, то, вполне вероятно, Вам понадобится распечатать данные каждого заказа. Поэтому следует предусмотреть возможность печати бланка заказа, который к тому же можно заполнять автоматически. Образец бланка заказа, изображенный на рисунке, может служить основой для создания лучшего бланка.

 

		Заказ №	3604	от	05.01.98	35800
	Название фирмы заказчика		Центальный ООО			Код	2301
	Наименование товара		жакет			№	201
	Заказываемое количество		30	ед по цене		$500.00	за ед.
	Общая стоимость заказа		$15 000.00		Скидка(%)		0.00%
	К оплате	$15 000.00		Оформил		Бусыгина О.А.

Если Вас не устраивает шрифт, установленный  по умолчанию, начните с выбора шрифта. Для этого выделите весь рабочий  лист с помощью кнопки в верхнем  левом углу листа, а затем в списке Шрифт  панели инструментов Форматирование выберите новый вид шрифта. Теперь можно приступить к созданию бланка. Обратите внимание, что все четные строки листа не заполняются. Установите указатель ячейки в ячейке D3 и введите запись Заказ N. Номер заказа следует поместить в ячейку ЕЗ. Его можно подчеркнуть, выбрав в списке Линии рамки нижнюю линию. Во время работы не забывайте изменять соответствующим образом ширину столбцов. В ячейку F3 введите запись от и уменьшите ширину столбца. В ячейке G3 должна быть указана дата заказа. Ее мы вставим с помощью  следующей формулы:

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Дата)

  Эта формула аналогична использовавшейся ранее, поэтому мы не   будем тратить время на ее рассмотрение. Подчеркните вставляемое с помощью формулы значение. В дальнейшем при разработке бланка все области, предназначенные для ввода переменных   данных, следует подчеркивать, используя список Линии рамки панели инструментов Форматирование. Значения в строке должны быть выделены  полужирным  начертанием и иметь размер шрифта 14 пунктов. Продолжим создание нашего бланка и перейдем к оформлению второй строки. В ячейку С5 введите запись Название фирмы-заказчика. Постарайтесь ввести текст таким образом, чтобы он занял ячейки С5 и D5. Для названия фирмы отведите ячейки Е5, F5, G5. Чтобы при заполнении бланка название фирмы вставлялось автоматически, поместите в ячейку Е5 формулу

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Фирма2)

Подчеркните название фирмы и разместите его по центру диапазона из трех ячеек. Для этого выделите ячейки Е5, F5, G5 и активизируйте в панели инструментов Форматирование кнопку Объединить и поместить в центре . В ячейку Н5 введите запись Код, а в ячейку I5 поместите формулу

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Код2)

Теперь займемся оформлением третьей  строки нашего бланка. В ячейку С7 введите запись Наименование товара, а ячейкам Е7, F7 и  G7 назначьте подчеркивание и центрирование. Ячейка Е7 должна содержать следующую формулу:

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Товар2)

В ячейку Н7 введите запись № (символ номера), а в ячейку I7— формулу

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Номер2)

Четвертая строка нашего бланка должна содержать сведения о количестве и цене заказываемого товара. Введите в ячейку С9 запись Заказываемое количество. Для значения зарезервируйте ячейку Е9. Значение будет вставляться автоматически, если в ячейку Е9 ввести формулу

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Количество)

Как и для всех областей бланка, предназначенных для ввода переменной информации, задайте для этой ячейки подчеркивание. В ячейку F9 введите запись ед. по цене и выровняйте ее относительно центра столбцов F и G. Ячейка Н9 должна содержать такую формулу:

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Цена2)

Этой ячейке следует назначить  подчеркивание и денежный стиль. В завершение работы над четвертой  строкой бланка введите в ячейку 19 запись за ед.

Введите в ячейку С11 текст Общая стоимость заказа, в ячейку I 11 поместите формулу

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Сумма)

и установите параметры форматирования: подчеркивание и денежный стиль. В ячейку FII введите запись Скидка (%), выделите ячейки F11, G11, H11 и выполните щелчок на кнопке . В ячейку I11 поместите формулу

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Скидка2)

и задайте для ячейки подчеркивание  и процентный стиль. Теперь приступим  к работе над последней строкой  бланка. Введите в ячейку С13 текст К оплате, а в ячейке D13 разместите следующую формулу:

= ЕСЛИ($E$3=""; ""; ПРОСМОТР ($E$3; Заказ; Оплата)

Задайте для ячейки подчеркивание  и денежный стиль. В заключение не мешало бы указать фамилию лица, оформившего этот заказ. В ячейку Е13 введите запись Оформил:, выделите ячейки E13, F13 и задайте центрирование текста. Затем выделите ячейки G13, Н13, 113 и задайте для них центрирование и подчеркивание. Большая часть работы сделана. Вы даже частично сформатировали бланк, осталось только улучшить внешний вид документа и -проверить однотипность оформления, например, все ли ячейки, предназначенные для ввода значений, выделены голубым цветом (номер заказа и фамилия лица, оформившего бланк).

В завершение установите ширину столбцов В и J равной  1,57, выделите диапазон ячеек В2— J14 и задайте обрамление всего диапазона.

Теперь все. Попробуем использовать созданный бланк на практике. Укажите в ячейке ЕЗ номер заказа, а затем, перед печатью бланка, свою фамилию. Если программа не совсем корректно справилась с поставленной задачей, проверьте, отсортированы ли номера заказов по возрастанию, или измените ширину столбцов (при возникновении значения ошибки).

Печать документа

  Перед печатью бланка стоит  воспользоваться функцией предварительного просмотра, чтобы проверить вид таблицы на распечатке.   Для активизации функции предварительного просмотра выберите   одноименную команду в меню Файл.

Таблица, распечатанная в таком  виде, вряд ли украсит Ваш архив.   Чтобы красиво расположить таблицу  на странице, нажмите кнопку Страница и в появившемся диалоговом окне выберите раздел   Поля. В нем можно установить новые значения полей, задать   способ расположения таблицы по вертикали и горизонтали, а   также расстояние от краев бумаги до нижнего и верхнего колонтитулов. В разделе Страница можно задать ориентацию страницы,   размер бумаги, качество печати и печать номера первой страницы.   В разделе Колонтитулы определяется содержимое верхнего и   нижнего колонтитулов. Вы можете выбрать один из встроенных   колонтитулов. В завершение в разделе Лист укажите, нужно ли   печатать сетку, а также определите качество и последовательность   печати. Установите все необходимые параметры и закройте диалоговое окно Параметры страницы нажатием кнопки ОК.   Для запуска процесса печати нажмите кнопку Печать. Программа   попросит Вас установить в появившемся диалоговом окне некоторые параметры печати: печатаемые страницы, количество копий. Параметры для  принтера задаются в диалоговом окне,   которое открывается вследствие нажатия кнопки Свойства.   Подождите немного, пока компьютер и принтер справятся с   поставленной перед ними задачей, и приготовьтесь принимать   поздравления с выполнением первого примера.