Створення та ведення списків – ЧАСТИНА 10

ДИВИСЬ ТАКОЖ

За додатковими відомостями про структурах звертайтеся до розділу «Структурування листів» глави 9

Рис 2834 Після приховування рядків рівня 2 ми бачимо тільки підсумкові дані

Підведення підсумків за кількома стовпцями

Припустимо, що ви хочете дізнатися сумарні виплати за одержувачам всередині кожної категорії витрат Для цього виберіть команду Підсумки в меню Дані ще раз У діалоговому вікні Проміжні підсумки вкажіть потрібний стовпець і підсумкову функцію (в даному випадку Одержувач і Сума) і обовязково зніміть прапорець Замінити поточні підсумки (Replace Current Subtotal) Результат продемонстрований на рис 2835

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

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

Автоматичні розриви сторінок

Прапорець Кінець сторінки між групами (Page Break Between Groups) у діалоговому вікні Проміжні підсумки (Subtotal) зазвичай стає актуальний при друку списків, що містять проміжні підсумки Якщо цей прапорець встановлений, Excel друкує кожну групу на окремій сторінці

Рис 2835 Тепер ми маємо підсумки по одержувачам всередині кожної категорії

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

Щоб видалити зі списку проміжні підсумки, а також структуру, відкрийте вікно Проміжні підсумки та натисніть кнопку Прибрати все (Remove All) Щоб замінити поточні проміжні підсумки, встановіть прапорець Замінити поточні підсумки того ж вікна

Угруповання по датах

Команда результаті не групує дані по датах Якщо потрібно отримати помісячні підсумки для списку, показаного на рис 2831, вам доведеться створити в списку додатковий стовпець Місяць (Спроба застосувати формат ммм до дат в стовпці А (або до колонку з копіював датами) і потім використовувати команду Підсумки не приведе до бажаного результату Excel переглядає фактичні значення осередків, а не відображаються)

Після вставки нового стовпця введіть у нього формулу у вигляді = МІСЯЦЬ (А2) Після цього введіть дані списку за номерами місяців При бажанні тшдеть назви місяців замість номерів використовуйте формулу виду = ВИБІР (МІСЯЦЬ (А2) Січень; лютого; Мар; ..), але це зажадає деякого часу на введення з клавіатури Краще рішення – вдатися до зведеної таблиці, яка дозволить вам групувати дані по роках, місяцях, тижнях та іншим тимчасових інтервалах

ДИВИСЬ ТАКОЖ

Додаткову інформацію наведено в розділі «Угруповання елементів за тимчасовими діапазонами» глави 30

Функція ПРОМЕЖУТОЧНИЕІТОГІ

Для виконання своїх обчислень команда Підсумки з меню Дані викликає функцію

ПРОМЕЖУТОЧНИЕІТОГІ (SUBTOTAL), що має наступний синтаксис

ПРОМЕЖУТОЧНИЕІТОГІ (Номер_функції посилання1 посилання2 ..)

Аргументпомер_фуікціівказує, яка функція повинна використовуватися при обчисленні підсумків Наприклад, формула

ПРОМІЖНІ ПІДСУМКИ (1 діапазон)

обчислює середнє значення для заданого діапазону комірок, а формула

ПРОМІЖНІ ПІДСУМКИ (9 діапазон)

підраховує суму

Відповідність значень аргументу Номер_функції і статистичних функцій наведено в табл 281

Таблиця 281 Функції, що викликаються з команди ПРОМІЖНІ ПІДСУМКИ

Номер функції Функція

1 або 101 СРЗНАЧ (AVERAGE)

2 або 102 РАХУНОК (COUNT)

3 або 103 СЧЕТЗ (COUNTA)

4 або 104 МАКС (МАХ)

5 або 105 MHH (MIN)

6 або 106 ПРОИЗВЕД (PRODUCT)

7 або 107 СТАНДОТКЛОН (STDEV)

8 або 108 СТАНДОТКЛОНП (STDEVP)

9 або 109 СУММ (SUM)

10ілі 110 ДИСП (VAR)

При застосуванні команди Підсумки вам не треба піклуватися про аргументи функції ПРОМЕЖУТОЧНИЕІТОГІ – Excel генерує потрібну форму без вашого прямого участі Але якщо ви створюєте власні формули із залученням цієї функції, важливо мати на увазі наступне:

► значення аргументу Номер_функції в діапазоні від 1 до 11 вказують Excel, що при обчисленні підсумків повинні виключатися рядки, приховані командами Автофільтр (AutoFUter) або Розширений фільтр (Advanced Filter)

