Створення бази даних в MS Excel 2010 використовуючи функцію «ГПР»

1. Завантажити табличний процесор MS Excel 2010.
2. Встановити пароль для відкривання файлу (1111).
3. Зберегти книгу у власній папці «Учень» під назвою «База БД ГПР».
4. Завантажити ТБ MS Excel 2010.

5. Перейменуйте Лист1 на база даних ГРП, створіть таблицю використовуючи автозаповнення я показано на зразку.
6. В комірку В введіть наступну формулу:=СУММ(($L$1:$U$1=A2)*1), після введення формули натисніть комбінацію CTRL+SHIFT+Enter – це дасть можливість закінчити формулу і у Вас відразу в формулі котру Ви надрукували добавляться квадратні дужки як показано на зразку. (Пояснення по даній комбінації Ви знайдете у кінці роботи в текстовому варіанті) цей самий масив потрібно застосовувати для повного завершення формули на комірках В, D, F i W.
7. В комірку D введіть наступну формулу: =СУММ(($L$2:$U$2=C2)*1).
8. В комірку F введіть наступну формулу:=СУММ(($L$3:$U$3=E2)*1).
9. В комірку Н введіть наступну формулу: =B2*D2*F2.
10. На верхній рядок котрий у вас підписаний у вигляді літер від А до Н задайте режим фільтру.
11. Після завершення обрахунку Ви отримаєте таблицю із такими значеннями як показано на зразку.
12. В комірку W1 введіть наступний текст «Масив потрібних значень» і задайте параметр на панелі форматування «По центру».
13.    В комірку W2 введіть наступну формулу: =(ЕЧИСЛО(ГПР(A2:A15;L1:U1;1;0))*1)*

(ЕЧИСЛО(ГПР(C2:C15;L2:U2;1;0)*1)

*(ЕЧИСЛО(ГПР(E2:E15;L3:U3;1;0))*1))

*G2:G15 і за допомогою автозаповнення протягніть формулу до комірки W15 і ви

 отримаєте такий вигляд формули і запису як на зразку зображення.

14. В комірку V17 введіть текст «ГРП» а в комірку V18 «ПОИСКПОЗ».
15.    Введіть в комірку W17 наступну формулу: =СУММ((ЕЧИСЛО(ГПР(A2:A15;L1:U1;1;0))*1)

*(ЕЧИСЛО(ГПР(C2:C15;L2:U2;1;0)*1)

*(ЕЧИСЛО(ГПР(E2:E15;L3:U3;1;0))*1))*G2:G15).

16. Введіть в комірку W18 наступну формулу: =СУММ(ЕЧИСЛО(ПОИСКПОЗ(A2:A15;L1:U1;0)

*ПОИСКПОЗ(C2:C15;L2:U2;0)

*ПОИСКПОЗ(E2:E15;L3:U3;0))*G2:G15) і Ви повинні отримати наступні значення і звісно потрібно задати примітку як показано на зразку зображення.

1

2

3

4

Примітка до №6 пункту завдання!

Формули масиву іноді називають формулами CSE, так як для їх введення в книгах використовується поєднання клавіш CTRL + SHIFT + ВВІД (ENTER).

Короткий вступ в масиви і формули масиву

Якщо вам доводилося хоча б трохи займатися програмуванням, ви, швидше за все, стикалися з терміном масив. Для наших цілей масив можна уявити як набір елементів. В Excel ці елементи можуть перебувати в окремому рядку (в цьому випадку мова йде про одновимірному горизонтальному масиві), в стовпці (одновимірний вертикальний масив) або в декількох рядках і шпальтах (двовимірний масив). В Excel можна створювати тривимірні масиви або формули масивів.

Формула масиву представляє собою формулу, за допомогою якої можна виконувати різні обчислення з одним або декількома елементами в масиві. Формули масиву можуть повертати як окреме значення, так і безліч значень. Наприклад, можна помістити формулу масиву в діапазон комірок і скористатися цією формулою для обчислення шпальти чи рядки проміжних підсумків. Крім того, можна помістити формулу масиву в окремій клітинці, а потім обчислити окреме значення. Формула масиву, розташована в декількох осередках, називається формулою з декількома осередками, а формула масиву, що знаходиться в одній комірці, носить назву формули з одним осередком.

17.          На наступному Листі створіть таблицю за поданим зразком та виведіть поточні дати як показано на зразку.
18.          Перейдіть на наступний Лис і перейменуйте його на Товарний чек і створіть таблицю за поданим зразком та в загальну суму введіть наступну формулу =СУММ(B2:B5*C2:C5) використовуючи масив.
19.          Перейдіть на наступний Лис і перейменуйте його на Транспортування, виконайте таблицю за поданим зразком далі виділіть від А1 до В8 і натискаємо комбінацію клавіш Ctrl + Shift + Enter і Ви отримаєте такий вигляд таблиці як показано на зображенні.

20. Перейдіть на наступний Лист і задайте йому ім’я Таблиця множення та створіть таблицю за поданим зразком. Виділіть комірки від В2 до К11 і введіть формулу =A2:A11*B1:K1 і натисніть Ctrl + Shift + Enter. Після вірно введеної формули ви отримаєте таблицю множення як показано на зразку.

21. Перейдіть до наступного Листа та перейменуйте його на Вибіркове сумування та створіть таблицю за поданим зразком нижче. В комірку G4 потрібно вивести випадаючий список Замовників використовуючи таку послідовність дій Даные>Проверка данных>Тип даных>Список>Источник =$K$4:$K$12 і натиснути Enter. В комірку G5 потрібно вивести випадаючий список Товарів використовуючи таку послідовність дій Даные>Проверка данных>Тип даных>Список>Источник =$J$4:$J$6 і натиснути Enter. В комірку G6 потрібно вивести наступну формулу =СУММ((C3:C21=G4)*(B3:B21=G5)*D3:D21) і натиснути Ctrl + Shift + Enter.

Робоче місце добре освітлене.

Вентиляція приміщення.

Відсутність зайвих предметів, матеріалів.

Інструмент, який беруть правою рукою лежить з права,

а що, лівою рукою – зліва.

Користування справним інструментом.

Системний блок оцінюється за якість виконаної роботи.

Всі матеріали підключені послідовно.

Немає пошкоджень.

Зайвих пристроїв та інструментів.

Чистота робочого місця та апаратури.

Робота в спецодязі та взутті.

Монітор, системний блок, клавіатура та

маніпулятор (миша) в справному стані. Робоче місце відповідає

Вимогам охорони праці в повному обсязі.

Матеріали та їх кількість:

15 – Інструкційно-технологічних карт;

15 – укомплектованих робочих місць;

15 – комплектів встановленого програмного забезпечення;

15 медичних халатів.

Час виконання роботи:

5 годин.