Формування проміжних підсумків

 

У Microsoft Excel можна обчислювати проміжні підсумки або підсумки для частин даних аркуша. Наприклад, на аркуші з даними продажу для трьох різних категорій товарів можна спочатку відсортувати товари за категоріями, а потім виділити всі клітинки, які містять дані, і відкрити діалогове вікно Проміжні підсумки (меню Дані, команда Підсумки).

У діалоговому вікні Проміжні підсумки можна вибрати стовпець, на даних якого базуватимуться проміжні підсумки (наприклад, за кожної зміни значення у стовпці «Тиждень»), потрібну операцію обчислення, а також стовпець або стовпці, за даними яких потрібно додати підсумки. Наприклад (як показано на попередньому малюнку), можна обчислити проміжні підсумки для кількості одиниць товару, проданого в кожній категорії. Визначені проміжні підсумки відобразяться на аркуші.

 

Як показано на попередньому малюнку, при додаванні проміжних підсумків до аркуша також визначаються групи, які базуються на рядках, використаних для обчислення підсумків. Групи формують структуру аркуша залежно від критеріїв, використаних для створення проміжних підсумків. Усі рядки з меблевою продукцією містяться в одній групі, рядки з устаткуванням - в іншій групі, і т.д. Розділ структури ліворуч від аркуша містить елементи керування, за допомогою яких можна приховати або відобразити групи рядків.

 

Існує три таких типи елементів керування в розділі структури.

Кнопки приховування подробиць Коли рядки групи відображено, кнопка приховування подробиць з'являється поруч із групою.

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

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

 

Нижче подана таблиця ідентифікує три рівні організації з попереднього малюнка.Рівень Опис

1 Загальний підсумок

2 Проміжні підсумки для кожної групи

3 Окремі рядки на аркуші

 

 

Якщо на аркуші, показаному на попередньому малюнку, натиснути кнопку рівня 2, то буде приховано рядки з даними продажу окремих товарів, але відображеними залишаться рядок із загальним підсумком (рівень 1) і всі рядки з проміжними підсумками для кожного продукту (рівень 2).

 

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

 

 

Можна автоматично обчислити проміжні та загальні підсумки у списку для

стовпця за допомогою команди Проміжні підсумки у групі Структура на вкладці Дані.

Проміжні підсумки обчислюються за допомогою таких функцій зведення, як, наприклад, Сума або Середнє, використовуючи

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

Загальні підсумки отримуються на основі докладних даних, а не

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

 

Якщо в книзі настроєно автоматичне обчислення формул, команда Проміжні підсумки автоматично переобчислюватиме значення проміжних і загальних підсумків під час змінення докладних даних. Команда Проміжні підсумки також структурує список, щоб можна було відображати та приховувати рядки з

докладними даними для кожного проміжного підсумку.

Переконайтеся, що кожний із стовпців містить назву в першому рядку, схожі факти в кожному зі стовпців, і що в діапазоні немає пустих рядків або стовпців.

Виділіть клітинку в діапазоні.

Виконайте одну з таких дій:

 

Вставлення одного рівня проміжних підсумків

 

Вставлення вкладених рівнів проміжних підсумків

 

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

 

 

Функції дати і часу та дії над ними

 

Для роботи з датами і часом використовується більше десятка функцій. Виберіть команду Функція (Function) у меню Вставка (Insert). У діалоговому вікні Майстер функцій — крок 1 з 2 (Insert Function) (мал. 16.6) у списку або категорію (Function category) виділите Дата і час (Date&Time).

Приклад використання функції Дата і час (Date&Time) розглянутий нижче (див. Вправи).

 

В Excel передбачено 14 функцій, що працюють з типом значень Дата и время (Date & Time). Розглянемо деякі з них.

 

СЕГОДНЯ (TODAY)

 

Функція СЕГОДНЯ (TODAY) не має аргументу. Вона повертає поточну дату, точніше, дату, встановлену в системі.

 

Наприклад, якщо користувач працює 20-го травня 1997 р., то наступна формула

 

=СЕГОДНЯ() або =TODAY()

 

поверне дату 20.05.97.

 

ДАТА (DATE)

 

Функція ДАТА (DATE) відтворює на екрані дату залежно від своїх трьох аргументів: рік, місяць і день. Ця функція допоможе скомпонувати дату на підставі інформації, поміщеної в робочу таблицю.

 

Наприклад, якщо клітинка А1 вміщує 1997, клітинка В1 - 06 і клітинка С1 - 25, то формула

 

=ДАТА(А1;В1;С1) або =DATE(А1;В1;С1)

 

поверне дату 25.06.97.

 

ДЕНЬ (DAY)

 

Функція ДЕНЬ (DAY) визначає день місяця в даті, що вказана. Наприклад, якщо клітинка А1 вміщує дату 25.12.97, то формула

 

=ДЕНЬ(А1) або =DAY(А1)

 

поверне 25.

 

Примітка. Крім цього, в Excel передбачені функції ГОД (YEAR) і МЕСЯЦ (MONTH), які виділяють з дати відповідно рік і місяць.

 

ДЕНЬНЕД (WEEKDAY)

 