► значення в діапазоні від 101 до 111 подразумевают, що при обчисленні підсумків повинні виключатися рядки, приховані командами Автофільтр або Розширений фільтр, а також командою Формат ► Рядок ► Приховати (Format ► Row ► Hide)

► значення від 101 до 111 генерують значення помилки # VALUE (# VALUE) У всіх версіях Excel до Excel 2003

► звичайні статистичні функції, на відміну від функції ПРОМІЖНІ ПІДСУМКИ, виконують обчислення на всіх рядках, незалежно від того, заховано останні чи ні

Функції в аналізі списків

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

Статистичні функції баз даних

Список статистичних функцій баз даних Excel включає в себе 12 назв – ДСРЗНАЧ (DAVERAGE), БСЧЕТ (DC0UNT), БСЧЕТА (DC0UNTA), БІЗВЛЕЧЬ (DGET), ДМАКС (DMAX), ДМІН (DMIN), БДПРОІЗВЕД (DPR0DUCT), ДСТАНДОТКЛ (DSTDEV), ДСТАНДОТКЛП (DSTDEVP), БДСУММ (DSUM), БДДІСП (DVAR) і БДДІСПП (DVARP) Кожна з цих функцій, за винятком БІЗВЛЕЧЬ, є аналогом «звичайної» статистичної функції: БДСУММ – варіація СУММ, БДДІСП – аналог ДИСП і т д

Різниця між ними і звичайними статистичними функціями полягає в тому, що D-функції (всі вони мають префікс D) оперують тільки з елементами списку, які задовольняють заданим умовам Ці умови задаються за допомогою діапазону умов точно так само, як при роботі з розширеним фільтром

ДИВИСЬ ТАКОЖ –

Додаткову інформацію наведено в розділі «Розширений фільтр»

Всі статистичні функції баз даних мають наступний синтаксис:

ДСРЗНАЧ (база_данних поле критерій)

Перший аргумент визначає проглядається список Другий аргумент задає поле (стовпець), з якого здобуваються значення Це може бути адреса комірки, що містить заголовок стовпця, текст заголовка стовпця в лапках або номер стовпчика в списку, починаючи з 1 Третій аргумент – посилання на діапазон комірок з умовами На відміну від інших програм електронних таблиць, Excel не дозволяє задавати умову як текстовий рядок всередині функції

Малюнок 2836 демонструє застосування функції ДСРЗНАЧ Для обчислення середньої заробітної плати співробітників у віці 40 років і старше ми склали наступну формулу:

= ДCP3HAЧ (A7: G24 Оклад; А1: А2)

За винятком БІЗВЛЕЧЬ, всі статистичні функції баз даних працюють аналогічним чином Функція БІЗВЛЕЧЬ повертає значення клітинки у стовпці, що задовольняє критеріям, заданим в діапазоні умов Якщо такий

осередки не виявлено, функція повертає помилку # VALUE (# VALUE) Коли на-

лежання умовам задовольняє кілька осередків, функція повертає помилку

# ЧІСЛ0 (# NUM)

Рис 2836 Статистичні функції баз даних зазвичай використовуються для аналізу тих даних списку, для яких дотримані певні умови Тут ми використовували функцію ДСРЗНАЧ для визначення середньої заробітної плати співробітників у віці 40 років і старше

Ви можете використовувати функцію ЕОШИБКА (ISERROR) для відстеження формули БІЗВЛЕЧЬ, що повертає значення помилки Наприклад, наступна формула повертає текст «Дані відсутні», якщо не виявлено жодної клітинки або, навпаки, знайдено кілька осередків, які відповідають умові

