Інформатика та комп’ютерна техніка

9.14.1. Основні відомості
Розглянемо таблицю:

Вихідними даними у таблиці є стовпчики Загальна вартість, Податок та Сума замовлення.
Обчислення провадится за формулами:
Загальна вартість = Ціна за одиницю*К-сть, тобто формула
=G11*F11 записується у комірку Н11 і копіюється вниз по комірках Н12:Н24.
Податок=Загальна вартість*18 %, тобто формула =Н11*$B$28 записується у комірку І11 і копіюється вниз по комірках І12:І24.
Сума замовлення=Загальна вартість+Сума замовлення, тобто формула =Н11+І11 записується у комірку J11 і копіюється вниз по комірках J12:J24.
Таблиця містить список клієнтів, що замовляли певні вироби протягом різних проміжків часу. Дані у таблиці не упорядковані, тому важко з’ясувати;

• хто з клієнтів, коли і скільки разів робив замовлення,
• які з виробів, коли, якими клієнтами та скільки разів замовлялися,
• кількість сплачених чи не сплачених замовлень,
• які форми розрахунку та за які вироби використовувались частіше тощо.

Упорядковуючи дані у таблиці за тими чи іншими ознаками, можна проаналізувати всі наведені вище запитання. Для цього спочатку треба виділити таблицю включно з заголовками, але без підсумкового рядка, у даному випадку — це діапазон А11:L24. Якщо таблиця містить багато даних, виділеному діапазону таблиці можна надати ім’я і в подальшому викликати потрібний діапазон за цим іменем.
9.14.2. Присвоєння імені для діапазону
Після виділення натискається ліва кнопка мишки у рядку формул ліворуч, де показана адреса поточної комірки — адреса виділиться синім кольором:

Замість виділеної адреси записують ім’я діапазону, наприклад База 1 і натискають Enter. Тепер, коли треба виділити діапазон, натискають на стрілку в зоні поточної адреси у рядку формул і вибирають із показаного списку потрібне ім’я.

Діапазон, якому було раніше надане це ім’я, буде виділений.
Для скасування імені вибирається пункт головного меню Вставка/Имя/Присвоить, відкриється вікно, де треба позначити ім’я, і натискають на кнопку Удалить і Ok

9.14.3. Сортування даних у виділеному діапазоні
Вибирається пункт головного меню Данные/Сортировка…
Відкриється вікно, де можна вибрати сортування за одною, двома чи трьома ознаками.
Необхідною і достатньою є тільки перша ознака — назва стовпчика, де дані упорядковуються у першу чергу. Якщо дані у цьому стовпчику повторюються, то можна вибрати другу ознаку — назву стовпчика, де дані будуть упорядковані серед тих, що повторюються у попередній ознаці. Якщо ж і тут є дані, що повторюються, то можна вибрати третю ознаку для сортування — назву стовпчика, де дані упорядковуватимуться серед тих, що повторювалися у попередній ознаці. Після вибору ознаки визначають спосіб упорядкування — за збільшенням чи за зменшенням і натискають Ok.
Наприклад, відсортувати наведену вище таблицю за прізвищами (в алфавітному порядку — тобто за збільшенням):
У вікні Сортировка диапазона визначають першу ознаку — зі списку Сортировать по… вибирають назву стовпчика Прізвище клієнта, встановлюється позначка По возрастанию і Ok:

Таблиця набуде такого вигляду:

З таблиці видно, що клієнти повторно замовляли деякі вироби протягом різних проміжків часу, тому доцільно було б відсортувати за другою ознакою — Назва виробу і третьою ознакою — Дата замовлення.
У вікні Сортировка диапазона додається друга ознака — Затем по… — стовпчик Назва виробу, і третя ознака — В последнюю очередь по… — стовпчик Дата замовлення. Усі ознаки упорядковуються за збільшенням і Ok:

Після сортування за трьома ознаками таблиця набуде такого вигляду:

9.14.4. Впровадження проміжних підсумків
В упорядковану таблицю можна впровадити проміжні підсумки: Загальну суму, Кількість, Середнє, Мінімальне, Максимальне значення по числових стовпчиках, що відповідають упорядкованому за першою ознакою стовпчику.
Наприклад, потрібно визначити Загальну кількість замовлених виробів, Загальну вартість, Загальний податок і Загальну суму замовлення по кожному із клієнтів. Тобто під групами однакових прізвищ мають бути вирахувані загальні суми — проміжні підсумки для цих клієнтів по стовпчиках Кількість, Загальна вартість, Податок і Сума замовлення.
Для впровадження таких проміжних підсумків виділяється діапазон База 1, викликається пункт головного меню Данные/Итоги…
З’явиться вікно:

У зоні При каждом изменении в… зі списку назв стовпчиків вибирають стовпчик Прізвище клієнта.
У зоні Операция… зі списку операцій вибирається Сумма.
У зоні Добавить итоги по… мишкою позначаються назви числових стовпчиків, у яких треба провести визначену вище операцію суми: це сповпчики К-сть, Загальна вартість, Податок і Сума замовлення. З усіх інших стовпчиків позначка знімається. Покажчик Заменять текущие итоги при першому створенні проміжних підсумків ролі не відіграє, і тому його можна залишити:

За один раз можна вибрати тільки одну операцію. Якщо ж потрібно крім суми, визначити ще і середнє, знову викликаються Данные/Итоги…, замість операції Сумма вибирають Среднее і знімають покажчик Заменять текущие итоги, бо, якщо його залишити, то тепер поточна операція суми заміниться операцією визначення середнього значення, а якщо зняти, то до поточної операції суми додасться ще й операція визначення середнього значення:

Таблиця з упровадженими проміжними підсумками показана нижче:

Кнопки з цифрами 1, 2, 3, 4 використовуються для відтворення детального чи стислого показу підсумків, а кнопки з мінусами ліворуч означають, що провадиться детальний показ.
При натисненні кнопки з цифрою 1 буде відтворений стислий показ — тільки основні підсумки, а кнопки з мінусами перетворяться на кнопку з плюсом:

(Якщо натиснути на кнопку з плюсом, то знову буде відтворений детальний показ для усіх підсумків).
Кнопка з цифрою 2 використовується для показу підсумків операції суми по групах:

У свою чергу, кнопка з цифрою 3 використовується для показу й операції суми та операції для визначення середнього значення по групах:

Кнопка з цифрою 4 знову відтворить повний показ усіх підсумків.
Натиснення кнопки з плюсом під кнопкою з цифрою 3 відтворить детальний показ по визначеній групі. Наприклад, якщо натиснути на кнопку з плюсом навпроти прізвищ Петров і Васильєв, то підсумки по цих прізвищах
будуть показані детально, а для всіх інших прізвищ —
стисло:

Таким чином, вибираючи ту чи іншу кнопку, можна проводити підсумковий аналіз даних у таблиці.
Таблицю з проміжними підсумками можна виділити і скопіювати на новий аркуш, при цьому вже не можна буде користуватися зміною показу, а доведеться задовольнитися тільки поточним переглядом скопійованих даних.
Для скасування проміжних підсумків, якщо вони вже
не потрібні, вибирається пункт меню Данные/Итоги..., і натискається кнопка Убрать все. Таблиця набуде вигля-
ду, який вона мала перед упровадженням проміжних під-
сумків.
9.14.5. Проведення деяких проміжних підсумків
за допомогою стандартних функцій
Microsoft Excel
Для підрахунку суми числових значень у певному діапазоні використовується функція СУММ (діапазон комірок), проте вона підраховує суму із усіх значень, розташованих у вказаному діапазоні. Коли ж потрібно підрахувати суму числових значень, які відповідають певній умові у таблиці, використовується функція СУММЕСЛИ, яка має такий вигляд:
=СУММЕСЛИ(діапазон для перевірки умови; «умова»; діапазон для підрахунку суми).
Принципом роботи функції є перегляд значень у діапазоні умови і, якщо є значення, що відповідають умові, вираховується по тих же рядках сума числових значень у діапазоні підрахунку суми.
Наприклад, за даними попередньої таблиці треба підрахувати суму податку, що сплатив клієнт Іванов.
Діапазоном для перевірки умови є стовпчик з прізвищами клієнтів — В12:В24, серед яких зустрічається (або ні) потрібне прізвище.
Умовою для пошуку є прізвище «Іванов», а діапазоном для підрахунку суми значень є стовпчик з нарахованими податками — І12:І24. Тоді функція набуває такого вигляду:
=СУММЕСЛИ(В12:В24;«Іванов»;І12:І24)

Після введення функції у вільну комірку таблиці буде отримано результат підрахунку:

Умова може бути не тільки текстовою, а й числовою: наприклад, підрахувати загальну кількість виробів, замовлених до 1998 року.
Діапазоном для перевірки умови буде стовпчик із датами замовлення — С12:С24, умовою мають бути дати до 1 січня 1998 року, тобто «<01.01.98», а діапазоном даних для суми є стовп­чик з кількістю виробів — F12:F24. Функція буде виглядати так:
=СУММЕСЛИ(С12:С24;«<01.01.98»;F12:F24)
Аналогічно для визначення загальної кількості замовлених принтерів функція СУММЕСЛИ матиме такий вигляд:
=СУММЕСЛИ(Е12:Е24;«Принтер»;F12:F24)
Інша функція для підрахунку з умовою — це функція СЧЕТ­ЕСЛИ:
=СЧЕТЕСЛИ(діапазон для перевірки умови; «умова»)
У процесі роботи цієї функції проглядається діапазон для перевірки умови і підраховується кількість виконань умови.
Наприклад,

• визначити, скільки разів робив замовлення клієнт Іванов:

Діапазон для перевірки — В12:В24, умова — «Іванов».
Функція має такий вигляд:
=СЧЕТЕСЛИ(В12:В24;«Іванов»)

• визначити, скільки разів робились замовлення до 1998 року:

Діапазон для перевірки — С12:С24; умова — «<01.01.98».
Функція має такий вигляд:
=СЧЕТЕСЛИ(С12:С24;«<01.01.98»)

• визначити, скільки разів замовлялися принтери:

Діапазон для перевірки — Е12:Е24, умова — «Принтер».
=СУММЕСЛИ(Е12:Е24;«Принтер»; F12:F24)
Нижче показані тексти функцій та результати підрахунків: