Лабораторные работы Excel

Лабораторная работа № 1

Создание списка клиентов

1. Создайте рабочую книгу и сохраните ее в своей папке под именем Заказы(Ваша фамилия).

2. Первому листу присвойте имя Клиенты. Для этого 2 раза щелкните на ярлык Лист1 и переименуйте его в Клиенты.

3. Введите в ячейку А1 заголовок первого столбца – Название фирмы. Затем переместите указатель мыши в первую ячейку второго столбца. Для этого нажмите на клавишу [Tab] или стрелку . Следует в ячейки А1-I1 ввести следующие заголовки:
   
   А1Название фирмы-(любое название фирмы)
   B1ÞКод- (любое четырехзначное число, у каждой фирмы свой код, т.е. нет двух одинаковых кодов)
   C1ÞКонтактная персона-(представитель фирмы, ответственный за контакты с клиентами: фамилия И.О.)
   D1ÞИндекс
   E1ÞГород
   F1ÞУлица
   G1ÞТелефакс
   H1ÞТелефон
   I1ÞСкидка(%)-(указывается скидка в числовом выражении, т.е. 0,15 или 0,3 и т.д. )
   
   
   

4. Ввод записей. Для этого находясь в следующей строке заполненной области выбрать в меню Данные Форма. Нажмите ОК. Откроется диалоговое окно формы данных Клиенты.
   

Введите список 15 фирм. Фирмы распределите по 5 городам. Набрав первую запись нажмите на кнопку Добавить.

5. Форматирование таблицы. Для ячеек I2-I14 задайте процентный стиль (для этого выделите данный диапазон и нажмите на кнопку Процентный формат на панели инструментов Форматирование).

6. Сортировка данных. Необходимо выбрать в меню Данные Сортировка. В диалоговом окне выбрать первый критерий сортировки Код и второй критерий Город и ОК.

7. Фильтрация данных. Выбрать в меню Данные Фильтр/Атофильтр. После щелчка на имени этой команды в первой строке рядом с заголовком каждого столбца появиться кнопка со стрелкой. С ее помощью можно открыть список, содержащий все значения полей в столбце. Выберите название одного из городов в Город. Кроме значений полей, каждый список содержит еще три элемента: (Все), (Первые 10…) и (Условие…). Элемент (Все) предназначен для восстановления отображения на экране всех записей после применения фильтра. Элемент (Первые 10…) обеспечивает автоматическое представление на экране десяти первых записей списка. Если вы занимаетесь составлением всевозможных рейтингов, главная задача которых состоит в определении лучшей десятки, воспользуйтесь этой функцией. Последний элемент - используется для формирования более сложного критерия отбора, в котором можно применить условные операторы И и ИЛИ.

8. Установите курсор в любую заполненную ячейку и выполните следующие действия: в меню Формат Автоформат Список 2.
   
   

Создание списка товаров

Второй список будет содержать данные о предлагаемых нами товарах.

1. Перейдите на второй лист рабочей книги, щелкнув на ярлычке Лист2, и присвойте ему имя Товары.

2. Список, создаваемы на рабочем листе Товары, должен состоять из трех столбцов: Номер, Наименование товара и Цена. Введите указанные имена в ячейки А1-С1.

3. Т

   

   еперь приступаем к вводу данных. Желательно, чтобы номера товаров были расположены по возрастанию.
   
   

4. Цены должны вводиться в таком виде: 1150, 200 или 3000. Затем диапазон С2-С12 форматируем следующим образом в меню ФорматЯчейкиЧислоДенежныйОбозначение($Английский(США); число десятичных знаков-0) ОК.

5. Ф

   

   ормат Автоформат Список 3.
   
   

Лабораторная работа № 2

Лист Заказы

1. Переименуйте рабочий лист ЛистЗ на имя Заказы.

2. Введите в первую строку следующие данные, которые будут в дальнейшем именами полей:
   А1  Месяц заказа, В1  Дата заказа, С1  Номер заказа, D1  Номер товара, Е1  Наименование товара, F1 Количество, G1  Цена за ед., H1 Код фирмы заказчика., I1  Название фирмы заказчика, J1  Сумма заказа, К1  Скидка(%), L1 Оплачено всего.

3. Для первой строки выполните выравнивание данных по центру Формат  ЯчейкиВыравниваниепереносить по словам.

4. Выделите по очереди столбцы B, C, D, E, F, G, H, I, J, K, L и введите в поле имени имена Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата.

