Формули масиву в Excel
Під масивом зазвичай розуміють набір даних, об'єднаних у групу. Масиви бувають одновимірні (елементи масиву утворюють рядок або стовпець) або двовимірні (матриця). Легко збагнути, що майже в будь-якій таблиці Excel за бажання можна знайти один або кілька таких масивів:
Формули масиву
Excel - це спеціальні формули для обробки даних з таких масивів. Формули масиву поділяються на дві категорії - ті, що повертають одне значення і ті, що дають на виході цілий набір (масив) значень. Розглянемо їх у простих прикладах.
Приклад 1. Класика жанру – товарний чек
- виділяємо осередок С7
- вводимо з клавіатури = СУМ (
- виділяємо діапазон B2:B5
- вводимо знак множення (зірочка)
- виділяємо діапазон C2:C5 і закриваємо дужку функції СУМ - у результаті має вийти так:
Тобто. Excel здійснив попарне множення елементів масивів B2:B5 і C2:C5 і утворив новий масив цін (в пам'яті комп'ютера), а потім склав усі елементи цього нового масиву.
Зверніть увагу на фігурні дужки, що з'явилися у формулі - відмітна ознака формули масиву. Вводити їх вручну з клавіатури марно - вони автоматично з'являються при натисканні Ctrl+Shift+Enter.
Приклад 2. Дозвольте вам. транспонувати?
Працюючи з таблицями нерідко виникає необхідність змінити місцями рядки і стовпці, тобто. розгорнути таблицю на бік, щоб дані, які раніше йшли рядком, тепер розташовувалися в стовпцях і навпаки. У математиці така операція називається транспонування. За допомогою формули масиву та функції ТРАНСП (TRANSPOSE) це робиться на один раз.
Допустимо, маємо двовимірний масив осередків, який хочемо транспонувати.
- Виділяємо діапазон осередків розміщення транспонованої таблиці. Оскільки вихідний масив осередків був 8 рядків на 2 стовпці, треба виділити діапазон порожніх осередків розміром 2 рядки на 8 стовпців.
- вводимо функцію транспонування = ТРАНСП (
- як аргумент функції виділяємо наш масив осередків A1:B8
тиснемо Ctrl+Shift+Enter і отримуємо "перевернутий масив" як результат:
Редагування формули масиву
Якщо формула масиву розташована не в одному осередку (як у Прикладі 1), а в кількох осередках (як у Прикладі 2), то Excel не дозволить редагувати або видалити одну окремо взяту формулу (наприклад, в осередку D10) і видасть попереджувальне повідомлення Неможливо змінити частину масиву.
Для редагування формули масиву необхідно виділити весь діапазон (A10:H11 у нашому випадку) та змінити формулу в рядку формул (або натиснувши F2). Потім необхідно повторити введення зміненої формули масиву, натиснувши клавіші Ctrl+Shift+Enter.
Excel також не дозволить вільно переміщати комірки, що входять до формули масиву або додавати нові рядки-стовпці-комірки в діапазон формули масиву (тобто діапазон A10:H11 у нашому випадку)
Приклад 3. Таблиця множення
Згадайте дитинство, школу, свій зошит з математики. На звороті зошита на обкладинці було що? Таблиця множення такого виду:
За допомогою формул масиву вона робиться в один рух:
- виділяємо діапазон B2: K11
- вводимо формулу =A2:A11*B1:K1
- тиснемо Ctrl+Shift+Enter, щоб Excel сприйняв її як формулу масиву
і отримуємо результат:
Приклад 4. Вибіркове підсумовування
Подивіться як за допомогою однієї формули масиву красиво і легко вибираються дані щодо певного товару та замовника:
В даному випадку формула масиву синхронно пробігає по всіх елементах діапазонів C3:C21 і B3:B21, перевіряючи, чи вони збігаються із заданими значеннями з осередків G4 і G5. Якщо збігу немає, то результат рівності нуль, якщо збіг є, то одиниця. Таким чином суми всіх угод, де замовник не ANTON та товар не Boston Crab Meat множаться на нуль та підсумовуються тільки потрібні замовлення.
Посилання по темі
Як зробити вибірку в Excel за допомогою формул масиву
За допомогою засобів Excel можна здійснювати вибірку певних даних із діапазону у випадковому порядку, за однією умовою або декількома. Для вирішення подібних завдань використовуються, як правило, формули масиву чи макроси. Розглянемо на прикладах.
Як зробити вибірку в Excel за умовою
При використанні формул масиву відібрані дані відображаються в окремій таблиці. У чому полягає перевага даного способу в порівнянні зі звичайним фільтром.
Спочатку навчимося робити вибірку за одним числовим критерієм. Завдання - вибрати з таблиці товари з ціною вище 200 рублів. Один із способів вирішення – застосування фільтрації. В результаті у вихідній таблиці залишаться ті товари, які задовольняють запиту.
Інший спосіб розв'язання – використання формули масиву. Відповідні запиту рядки помістяться до окремого звіту-таблиці.
Спочатку створюємо порожню таблицю поруч із вихідною: дублюємо заголовки, кількість рядків та стовпців. Нова таблиця займає діапазон Е1: G10. Тепер виділяємо Е2: Е10 (стовпець «Дата») і вводимо таку формулу: < >.
Щоб вийшла формула масиву, натискаємо клавіші Ctrl + Shift + Enter. У сусідній стовпець - "Товар" - вводимо аналогічну формулу масиву: <>.Змінився лише перший аргумент функції ІНДЕКС.
У стовпець «Ціна» введемо таку саму формулу масиву, змінивши перший аргумент функції ІНДЕКС.
В результаті отримуємо звіт по товарах із ціною більше 200 рублів.
Така вибірка є динамічною: при зміні запиту або появі у вихідній таблиці нових товарів автоматично зміниться звіт.
Завдання №2 – вибрати з вихідної таблиці товари, що надійшли у продаж 20.09.2015. Тобто критерій відбору – дата. Для зручності шукану дату введемо в окрему комірку, I2.
Для розв'язання задачі використовується аналогічна формула масиву. Тільки замість критерію.
Подібні формули вводяться й у інші стовпці (принцип див. вище).
Тепер використовуємо текстовий критерій. Замість дати в осередок I2 введемо текст «Товар 1». Трохи змінимо формулу масиву: <>.
Така велика функція вибірки Excel.
Вибірка за кількома умовами в Excel
Спочатку візьмемо два числові критерії:
Завдання - відібрати товари, які коштують менше ніж 400 і більше 200 рублів. Об'єднаємо умови знаком «*». Формула масиву виглядає наступним чином: < = C2: C10); РЯДКУ (C2: C10); "");
Це для першого стовпця таблиці-звіту. Для другого та третього – змінюємо перший аргумент функції ІНДЕКС. Результат:
Щоб зробити вибірку за кількома датами або числовими критеріями, використовуємо аналогічні формули масиву.
Випадкова вибірка в Excel
Коли користувач працює з великою кількістю даних, для подальшого аналізу може знадобитися випадкова вибірка. Кожному ряду можна надати випадковий номер, а потім застосувати сортування для вибірки.
Вихідний набір даних:
Спочатку вставимо зліва два порожні стовпці. У комірку А2 впишемо формулу СЛЧИС (). Розмножимо її на весь стовпець:
Тепер копіюємо стовпець з випадковими числами та вставляємо його в стовпець Ст. Це потрібно для того, щоб ці числа не змінювалися під час внесення нових даних у документ.
Щоб вставити значення, а не формула, клацаємо правою кнопкою миші по стовпцю В і вибираємо інструмент «Спеціальна вставка». У вікні ставимо галочку навпроти пункту «Значення»:
Тепер можна відсортувати дані в стовпці В за зростанням або спаданням. Порядок уявлення вихідних значень також зміниться. Вибираємо будь-яку кількість рядків зверху чи знизу – отримаємо випадкову вибірку.
- Створити таблицю
- Форматування
- Функції Excel
- Формули та діапазони
- Фільтр та сортування
- Діаграми та графіки
- Зведені таблиці
- Друк документів
- Бази даних та XML
- Можливості Excel
- Налаштування параметри
- Уроки Excel
- Макроси VBA
- Завантажити приклади