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

Лабораторные работы 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 городам. Набрав первую запись нажмите на кнопку Добавить.

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

 

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

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

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

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

 

 

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

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