ЛР_7_Excel

ЛАБОРАТОРНА РОБОТА №7

Тема:Табличний процесор Excel. Вибір малюнка з випадаючого списку. Робота з функціями ПОИСКПОЗ, СМЕЩ.

Мета:закріпити навички роботи з випадаючими списками значень; навчитися створювати випадаючи списки з малюнками, використовуючи функції ПОИСКПОЗ, СМЕЩ.

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

1. Як створити випадаючий список значень (перевірка коректності введення даних)?

2. Для чого використовують функції ПОИСКПОЗ, СМЕЩ?

ПОРЯДОК ВИКОНАННЯ РОБОТИ:

1. Завантажити табличний процесор MS Excel.

2. Встановити пароль для відкриття файлу та ввести свої особисті дані в книгу.

3. Зберегти книгу у папці Роботи по Excel назвою ЛР№_7

Завдання: Створити імітацію світлофора таким чином, щоб вибираючи зі списку колір, змінювався колір на малюнку світлофора.

Для виконання завдання потрібно:

4. На Листі2 створити список кольорів та вставити малюнки з відповідним кольором за зразком. Картинки знаходяться:

Робочий стіл/2 курс/Картинки/Світлофор

5. Присвоїти ім’я списку кольорів:

Формулы/Диспетчер имен/Создать

У вікні «Изменение имени» встановити:

Ім’я – ввести назву – колір

Діапазон – виділити список (діапазон А2:А4 – Лист2)

6. Перейти на Лист1 та створити випадаючий список в клітинці А1:

вкладка Данные/Проверка данных 

У вікні «Проверка вводимых значений» обрати:

Тип даних – Список

Источник – ввести формулу: = колір (діапазон А2:А4 – Лист2)

7. Встановити умовне форматування в клітинці А1 (Лист1), щоб при виборі назви кольору, текст оформлювався відповідним кольором.

Главная/Условное форматирование/Создать правило/Форматировать только ячейки, которые содержат/змінити опис правила та обрати формат.

8. Перенесемо перше зображення зі списку до випадаючого списку. Виділити клітинку з першим зображенням (не саме зображення, а клітинку) та виконати команди:

Главная/Копировать/Копировать как рисунок/обрати опції как на экране та растровый

9. Перейти на Лист1 та вставити ([Ctrl]+[V]) зображення в клітинку С3. Ми отримали зображення нашої клітинки, фактично Screenshot клітинки.

10. Присвоїмо клітинці (А1 – Лист1) з випадаючим списком ім’я:

вкладка Формулы/Диспетчер имен/Создать

У вікні «Изменение имени» встановити:

в поле Ім’я – ввести назву – фото

в поле Диапазон порібно ввести формулу; Будемо використовувати функції ПОИСКПОЗ, яка  знаходить клітинку з потрібним кольором по назві в списку та функцію СМЕЩ, яка  видає посилання на сусідню праворуч від знайденої назви клітинку.

Вводимо формулу в поле Диапазон:

Пояснення до формули:

=СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина])

ссылка – перша клітинка списку з назвами кольорів (А1 – Лист2 )

смещ_по_строкам – зміщення від першої клітинки вниз на n-ну кількість рядків (використаємо функцію ПОИСКПОЗ (пошук позиції). Вона буде шукати обраний колір із випадаючого списку в діапазоні А2:А4 та видавати порядковий номер клітинки в якій знаходиться цей колір – це й буде зсув вниз на задану кількість рядків )

=ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])

искомое_значение – клітинка з випадаючим списком А1 (Лист1)

просматриваемый_массив – виділити весь стовпчик А (Лист2).

[тип_сопоставления] – точно або приблизно потрібно шукати значення (0 – точно, 1 – приблизно) – вводимо 0

Так як перша клітинка стовпчика А у нас шапка таблиці і вона не повинна враховуватися, то ввести –1 (мінус1)

Дописуємо функцію СМЕЩ:

смещ_по_столбцам – зміщення від першої клітинки праворуч (в нашому випадку нам потрібно зміщення на стовпчик В, тобто на один стовпчик – введемо 1)

[высота] – розмір області на яку ми посилаємося по рядках – на клітинку з фото – на один рядок – ввести 1

[ширина] – розмір області на яку ми посилаємося по стовпчику – на клітинку з фото – на один стовпчик – ввести 1)

11. Виділити зображення світлофора на Листі1 (не клітинку, а зображення) та вписати в рядок формул: = фото

Ми отримали такий випадаючий список, в якому при виборі кольору встановлюється малюнок обраного світлофора.

12. Створити в клітинці С15 примітку з власним фото.

13. Аналогічно створити випадаючий список, в якому при виборі країни із списку – відображується її прапор.

Комп’ютер №1 – список країн на букву А, комп’ютер №2 – на букву Б; №3 – на букву В; №4 – Г; №5 – Д,Є,Е; №6 – З,І; №7 – К; №8 –Л; №9 – М; №10 – Н, О; №11 – П; №12 – Р, С; №13 – Т; №14 – У,Ф; №15 – Х,Ч,Ш; №16 – Ш, Ю, Я.

• Картинки прапорів країн знаходяться:

Робочий стіл/2 курс/папка Картинки/папка Прапор

14. Використовуючи функцію ВПР, встановити в стовпчиках D та Е відображення інформації про столицю країни та населення обраної країни.

15. Зберегти книгу.

16. Записати в конспект висновок по лабораторній роботі.

ДОМАШНЄ ЗАВДАННЯ:

1. Опрацювати опорний конспект лекції по темі лабораторної роботи, розібрати контрольні запитання та виконати тестові завдання.

2. Зробити письмово (в конспекті) відповіді на контрольні запитання до наступної лабораторної роботи.

3. Створити випадаючий список на 10 пунктів та інформацією по обраному пункту:

1 варіант – при виборі міста світу (не України) відображується картинка герба міста та інформація про країну та кількість населення;

2 варіант – при виборі області України відображується картинка герба області та інформація про площу та кількість населення;

3 варіант – при виборі районів Києва відображується герб району та інформація про площу та кількість населення;

4 варіант – при виборі кухонного приладдя відображується фото приладдя та інформація про виробника та ціну приладдя.

5 варіант – при виборі обладнання для ресторану відображується фото обладнання та інформація про виробника та ціну обладнання.

1. Зберегти завдання в папці Домашні завдання з назвою ДР№7.