Функція ДЕНЬНЕД (WEEKDAY) визначає день тижня в даті, що вказана. Вона має два аргументи: дату і код, що визначає тип результату (другий аргумент не обов'язковий). Коди запиту наведені в табл. 2.6.

 

Таблиця 2.6

 

Коди запитів функції ДЕНЬНЕД

 

 

Наприклад, якщо клітинка А1 вміщує 12.09.97, то формула

 

=ДЕНЬНЕД(А1;2) або =WEEKDAY(А1;2)

 

поверне число 4, яке визначає, що цей день був четвер.

 

ВРЕМЯ (TIME)

 

Функція ВРЕМЯ (TIME) повертає час залежно від своїх трьох аргументів: годин, хвилин і секунд. Ця функція допоможе скомпонувати час на підставі інформації, вміщеної в робочій таблиці.

 

Наприклад, якщо клітинка А1 вміщує 8, клітинка В1 - 15, а клітинка С1 - 0, то формула

 

=ВРЕМЯ(А1;В1;С1) або =TIME(А1;В1;С1)

 

поверне 8:15:00.

 

ЧАС (HOUR)

 

Функція ЧАС (HOUR) виділяє з часу кількість годин. Наприклад, якщо клітинка А1 вміщує час 8:15:00, то формула

 

=ЧАС(А1) або =HOUR(А1)

 

поверне 8.

 

 

Функції обробки масивів ЕТ

 

 

Щоб стати досвідченим користувачем програми Excel, потрібно навчитися використовувати формули масивів. За допомогою цих формул виконуються обчислення, які не можна зробити за допомогою звичайних формул. Ця стаття основана на серія статей для досвідчених користувачів програми Excel, які написав Колін Уілкокс, а також адаптовані розділи 14 і 15 книги Формули у програмі Excel 2002 (автор Джон Уокенбах, професіонал MVP у програмі Excel).

 

Якщо у вас є досвід використання формул у програмі Excel, ви вже знаєте, що вони дають змогу виконувати досить складні операції. Наприклад, можна обчислити загальну вартість позики на будь-яку кількість років. Однак якщо ви бажаєте опанувати формули Excel на фаховому рівні, вам потрібно навчитися працювати з формулами масивів. За допомогою цих формул можна виконувати складні завдання, зокрема:

підрахувати кількість символів у діапазоні клітинок;

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

підсумувати кожне n-е значення в діапазоні.

 

Примітка Іноді формули масивів називають "формулами CSE", оскільки для того, щоб ввести таку формулу, потрібно натиснути сполучення клавіш Ctrl+Shift+Enter.

Побіжний огляд масивів і формул масивів

 

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

 

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

 

У наступному розділі на прикладах показано, як створювати формули масивів для однієї та кількох клітинок.

 

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

 

Подібну техніку можна використовувати і для введення однотипних формул з обробки табличних даних. При введенні формули масиву Excel автоматично бере її у фігурні дужки {} після натиснення вказаних клавіш.

 

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

 

 

34. Типові алгоритми обробки одномірних масивів засобами електронних таблиць

 

Під поняттям алгоритм,як правило, мають на увазі "послідовність чітких недвозначних інструкцій, які зрозумілі виконавцеві і які призводять до певного результату за скінченний час".

 

1. Знайти суму та кількість від"ємних елементів масиву А(10)

 

1) B2:K2 <--- {=ЕСЛИ(B1:K1<0; B1:K1; "")}

 

2) B3<--- =СУММ(B2:K2)

 

3) B4<--- =СЧЕТ(B2:K2)

 

2. Знайти суму квадратів додатніх чисел масиву А(10) та масив відхилень масиву А від знайденої суми квадратів

 

1) B2:K2 <--- {=ЕСЛИ(B1:K1>0; B1:K1^2; "")}

 

2) B3<--- =СУММ(B2:K2)

 

3) B4:K4 <--- { =B1:K1-B3}

 

3. У вказаній комірці знайти координату(номер) нульового елемента масиву А(10)

 

1) B3:K3 <--- {=ЕСЛИ(B1:K1=0; B2:K2; "")}

 

2) B4 <--- = СУММ(B3:K3)

 

4. Знайти максимальний від"ємний елемент масиву А(10)

 

1) B2:K2 <--- {=ЕСЛИ(B1:K1<0; B1:K1; "")}

 

2) B3 <--- = МАКС(B2:K2)

 

5. Знайти суму перших N та добуток перших М елементів масиву А(10) у вказаних комірках

 

1) C5:L5<--- {=C1:L1+B5:K5}

 

2) C6:L6<--- {=ЕСЛИ(C2:L2=B3;C5:L5;"")}

 

3) B7<--- =МАКС(C6:L6)

 

4) C8:L8 <--- {=C1:L1*B8:K8}

 

5) C9:L9<--- {=ЕСЛИ(C2:L2=B4;C8:L8;"")}

 

6) B7<--- =МИН(C9:L9)

 

6. Методом часткових добутків знайти добуток від"ємних елементів масиву А(10)

 

1) C2:L2<--- {= ЕСЛИ(C1:L1<0; C1:L1*B2:K2;B2:K2)}

 

2) B3<--- =L2

 

7. В масиві А(10) знайти мінімальний парний елемент

 

1) B2:K2 <--- {=ЕСЛИ(B1:K1/2=ЦЕЛОЕ(B1:K1/2);B1:K1;"")}

 

2) B3<--- = МИН(B2:K2)

 

8. В масиві А(10) є декілька елементів, що мають однакове значення. Знайти це значення та координити елементів, які його мають.

 

1) B4:B13 <--- {=ТРАНСП(C3:L3)}

 

2) A4:A13 <--- {=ТРАНСП(C2:L2)}

 

3)C4:L13 <--- {=ЕСЛИ(C3:L3=B4:B13;ЕСЛИ(C2:L2<>A4:A13;C3:L3;"");"")}

 

4) C15 <--- =МАКС(C4:L13)

 

5) C16:L16 <--- {=ЕСЛИ(C3:L3=C15;C2:L2;"")}