Створення випадаючого списку. Функції ПОИСКПОЗ та ИНДЕКС

Лабораторна робота № 27-28

Тема: Робота з функціями та формулами у Microsot Excel

Мета: оволодіти навичками обчислення різних формул з використанням вбудованих функцій

КОНТРОЛЬНІ ПИТАННЯ

1. Як викликати Мастер функций?
2. Які кроки містить Мастер функций?
3. Що розуміють під синтаксисом формул?
4. Як здійснюється введення і редагування формул?
5. Що використовується як аргументи функції?
6. Як вставити функцію у формулу?
7. У яких випадках у функції немає аргументів?
8. Як здійснити виправлення формули?
9. Скільки рівнів вкладеності функцій можна використовувати у формулах Excel?
10. Як застосувати до комірки чи діапазону умовне форматуван
11. Навести формат та пояснити роботу функції ВПР
12. Навести формат та пояснити роботу функції ГПР

Порядок виконання роботи

 

Завдання 1. Створити на аркуші Лист1 таблицю по  зразку (рис. 1).

 

Завдання 2. Виконати розрахунки в створеній таблиці.

Для виконання завдання необхідно:

1. Розрахувати вартість автомобілів у гривнях (графа «Вартість, грн.»):

• в комірку B1 ввести поточний курс долару;
• в комірку B2 ввести поточний курс Євро;
• в комірку Е4 ввести формулу:

=ЕСЛИ(D4="$";C4*$B$1;C4*$B$2)

і скопіювати цю формулу в діапазон Е5:Е12.

2. Розрахувати суму виторгу у гривнях (графа «Усього, грн.»):

• в комірку G4 ввести формулу:

=E4*F4

і скопіювати цю формулу в діапазон G5:G12.

3. Розрахувати суми у комірках F13 i G13.

 

Завдання 3. Створити на аркуші Лист1 таблицю по зразку (рис. 2). Розрахувати кількість і вартість сплачених автомобілів готівкою та по безготівковій системі сплати, а також їх вартісну частку в загальній сумі.

Для виконання завдання необхідно:

1. Для рядка Готівковий ввести наступні формули:

• у комірку В16 ввести формулу:

=СУММЕСЛИ(H4:H12;A16;F4:F12)

• у комірку С16 ввести формулу:

=СУММЕСЛИ(H4:H12;А16;G4:G12)

Рис. 1. Вихідні дані для виконання завдання 1

 

Рис. 2. Вихідні дані для виконання завдання 3

 

2. Аналогічно ввести формули для рядка Безготівковий.
3. Розрахувати суми у комірках B18 та C18.
4. Розрахувати у комірці D16 вартісну частку за формулою =C16/$C$18, скопіювати цю формулу для рядка Безготівковий.
5. Коміркам D16 та D17 встановити відсотковий формат.
6. У комірці D18 перевірити суму часток за допомогою кнопки Автосумма.

 

Завдання 4. Створити на аркуші Лист1 таблицю по зразку (рис. 3). Самостійно розрахувати кількість, вартість автомобілів та їх частку в загальній кількості, проданих автомобілів у кожному місяці.

Рис. 3. Вихідні дані для виконання завдання 4

 

Завдання 5. Створити на аркуші Лист1 таблицю по зразку (рис. 4). Самостійно розрахувати кількість, вартість проданих автомобілів кожної марки та їх частку в загальній кількості.

Рис. 4. Вихідні дані для виконання завдання 5

 

Завдання 6. Створити на аркуші Лист1 таблицю по зразку (рис. 5). Визначити вартість мінімального і максимального замовлення у кожному місяці.

Рис. 5. Вихідні дані для виконання завдання 6

 

Для виконання завдання необхідно:

1. Для рядка Січень в комірку B34 ввести формулу: =МИН(G4:G5).
2. Аналогічно провести розрахунки для рядків Лютий, Березень.
3. Самостійно виконати розрахунки для стовпця Максимум та рядка Разом.
4. Дати ім’я поточному аркушу Завдання_1-6.

 

Завдання 7. Створити на аркуші Лист3, який перейменувати на Завдання_7-14 _Формат, таблицю по зразку (рис. 6).

Рис. 6. Вихідні дані для виконання завдання 7

 

Завдання 8. Вставити у таблицю між графами «Рік випуску» та «Середні витрати пального на 100 км, л» нову графу Строк служби транспортної одиниці, років. В комірку G2 ввести формулу: =ГОД(СЕГОДНЯ())-F2, скопіювати її в блок комірок G3:G19.

 

Завдання 9. Створити на поточному аркуші Завдання_7-14 _Формат таблицю по зразку (рис. 7). Виконати розрахунки у таблиці.

 

Завдання 10. На поточному аркуші Завдання_7-14_Формат у першій таблиці додати стовпчик Найбільший пробіг, у комірках якого для транспортних засобів з найбільшим пробігом вивести символ . Для чого в комірку I2 ввести формулу: =ЕСЛИ(E2=МАКС($E$2:$E$19);СИМВОЛ(14);""), скопіювати її в блок комірок I3:I19.

 

Завдання 11. На поточному аркуші Завдання_7-14_Формат у першій таблиці додати стовпчик Найбільші витрати палива, у комірках якого для транспортних засобів з найбільшими витратами палива вивести слово max.

 

Завдання 12. На поточному аркуші Завдання_7-14_Формат у першій таблиці додати стовпчик Капітальний ремонт, у комірках якого для транспортних засобів з пробігом понад 500000 км вивести слово Капремонт.

 

Завдання 13. На поточному аркуші Завдання_7-14_Формат у першій таблиці у стовпчику «Код виду транспорту» використовуючи умовне форматування виділити значення коду різними кольорами, зробіть різний колір фону комірок.

 

Рис. 7. Вихідні дані для виконання завдання 9

 

Завдання 14. На поточному аркуші Завдання_7-14_Формат у першій таблиці у стовпчику «Пробіг, км» виділити значення червоним кольором для тих транспортних засобів, пробіг яких понад 300000 км.

 

Завдання 15. На аркуші з ім’ям Завдання_15_Сортування, який розташувати за аркушем Завдання_7-14_Формат, створити таблицю по зразку (рис. 8) та виконати сортування прізвищ по алфавіту

 

Завдання 16. Ознайомитися з роботою функції ВПР.

Для виконання завдання необхідно:

1. На аркуші з ім’ям Завдання_16_Факультет_1, який розташувати за аркушем Завдання_15_Сортування, ввести в комірку А1 назву Прізвище.
2. Заповнити стовпець А прізвищами студентів 1-го факультету з аркуша Завдання_15_Сортування.

Для цього ввести в комірку А2 формулу:

=ЕСЛИ(Завдання_15_Сортування!G2=1;Завдання_15_Сортування!A2;"")

3. Скопіювати формулу у діапазон A3:A10.
4. Виділити діапазон A2:A10, виконати сортування прізвищ по алфавіту
5. На аркуші з ім’ям Завдання_16_Сесія, який розташувати за аркушем Завдання_16_Факультет_1, створити таблицю по зразку (рис. 9).
6. У стовпець А скопіювати тільки прізвища з аркуша Завдання_16_Факультет_1.
7. Для автоматичного заповнення таблиці іменами і по батькові студентів:

• в комірку В3 ввести формулу:

=ВПР(A3;Завдання_15_Сортування!$А$2:$Н$10;2)

• скопіювати формулу в блок комірок B4:B8;
• у комірку C3 ввести формулу:

=ВПР(A3;Завдання_15_Сортування!$А$2:$Н$10;3)

• скопіювати формулу в блок комірок C4:C8.

У цих формулах спочатку вказана адреса комірки з прізвищем, а потім діапазон комірок і номер стовпця в діапазоні, з якого треба витягувати дані.

8. Заповнити стовпці з назвою дисциплін оцінками за національною шкалою (5; 4; 3; 2; не з’явився) по зразку (рис. 10).

Рис. 8. Вихідні дані для виконання завдання 15

Рис. 9. Вихідні дані для виконання пункту 5 завдання 16

Рис. 10. Вихідні дані для виконання пункту 8 завдання 16

 

Завдання 17. Створити аркуш з назвою Завдання_17_18_19_Підсумок, який розташувати за аркушем Завдання_16_Сесія, а потім:

1. в комірку А1 ввести Прізвище і далі в блок комірок A2:A7 скопіювати прізвища студентів з аркуша Завдання_16_Сесія;
2. з аркуша Завдання_16_Сесія скопіювати список дисциплін командою Копировать, а потім в комірці Н1 аркуша Завдання_17_18_19_Підсумок викликати правою клавішею миші вікно Специальная вставка, в якому обрати метод копіювання транспонировать.
3. у комірку D1 ввести формулу: =C1+3

 

Завдання 18. Ознайомитися з та роботою функції ГПР.

Для виконання завдання необхідно:

1. Створити на аркуші Завдання_17_18_19_Підсумок елемент управління Поле со списком:

• вкладка Разработчик → раздел Элемент управления  Вставить  Элементы управления формы: Поле со списком);
• розмістити елемент Поле со списком в комірці В1;
• клацнути правою клавішею миші на елементі Поле со списком, вибрати команду Формат объекта… і у вікні Формат элемента управления на вкладці Элемент управления указати інформацію по зразку (рис. 11).
• розмістіть елемент Поле со списком так, щоб він був у комірці В1 (див. малюнок).

2. Заповнити формулами стовпець В:

• в комірку В2 ввести формулу:

=ГПР($D$1;Завдання_16_Сесія!$D$2:$Н$8;2)

• в комірку В3 ввести формулу:

=ГПР($D$1;Завдання_16_Сесія!$D$2:$Н$8;3)

• аналогічно заповнити весь діапазон з прізвищами.

3. Перевірити роботу елемента Поле со списком – клацнути на стрілці елементу управління, зі списку вибрати Інформатика - в комірку С1 виводиться 1.

Вміщена в комірку В2 формула =ГПР($D$1;Завдання_16_Сесія!$D$2:$Н$8;2) шукає в діапазоні $D$2:$Н$8 аркуша Завдання_16_Сесія для прізвища студента, яке занесене у другий рядок аркуша Завдання_17_18_19_Підсумок оцінку по предмету, що має індекс 1+3 (з комірки D1) і виводить її на екран.

 

Рис. 11. Вихідні дані для виконання пункту 1 завдання 18

 

Завдання 19. Створити на аркуші Завдання_17_18_19_Підсумок таблицю по зразку (рис.12). Виконати розрахунки у таблиці. Формули, для обчислення кількості «4», «3» та «2» ввести самостійно.

Рис.12. Вихідні дані для виконання завдання 18. Таблиця формул

 

Завдання 20. Зберегти виконану роботу у файлі з ім’ям Лабораторна_робота_27, який розташувати у папці Вашої групи на диску D:. Пред’явити виконану роботу викладачу.