= ЕСПІ (ЕOШІБКА (БІЗВПЕЧЬ (А7: С24 Оклад; А1: А2) Дані відсутні;

БІЗВЛЕЧЬ (А7: G24 Оклад; А1: А2))

Щоб розрізнити два можливих типи помилки, включіть в цю формулу функцію

ОШІБКАТІП (ERRORTYPE) Вона повертає 3 для помилки # VALUE, І 6 для помилки

# ЧІСЛO

Функції СЧЕТЕСЛІ і СУММЕСЛІ

Функції СЧЕТЕСЛІ (COUNTIF) і СУММЕСЛІ (SUMIF) простіше у використанні, ніж статистичні функції баз даних, оскільки вони дозволяють задавати умови безпосередньо у формулі Правда, в цих функціях допускаються тільки прості умови порівняння

Функція СЧЕТЕСЛІ має наступний синтаксис:

= СЧЕТЕСЛІ (діапазон критерій)

Аргумент діапазон задає діапазон, в якому потрібно підрахувати кількість значень, а критерій – це текстове значення, що представляє умова Так, щоб підрахувати кількість співробітниць в списку, представленому на рис 2836, підходить формула

= СЧЕТЕСЛІ (Е7: Е24 Ж)

А для підрахунку числа співробітників у віці 45 років або старше підійде формула

=C4ETECnH(G7:G24&quot&gt=45&quot)

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

Синтаксис функції СУММЕСЛІ нагадує попередній випадок:

= СУММЕСЛІ (діапазон критерій діапазон_суммірованія)

Тут аргументкритерій– Це умова, що застосовується до діапазону, адіапазон підсумовування задає діапазон сумміруемих значень Наприклад, у списку з рис 2836 завданні обчислення загальних витрат на виплату заробітної плати співробітникам молодше 30 років відповідає формула

= CУMMECЛІ (G7: G24 <30"; F7: F24)

Оскільки середнє – це сума, поділена на кількість значень, в обчисленні середнього будуть брати участь обидві функції: СУММЕСЛІ і СЧЕТЕСЛІ Так, наступна формула повертає середню заробітну плату співробітників молодше 30 років:

= СУММЕСЛІ (G7: G24 <30"; F7: F24) / СЧЕТЕСЛІ (G7: G24; "<30")

Використання Майстра підсумовування

Майстер підсумовування, що є надбудовою, супроводжує процес підсумовування елементів, що задовольняють одному або декільком умовам Якщо він встановлений, ви можете його запустити за допомогою команди підміню Майстер підсумовування (Conditional Sum Wizard) меню Сервіс (Tools) Якщо ви не знайшли цієї команди, вам слід встановити надбудову Для цього виберіть команду Надбудови (Add-ins) в меню Сервіс та потім у списку надбудов встановіть прапорець Майстер підсумовування (Conditional Sum Wizard)

Однією з переваг майстра перед функцією СУММЕСЛІ є те, що він допускає поєднання декількох умов У другому діалоговому вікні Майстра підсумовування, показаному на рис 2837, ви можете задати умову, використовуючи списки, що розкриваються, натиснути кнопку Додати умова (Add Condition) і повторити цей процес для додаткових умов

Цікаво те, що Майстер підсумовування не генерує формули СУММЕСЛІ, а створює формули масиву При бажанні ближче познайомитися з роботою формул масиву ви можете досліджувати результати роботи майстра

Рис 2837 Задайте умови за допомогою розкривних списків

ДИВИСЬ ТАКОЖ

Додаткову інформацію про формули масиву см в розділі «Масиви» глави 12

Функція СЧІТАТЬПУСТОТИ

Функція СЧІТАТЬПУСТОТИ (діапазон) повертає кількість осередків в діапазоні, які є порожніми або містять формули, що повертають порожню текстову рядок Якщо ваш додаток неправильно працює через те, що в якихось шпальтах списку порожньо, організуйте сигнальні прапорці за допомогою цієї функції

Функції ВПР і ГПР

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

ВПР(искомое_значениетаблицаномер_столбца[тип_просмотра])

Аргументшукане_значення –значення, яке функція шукає,таблиця– Це діапазон, який містить таблицю з даними,помер_столбца задає стовпець, з якого вибирається повертається значення Необовязковий аргументmun_npoсмотра визначає спосіб пошуку значення

На рис 2838 показаний приклад застосування функції ВПР Тут формула = ВПР (14 таблиця 3) визначає величину податку на основі оподатковуваного доходу та категорії платника податків

Перший аргумент, I4, є посиланням на клітинку, яка містить суму доходу,

підлягає обкладенню, – в даному випадку $ 50 800 Ми присвоїли діапазону A3: F102

імя таблиця і використовували його в якості другого аргументу Третій аргумент, 3, вказує функції повернути значення з третього стовпця таблиці Функція ВПР переглядає самий лівий стовпець цього діапазону, стовпець А, і знаходить значення 50800 в рядку 19 Потім вона переміщається по цьому рядку до третього стовпцю таблиці, знаходить там значення 10819 і повертає його в якості результату

Рис 2838У цьому випадку функція ВПР повертає значення 10819 з третього стовпчика таблиці, так як воно знаходиться в тому ж рядку, в якій було знайдено значення комірки 14 (50800)

Якщо четвертий аргумент опущений або визначений як ІСТИНА, функція переглядає самий лівий стовпець, поки не знаходить найбільше значення, одночасно менше або рівне шуканого Коли для четвертого аргументу задано значення БРЕХНЯ, функція ВПР повертає # N / A (# N / A), якщо вона не знаходить точної відповідності в самому лівому стовпчику Якщо пошук здійснюється без точної відповідності (аргумент тіп_просмотра опущений або ІСТИНА), необхідно попередньо впорядкувати лівий стовпець по зростанню В іншому випадку сортування необовязкова Оскільки в наведеному прикладі ми обійшлися без цього аргументу, формула поверне 10819, якщо значення в комірці 14 буде перебувати в інтервалі від 50 800 до 50 859

Функцію ВПР можна використовувати і для пошуку тексту Колиіскомое_зіачеііе текстове, аргументутіп_просмотразазвичай присвоюють значення БРЕХНЯ, щоб функція повертала значення, грунтуючись на точному збігу текстів

Функція ГПР (HLOOKUP) ідентична функції ВПР, за винятком того, що вона працює з горизонтально орієнтованими, а не вертикальними таблицями Вона шукає значення у верхньому рядку такої таблиці і повертає значення з заданої рядки

Використання Майстра підстановок

Майстер підстановок, який є надбудовою, супроводжує процес пошуку значення у вертикальній таблиці, переглядаючи стовпець праворуч від деякого заданого значення таблиці Іншими словами, майстер надає спосіб, альтернативний побудови формули за участю функції ВПР Якщо надбудова встановлена, ви можете запустити майстер за допомогою команди Сервіс ► Майстер ► Пошук (Tools ► Wizard ► Lookup) Якщо ви не виявлено названої команди, значить, майстер не встановлений Виправте це упущення: виберіть команду Надбудови (Add-ins) меню Сервіс та потім у списку надбудов встановіть прапорець Майстер підстановок (Lookup Wizard)

У функціонуванні ВПР і Майстра підстановок є істотна відмінність: останній вимагає точного збігу в лівому стовпчику переглядається таблиці Причина в тому, що майстер генерує формулу, звертаючись до функцій ІНДЕКС та П0ІСКПО3 замість ВПР і ГПР

Функції ПОИСКПОЗ і ІНДЕКС

Функція ПОИСКПОЗ (MATCH), на відміну від ВПР і ГПР, повертає порядковий номер в діапазоні, в якому шукає іскомое_зпачеііе Тобто, наприклад, якщо шукане значення виявляється пятим елементом у діапазоні, на виході функції ПОИСКПОЗ ви побачите число 5, а не значення пятого елементу Функція ПОИСКПОЗ має наступний синтаксис:

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

Аргумент шукане значення ~ число, або текст, або посилання на осередок, що містить це значення Аргументпросматріваемий_массівможе бути стовпцем, рядком або масивом значень, що задаються безпосередньо у функції Необовязковий аргумент тип зіставлення,за замовчуванням рівний 1, визначає спосіб пошуку значення за правилами, описаним в табл 282

Таблиця 282 Способи застосування функції ПОИСКПОЗ

Аргумент Опис

1 Функція шукає в відсортованому по зростанню масиві найбільше значення, одночасно менше або рівне шуканого

0 Пошук першого значення, точно збігається з шуканим (проглядається масив в цьому випадку не обовязково повинен бути відсортований) Якщо ні одне значення не задовольняє вимогам, функція повертає помилку # N / Д

-1 Функція шукає в відсортованому за спаданням масиві найменше значення,

яке більше шуканого або дорівнює йому

Функція ПОИСКПОЗ часто виступає в ролі аргументу функції ІНДЕКС (INDEX) Остання має дві синтаксичні форми Одна завжди повертає значення або масив, а інша завжди повертає посилання на клітинку або діапазон Синтаксис функції ІНДЕКС наступний:

ІНДЕКС (масив, номер_строкі номер_стовпчика)

ИНДЕКС(ссылканомер_строкиномер_столбца[номер_области])

Аргументмасив-> діапазон або масив значень, заданий всередині функції Аргументпосиланняможе бути представлений одним або декількома несуміжними діапазонами якщо задано декілька діапазонів, аргумент помер_області вказує, в якому з них потрібно виконувати пошук

Якщо масивабопосиланнямістять тільки один рядок або один стовпець, відповідний аргументпомер_строкіабопомер_столбцаможна опустити Коли комбінація аргументівномер_строкііномер_стовпчикавказує на клітинку або значення, що лежать поза діапазону або масиву, функція повертає значення # посилаючись (# REF)

На рис 2839 до складу формули в комірці D2 включені функції П0ІСКПОЗ і ІНДЕКС

з метою знаходження величини окладу конкретної співробітниці – Єгорової

= ІНДЕКС (А6: G26 ПОИСКПОЗ (С2 А6: А26 0) П0ІСКПОЗ (D1 А6: G6 0))

Рис 2839 Ми використовували функції ПОИСКПОЗ і ІНДЕКС для знаходження значення у списку персоналу на певному перетині рядка і стовпця

Як аргументмасивфункції ІНДЕКС фігурує діапазон A6: G26, що охоплює список співробітників Аргументіомер_строкіотримує значення 22, яке повертає функція ПОІСКПО3 (С2 А6: А26 0), оскільки Єгорова (вміст комірки А2) знаходиться на 22-й позиції в несортоване списку Звідси, аргумент  номер_стовпчикаотримує значення 6, яке повертає функція ПОИСКПОЗ (D1 A6: G6 0), оскільки Оклад (значення комірки D1) займає 6-у позицію в діапазоні A6: G6 Відповідно функція ІНДЕКС повертає 51825, значення, що лежить на перетині рядка 22 і шпальти 6 діапазону A6: G26

Якщо помер_строкі абопомер_столбца дорівнює 0, функція ІНДЕКС повертає масив значень для цілого стовпця або цілої рядки відповідно Таким чином, можна

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

= ІНДЕКС (А6: С26 ПОИСКПОЗ (Єгорова; А6: А26 0) 0)

Зверніть увагу, що у функцію ПОИСКПОЗ ми включили прирівняний нулю аргумент тіп_сопоставленія, так як при пошуку нам потрібно точне збіг з текстовим значенням

Створення елемента керування списком

Озброївшись функцією ІНДЕКС, Ви можете створити зручний елемент управління типу

«Список» для перегляду даних Малюнок 2840 ілюструє цю ідею Ми створили такий «список» і помістили його поверх осередку D4 робочого аркуша Цей елемент управління повязаний з невидимою осередком В5, яка стиснута у вузьку лінію і закрита сірою смугою Форма розміщена в діапазоні B6: G15 листа Форма, а значення комірки В5 передається у вигляді аргументу функції ІНДЕКС (клітинки у стовпцях D і G), яка витягує значення зі списку, розташованого на аркуші Таблиця Наприклад, в D6 занесена формула = ІНДЕКС (Табліца $ А $ 2: $ і $ 16 Форма! $ В $ 5 1)

Рис 2840 Елемент управління типу «список» в комірці D4 повязаний з осередком В5

Оскільки в даний момент вибрано імя Acero Markas Endora 2304, яке є першим елементом списку, комірка В5 містить значення 1 Відповідно формула

ІНДЕКС повертає значення з комірки, що знаходиться на перетині рядка 1 і шпальти 1 в діапазоні таблиця $ А $ 2: $ U $ 16 Формула в D7 виглядає так само, як в D6, з тим розходженням, що аргумент помер_столбца дорівнює 2 і т д

Щоб створити елемент управління «список» на звичайному робочому листі, виконайте наступні кроки:

1 Виберіть команду Вид ► Панелі інструментів ► Форми (View ► Toolbars ►

Forms)

2 На панелі інструментів Форми (Forms) виберіть елемент Список (List Box) і очер-

титі прямокутник для розміщення елемента управління на робочому листі

3 Клацніть правою кнопкою миші на новому елементі управління та виберіть команду

Формат обєкта (Format Control)

4 На вкладці Елемент управління (Control) діалогового вікна Форматування обєкта (Format Control) (рис 2841) заповніть поля Формувати список за діапазоном (Input Range) і Звязок із клітинкою (Cell Link) Поле Формувати список за діапазоном визначає діапазон аркуша, що містить дані, які будуть зявлятися в списку У полі Звязок із клітинкою потрібно вказати комірку, куди повинна записуватися позиція елемента у вихідному діапазоні, в поточний момент обраного в елементі управління «список»

5 Встановіть перемикач в положення Одинарное значення (Single), щоб користувач міг вибирати у списку не більше одного елемента за раз

6 Натисніть кнопку ОК

Щоб зробити елемент управління «список» більш звичним, без таких атрибутів електронних таблиць, як заголовки рядків і стовпців, виберіть команду Параметри в меню Сервіс та на вкладці Вид відкрився вікна зніміть прапорець Заголовки рядків і стовпців (Row & Column Headers)

Рис 2841 Налаштований таким чином елемент управління «список» буде відображати дані з діапазону Таблиця $ А $ 2: $ 11 $ 16 Номер вибраного елемента (його позиція в списку) буде записуватися в клітинку В5

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*