Діапазони клітинок

Теоретичні відомості

Декілька клітинок робочої таблиці, які мають суміжні сторони, утворюють діапазон (блок) клітинок.

Діапазони мають прямокутну форму й описуються адресами двох діагонально протилежних клітинок. Наприклад:

· А1:СЗ — прямокутний діапазон;

· А1:А9 — діапазон-стовпець;

· А1:Е1 — діапазон-рядок.

Щоб виокремити діапазон, треба клацнути у лівому верхньому куті і, не відпускаючи клавіші, перемістити білий хрестоподібний курсор у правий нижній кут, відпустити клавішу. Щоб відмовитися від вибору, достатньо клацнути за межами діапазону.

Щоб виокремити несуміжні діапазони, треба користуватися клавішею Ctrl. Наприклад, щоб виокремити два несуміжні стовпці-діапазони, потрібно клацнути на їхніх назвах у режимі натиснутої клавіші Ctrl.

Діапазонам можна надавати назви і використовувати ці назви замість виразів на зразок А1:А9. Програма сама дає назви діапазонам, якщо вона може їх розпізнати. Наприклад, у таблиці на рис. 1 назви стовпців розпізнаються автоматично, тому в клітинку Е4 замість формули =В4 + С4 + D4 можна ввести формулу =Січень + Лютий + Березень.

2. Категорії функцій. Статистичні функції.

Функцій є декілька категорій:

· статистичні — AVERAGE (середнє арифметичне), MAX, MIN (у рос. версії програми СРЗНАЧ, МАКС, МИН), COUNT (кількість клітинок із числами), DEVSQ (сума квадратів відхилень від середнього значення), а також функції дослідження статистичних вибірок і функції прогнозування даних;

· математичні — sin, cos, exp, In, abs, atan, sqrt, SUM (сума, рос. назва СУММ) тощо, а також функції для роботи з матрицями;

· логічні;

· фінансові, інженерні;

· для роботи з датами, текстами та інші.

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

=SUM(A1; В6:С8; 20) або (рос.) =СУММ(А1; В6:С8; 20).

Оскільки суми обчислюють найчастіше, то в рядку формул, на стрічці чи панелі керування є кнопка Автосума ∑. Нею користуються так: вибирають клітинку під стовпцем чи праворуч від рядка з даними і клацають на кнопці Автосума — отримують шукану суму (числових даних із стовпця чи рядка).

Функції найкраще вставляти вручну. Але їх можна вставляти у вираз за допомогою майстра вставляння функцій, якого викликають комбінацією клавіш Shift+F3, або командами Формули / Вставити функцію чи Вставити / Функція..., або за допомогою кнопки f_х у рядку формул. В отриманому діалоговому вікні вибирають категорію і назву функції, ознайомлюються з її призначенням, вводять аргументи, переглядають результат, отримують у разі потреби довідку про дію і призначення функції, натискають OK.

Основні статистичні функції — це AVERAGE (середнє арифметичне), МАХ (максимальне значення), MIN (мінімальне значення). Функцію SUM (сума) зачисляють до категорії математичних, але її також часто застосовують для розв'язування статистичних задач. Розглянемо приклади застосування цих функцій:

1. МАХ (3; 4; 7; 6) повертає (має) значення 7;

2. MIN (3; 4; 7; 6) = 3;

3. AVERAGE (3; 4; 7; 6) = 5;

4. SUM (3; 4; 7; 5) = 20.

З призначенням інших, не менш важливих, статистичних функцій можна ознайомитися у вікні майстра вставляння функцій чи в довіднику.

Зауваження. В україномовних версіях програм зазвичай використовують англійські назви функцій, а в російськомовних — російські. Назви функцій можна записувати великими чи малими літерами.

3. Списки користувача.

Розглянемо ще один спосіб швидкого введення текстових даних у таблицю. Він полягає у використанні списків користувача. Списки користувача — це списки, що можуть містити назви об'єктів одного класу (товарів, міст, фірм, прізвища осіб тощо). Список використовують так: перший елемент списку вводять у деяку клітинку, перетягують її маркер копіювання — відбувається автозаповнення таблиці елементами списку. Але спочатку користувач має створити власні списки. У різних програмах це роблять різними способами.

У MS Excel 2003 списки створюють так: Сервіс /Параметри /Закладка Списки/ Новий список. Елементи списку вводять у вікно СПИСКИ через кому (або натискаючи на клавішу вводу) / Додати / OK.

У MS Excel 2007 списки створюють так: Меню / Параметри Excel / ГОЛОВНІ /Прокручують вікно праворуч до кінця і користуються кнопкою Змінити списки і т.д.

У MS Excel 2010-2013 списки створюють так: Файл /Параметри / Додатково /Прокручують вікно праворуч до кінця і користуються кнопкою Редагувати користувацькі списки (рос. Изменить списки) і т.д.

4. Випадаючі списки і перевірка даних.