5. Выделите столбец В и выполните команду меню Формат  Ячейки. Во вкладке Число выберите
   Числовой формат Дата, а в поле Тип выберите формат вида ЧЧ.ММ.ГГ. В завершении диалога
   щелкните кнопку ОК.

6. Выделите столбцы G, J, L и выполните команду меню Формат  Ячейки. Во вкладке Число
   выберите Числовой формат Денежный, укажите Число десятичных знаков равное 0, а в поле
   Обозначение выберите $ Английский (США). В завершении диалога щелкните кнопку ОК.

7. Выделите столбец К и выполните команду меню Формат  Ячейки. Во вкладке Число выберите
   Числовой формат Процентный, укажите Число десятичных знаков равное 0. В завершении
   диалога щелкните кнопку ОК.

8. В ячейке А2 нужно набрать следующую формулу:

=ЕСЛИ(ЕПУСТО($В2);« »;ВЫБОР(МЕСЯЦ($В2);«Январь»;«Февраль»; «Март»; «Апрель»;«Май»;«Июнь»;«Июль»;«Август»;«Сентябрь»;«Октябрь»;«Ноябрь»;«Декабрь»)) (3.1)

И залить ячейку в желтый цвет.

Формула (3.1) работает следующим образом, вначале проверяется условие на пустоту ячейки А2. Если ячейка пусто, то ставится пробел, в противном случае с помощью функции ВЫБОР выбираем нужный месяц из списка, номер которого определяется функцией МЕСЯЦ.

Для того, чтобы набрать формулу (3.1) выполните следующие действия:

• сделайте активной ячейку А2 и вызовите функцию ЕСЛИ;

• в окне функции ЕСЛИ в поле Логическое_выражеиие напечатайте вручную $B2= «», в

поле значепие_если_истина наберите « », в поле значение_еслн_ложь вызовите функцию ВЫБОР;

• в окне функции ВЫБОР в поле значение1 напечатайте «Январь», в поле значение2 напечатайте

«Февраль» и так далее пока не напечатаете все названия месяцем. Затем поставьте текстовый курсор

в поле номер_индекса и вызовите функцию МЕСЯЦ;

• в окне функции МЕСЯЦ в поле Дата_как_число наберите адрес $B2;

• Щелкните кнопку ОК.

9. В ячейку Е2 набираем следующую формулу:
   
   

=ЕСЛИ($D2=« »; “ ”;ПРОСМОТР($D2;Номер товара; Наименование товара) (3.2)

Правило набора формулы:
Щелкните в ячейку Е2. Установите курсор на значок Стандартной панели. Откроется окно Мастер функции …, выберите функцию ЕСЛИ. Выполните действия, которые видите на рисунке

Т.е. в позиции Лог_выражение щелкните на ячейку D2 и три раза нажмите на клавишу F4 - получите $D2, наберите =« », клавишей Tab или мышью перейдите в позицию Значение_если_истина и наберите. « », перейдите в позицию Значение_если_ложь – щелкните на кнопку рядом с названием функции и выберите команду Другие функции.. → Категории → Ссылки и массивы, в окне Функции → ПРОСМОТР→ ОК→ ОК.

Откроется окно функции ПРОСМОТР. В позиции Искомое_значение щелкните на ячейку D2 и три раза нажмите на клавишу F4 - получите $D2, клавишей Tab или мышью перейдите в позицию Просматриваемый_вектор и щелкните на ярлык листа «Товары», выделите диапазон ячеек А2:А12, нажмите на клавишу F4, перейдите в позицию Вектор_результатов – еще раз щелкните на ярлык листа «Товары», выделите диапазон ячеек В2:В12, нажмите на клавишу F4, и ОК. Если выполнили все верно – появится в ячейке #HD.

С

делайте заливку ячейки желтым цветом.

10. В ячейку G2 набираем следующую формулу:

=ЕСЛИ($D2=« »;« »;ПРОСМОТР($D2;Номер товара; Цена)) (3.3)

Сделайте заливку ячейки желтым цветом.

11. В ячейку I2 набираем следующую формулу:
=ЕСЛИ($Н2=« »;« »;ПРОСМОТР($H2;Код; Фирма)) (3.4)
Сделайте заливку ячейки желтым цветом.

12. В ячейку J2 набираем следующую формулу:
=ЕСЛИ(F2=« »;« »;F2*G2) (3.5)
Сделайте заливку ячейки желтым цветом..

13. В ячейку K2 набираем следующую формулу:
=ЕСЛИ($Н2=« »;« »;ПРОСМОТР($H2;Код; Скидка)) (3.6)
Сделайте заливку ячейки желтым цветом.

14. В ячейку L2 набираем следующую формулу:
=ЕСЛИ(J2=« »;« »;J2-J2*K2) (3.7)
Сделайте заливку ячейки желтым цветом.

15. Ячейки В2 , D2 и Н2 – в которых нет формул, залить голубым цветом. Выделите диапазон А2 – L2 и маркером заполнения (черный крестик в правом нижнем углу блока) протянуть заливку и формулы до 31 строки включительно..

16. Сделайте активной ячейку В2 и протяните вниз маркером заполнения до ячейки ВЗ1 включительно.

17. В ячейку С2 напечатайте число 2008-01, которое будет начальным номером заказа и протяните вниз маркером заполнения до ячейки CЗ1 включительно.

18. Теперь необходимо заполнить с клавиатуры столбцы В2:В31 , D2: D31 и Н2:Н31. С В2 по В11 набираем январские даты (например, 2.01.08, 12.01.08). С В12 по В21 набираем февральские даты (например, 12.02.08, 21.02.08) и с В22 по В31 набираем мартовские даты (например, 5.03.08, 6.03.08). В D2: D31 набираем номера товаров т.е. 101, 102, 103, 104, 201, 202, 203, 204, 301, 302 и 303. Номера могут повторяться и идти в любом порядке, аналогично в Н2:Н31 вводим Коды ваших фирм, которые у вас набраны на листе Клиенты. В столбец F вводим двузначные числа.

19. Вы с успехом выполнили работу, сдайте ее преподавателю!.

(СРСП) Лабораторная работа № 3

Бланк Заказа

1. П

   

   ереименуйте рабочий лист Лист4 на имя Бланк заказа.
   
   
   
   
   
   
   
   
   
   
   
   

2. Установите курсор в ячейку D3 и введите запись Заказ №. Номер заказа следует поместить в Е3. Его подчеркните, выбрав в списке Линии рамки нижнюю линию. В ячейку F3введите запись от и уменьшите ширину столбца.

3. В ячейке G3 должна быть указана дата заказа. Ее вставим с помощью формулы:
   =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Дата))
   В дальнейшем при разработке бланка все области, предназначенные для ввода переменных данных, следует подчеркивать, используя список Линии рамки панели инструментов Форматирования. Значения в строке 3 должны быть выделены полужирным начертанием и иметь размер шрифта 14 пунктов.

4. В ячейку С5 введите запись Название фирмы-заказчика. Постарайтесь ввести текс т.о., чтобы он занял две ячейки С5 и D5.

5. Д

   

   ля названия фирмы отведите ячейки E5, F5, G5. Чтобы при заполнении бланка название фирмы вставлялось автоматически, поместите в Е5 формулу
   =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Фирма2))
   Подчеркните название фирмы и разместите по центру диапазона из трех ячеек. Для этого выделите ячейки и активизируйте в панели Форматирование кнопку Объединить и поместить в центре.

6. В ячейку Н5 введите запись Код, а в ячейку I5 поместите формулу
   =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Код2))

7. В ячейку С7 введите запись Наименование товара.

8. Ячейка E7должна содержать формулу
   =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Товар2)),
   а ячейкам E7, F7, G7назначьте подчеркивание и центрирование.

9. В ячейку Н7 введите символ №, а в ячейку I7 – формулу:
   =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Номер2))

10. В ячейку С9 введите запись Заказываемое количество.

11. В ячейку Е9 –формулу
    =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Количество))

12. В ячейку F9 –запись ед. по цене и выровнять ее относительно центра столбцов F и G.

13. Ячейка Н9 должна содержать формулу
    =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Цена2)),
    этой ячейке следует назначить подчеркивание и денежный стиль. В ячейку I9 –запись за ед.

14. Введите в С11 текст Общая стоимость заказа, а в Е11 поместите формулу
    =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Сумма)),
    и установите параметры форматирования: подчеркивание и денежный стиль.

15. В ячейку F11 –запись Скидка(%). Выделите F11, G11, Н11и выполните щелчок по кнопке Объединить и поместить в центре . В ячейку I11 поместите формулу
    =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Скидка2)),
    и установите параметры форматирования: подчеркивание и процентный стиль.

16. В ячейку С13 –текст К оплате. А в ячейке D13разместите следующую формулу
    =ЕСЛИ($E$3=“ ”; “ ”;ПРОСМОТР($E$3;Заказ; Оплата)),
    и установите параметры форматирования: подчеркивание и денежный стиль.

17. В ячейку Е13 введите запись Оформил(а):, выделитеЕ13, F13 и задайте центрирование текста. Затем выделите G13, Н13, I13 и задайте в них центрирование и подчеркивание.

18. В завершение установите ширину столбцов B и J равной 1,57, выделите B2- J14 и задайте обрамление всего диапазона.

19. Теперь в Е3 укажите Номер заказа, и перед печатью бланка свою фамилию.

20. Вы с успехом выполнили работу, сдайте ее преподавателю!.

Сводная таблица

Создан список заказов для практического применения и его данные подлежать анализу. Поможет нам выполнить анализ Мастер сводных таблиц.

Сводные таблицы создаются на основе списка или базы данных.

1. Перейти на Лист 5 и придать ему имя Сводная таблица.

2. В меню Данные Сводная таблица.

3. Н

   

   а первом шаге одобрите выбор мастера – В списке или базе данныхMicrosoft Excel – нажмите кнопку Далее.
   
   

4. Н

   

   а втором шаге поместив курсор ввода в поле Диапазон следует вернутся в рабочий лист Заказы и выделить диапазон A1-L31. После нажимаем на кнопку Далее.
   
   
   
   
   
   
   
   

5. Т

   

   ретье оно Мастера сводных таблиц является наиболее важным. Нажмите на кнопку Макет. И нужно определить структуру сводной таблицы.
   
   
   
   
   
   
   
   
   
   
   
   
   
   

6. Поместите в область страниц поле данных Месяц – (зацепите мышкой кнопку Месяц и совместите с областью Страница), а в область строк – поля Наименование товара, Количество и Сумма заказа.

7. Т

   

   еперь следует определить по какому полю будем подсчитывать сумму. Поместите в область данных поле Сумма заказа, вследствие чего в области данных появится кнопка Сумма по полю Сумма заказа. Нажмите на кнопку Готово.

8. Вы с успехом выполнили работу, сдайте ее преподавателю!.

(СРСП) Лаб. № 4. Филиалы

1. Создайте рабочую книгу и сохраните ее в своей папке под именем Филиалы(Ваша фамилия). Начнем выполнение примера с создания таблицы и ввода данных о каждом филиале.

2. Подготовительный этап. Скопируйте в буфер обмена с листа Товары книги Заказы данные о товарах, их номерах и ценах, т.е. скопируйте диапазон ячеек А1-С12 листа Товары.

3. Перейдите к первому листу книги Филиалы и в ячейку А3 вставьте скопированный фрагмент таблицы. В 3 строе в ячейки D3, E3, F3 введите соответственно записи Количество заказов, Проданное количество и Объем продаж. Задайте центрирование текста в ячейках и разрешите перенос текста по словам.

4. В ячейку F4 поместите формулу: =С4*Е4 и скопируйте ее в ячейки F5-F14.

5. Введите в ячейку В15 слово Всего:, а в ячейку F15 вставьте формулу суммы или нажмите кнопку панели инструментов Стандартная. Excel сам определит диапазон ячеек, содержимое которых следует суммировать.

6. Таких листов должно быть столько, сколько у вас было городов в листе Клиенты. Мы должны скопировать этот лист 4 раза.

7. Для этого установите курсор мыши на его ярлычке и нажмите правую кнопку манипулятора. В контекстном меню выберите команду Переместить/скопировать, в появившемся диалоговом окне укажите лист, перед которым должна быть вставлена копия, активизируйте опцию Создать копию и нажмите ОК. Намного проще копировать с помощью мыши: установите указатель мыши на ярлычке листа и переместите его в позицию вставки копии, удерживая при этом нажатой клавишу [Ctrl].

8. Имена рабочих листов соответствуют названиям городов с листа Клиенты, например, Алматы, Астана, Шымкент, Актау, Караганда или другие названия. Введите название филиала, соответствующего названию листа и в ячейку А1 данного листа.

9. Дополните лист Заказы еще одним столбцом. В ячейку М1 введите слово Город. В ячейку М2 введите формулу =ЕСЛИ(ЕПУСТО($H2);“ ”;ПРОСМОТР($H2;Код; Город)), протяните эту формулу до строки 31 этого столбца.

10. Выбрать в меню Данные Фильтр/Атофильтр. Выберите в столбце Город первый филиал. Данные столбца Количество листа Заказы будут внесены вами в столбец Проданное количество листа книги Филиалы, в строки соответствующие номерам товаров. Если проданы товары с одним номером в разные месяцы, то берется их суммарное количество. И так заполняются листы всех городов.

11. Консолидация данных. Скопируйте с первого листа книги Филиалы диапазон А3-В14, перейдите в 6 рабочий лист и вставьте в ячейку А3.

12. Приступаем к консолидации. Установите указатель ячейки в С3 и выберите в меню Данные Консолидация.

13. В списке Функции следует выбрать элемент Сумма. Укажите в поле ввода Ссылка диапазон ячеек, данные которых должны быть подвергнуть процессу консолидации. Удобно отмечать диапазон ячеек с помощью мыши.

14. Установите курсор ввода в поле Ссылка, выполните щелчок на ярлычке первого города, например –Алматы, выделить диапазон ячеек D3-F14 и нажать кнопку Добавить окна Консолидация. В результате указанный диапазон будет переставлен в поле Список диапазонов.

15. Затем переходите на лист второго города. Диапазон указывается автоматически, нажимаете на кнопку Добавить и так 5 раз.

16. Если верхняя строка и (или) левый столбец содержат заголовки, которые необходимо скопировать в итоговую таблицу, следует активизировать соответствующие опции в группе Использовать метки. Поскольку в нашем примере верхняя строка содержит заголовки столбцов, нужно активизировать опцию В верхней строке.

17. Если между исходными данными и данными консолидированной таблицы должны быть установлены динамическая связь, включите опцию Создавать связи с исходными данными.

18. Кнопку Обзор следует использовать для выбора файла , который содержит консолидируемые данные.

19. Нажмите кнопку ОК.

20. В ячейку А1 введите название новой таблицы Итоговые данные.

21. Введите в ячейку В70 значение Всего:, а в Е70 - и нажмите на клавишу [Enter]

22. Теперь приступаем к определению доли от общей прибыли суммы, вырученной от продажи каждого товара. Введите в F9 формулу = Е9/$E$70 и скопируйте ее в остальные ячейки столбца F (до ячейки F70) .

23. Отформатируйте содержимое столбца F в процентном стиле. Полученные результаты позволяют сделать выводы о популярности того или иного товара.

24. При консолидации данных программа записывает в итоговой таблице каждый элемент и автоматически создает структуру документа, что позволяет добиться представления на экране только необходимой информации и скрыть ненужные детали. Слева от таблицы отображаются символы структуры. Цифрами обозначаются уровни структуры (в нашем примере – 1 и 2). Кнопка со знаком плюс позволяет расшифровать данные высшего уровня. Нажмите, например, кнопку для ячейки А9, чтобы получить информацию об отдельных заказах.

25. Скопируйте формулу из F9 в ячейки F4- F8.

Цифры в превращаются в Диаграммы

1. Подготовительная работа. Поскольку для каждой диаграммы нужна собственная таблица, создадим новую сводную таблицу на основе данных листа Заказы одноименной книги Заказы.

2. Откройте ранее созданную книгу Заказы. Создайте новую книгу и присвойте ее первому листу имя Таблица. Этот лист будет содержать числовой материал для диаграммы.

3. Поместите указатель в ячейку В3 и выберите меню Данные Сводная таблица.

4. Выберите первый способ расположения данных – В списке или базе данныхMicrosoft Excel – нажмите кнопку Далее.

5. На втором шаге поместив курсор ввода в поле Диапазон следует с помощью меню Окно перейти в рабочую книгу Заказы и в рабочем листе Заказы и выделить диапазон A1-L31. После нажимаем на кнопку Далее.

6. Следует определить структуру сводной таблицы. Поместите в область строк кнопку Наименование товара, а в область столбцов – кнопку Месяц. Сумма будет вычисляться по полю Сумма заказа, т.е. переместите эту кнопку в область данных. Нажмите кнопку Готово.

7. Выделите диапазон B4-F14. Если вы выделяете диапазон ячеек с помощью мыши, начните выделение с любой крайней ячейки диапазона за исключением ячейки F4, которая содержит кнопку сводной таблицы.

8. Щелкните на кнопке Мастер диаграмм в панели инструментов Стандартная.

9. На первом шаге укажите тип диаграммы, нажмите на кнопку Далее.

10. На втором шаге подтвердите диапазон =Таблица!$B$4:$F$15.

11. На третьем шаге указываете параметры диаграммы (Заголовки, Оси, Легенды и т.д.). Название диаграммы введите Объем продаж по месяцам, Категории (Х )- Наименование товара и Значение(Y) –Объем продаж(USD). Внесенные изменения сразу отразятся на изображении в поле Образец, нажмите на кнопку Далее.

12. Нажмите на кнопку Готово.