Як різновид даних клітинка може містити випадаючий список даних. Він призначений для автоматизації введення даних шляхом вибору даного із заздалегідь заготовленого списку, а головне — для унеможливлення введення в клітинку даного, якого немає у списку. Випадаючі списки є частиною технології перевірки даних, яку, вибравши спочатку клітину, стовпець, рядок чи інший діапазон, запускають командами Дані / Перевірка даних.../ Перевірка даних. Отримують вікно Перевірка даних, де на першій закладці Параметри задають тип даних,наприклад, ЦІЛІ числа, якщо в клітинки мають вводитися тільки цілі числа від деякого мінімального до максимального значення, чи тип для текстів, чи тип СПИСОК для створення випадаючого списку. На другій закладці записують текст повідомлення, яке має з'явитися на екрані, коли користувач вибере клітинку, наприклад, «Олю! Зараз будь уважною!». На третій закладці потрібно написати текст повідомлення, яке має з'явитися, коли користувач зробить помилку, наприклад, «Олю! Це помилка. Введи інше значення!».

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

Електронна таблиця як база даних.

ЕТ можна використовувати як базу даних. Розглянемо типові дії, які можна виконувати з даними: 1) упорядковувати рядки за зростанням чи спаданням значень у деякому стовпці; 2) шукати дані за деяким критерієм; 3) переглядати, створювати і шукати дані в ЕТ з великою кількістю стовпців і рядків за допомогою форм. Стовпець з даними тут називають полем.

Упорядкування. Спочатку вибирають частину таблиці з даними і назвами полів або всю таблицю (без заголовка таблиці і рядків з підсумками). Упорядкування виконують командами Дані /Упорядкувати (Сортувати). Отримують список назв полів, де вибирають потрібну для впорядкування назву, наприклад Місто, і задають порядок упорядкування: за зростанням чи спаданням — отримують таблицю, де рядки будуть упорядковані в алфавіт­ному чи зворотному порядку (тут назв міст).

Пошук даних називають інакше фільтруванням даних. Фільтри є трьох видів: простий фільтр (за деяким одним вибраним значенням чи маскою значень), автофільтр (за діапазоном значень деякого стовпця), розширений фільтр (за діапазонами значень декількох стовпців).

Маску значень утворюють так, як маску назв файлів в операційній системі: символ * позначає будь-яку кількість допустимих символів, а символ? — один деякий символ. Наприклад, маска *ий може позначати всі чоловічі прізвища, які закінчуються на -ий. Маска????? для імені позначатиме всі імена, що містять рівно п'ять літер: Петро, Роман, Ірина, а маска?ля означає імена Оля, Юля тощо.

Простий фільтр застосовують так: спочатку вибирають рядок, що містить назви стовпців, і виконують команди Дані / Фільтр. Клітинки з назвами стовпців стають списками з кнопками розгортання. Розгортають потрібний список, наприклад Ім'я, вибирають у списку деяке одне значення (наприклад, Петро) — отримаємо відомості про всіх осіб, яких звати Петро. Якщо ж значення не вибирати, а вибрати Умова чи в нових версіях вибрати Фільтри чисел чи Текстовий фільтр — відкриється вікно Автофільтра, що містить конструктор складних умов. Тут є зручні засоби для реалізації складних критеріїв пошуку даних в одному стовпці, наприклад, Січень. Приклад числової умови: більше 500000 і менше 2000000. Натискають на ОК і на екрані отримують результати пошуку — рядки таблиці з містами, де показник діяльності підприємства у січні задовольняв цьому критерію (50000<Січень<2000000).

В автофільтрі застосовують логічні операції і (and) та або (or), які ми детальніше розглянемо далі.

Щоб скасувати фільтрування і відновити на екрані всю таблицю, виконують команди Дані /Фільтр / Показати все (Скасувати) чи повторно клацають на стрічці на команді Фільтр.

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

Форми. Форма — це засіб подання даних з одного рядка таблиці на окремій сторінці. Форми корисні для роботи з великими таблицями. Для роботи з формами клацають у межах таблиці й виконують команди Дані / Форма.

У MS Excel 2007-2013 команди для роботи з формами немає. Цю команду спочатку потрібно додати до панелі швидкого доступу так: уверху вікна знайдіть і розгорніть список налаштування панелі швидкого доступу / виберіть команду ІНШІ команди... / у списку вибору команд увімкніть Усі команди /у кінці списку команд виберіть команду Форма / Додати / ОК.

Дані (рядки з таблиці) на формі можна переглядати, редагувати, вводити, вилучати, шукати відповідно до умов пошуку.

6. Підсумки в таблицях.

Проміжні підсумки — це технологія створення підсумків усередині таблиці з метою визначення ліпших, гірших, сумарних чи середніх показників діяльності підприємства чи його підрозділів у деяких країнах, містах тощо. Для цього спочатку рядки в таблиці впорядковують з метою групування (розташування поряд) даних, що стосуються кожної країни, міста чи підрозділу. Для отримання підсумків до вибраної впорядкованої таблиці застосовують команду Дані / Підсумки...(Проміжні підсумки). Отримують вікно, де задають: 1) назву поля, що містить об'єкти, для яких створюють підсумки, наприклад, Країна; 2) операцію підсумовування, наприклад, сума і 3) назву поля, що містить дані, які підлягають підсумовуванню (наприклад, Всього або/і Березень). Операції підсумовування є різні: сума, максимум, мінімум, середнє значення, відхилення від норми тощо. Скасувати підсумки можна командою Прибрати все.