Управління масивами в Microsoft Excel
Під час роботи з таблицями Excel досить часто доводиться оперувати з цілими діапазонами даних. При цьому деякі завдання мають на увазі, що вся група осередків повинна бути перетворена буквально в один клік. У Ексель є інструменти, які дозволяють проводити подібні операції. Давайте з'ясуємо, як можна управляти масивами даних в цій програмі.
зміст
Операції з масивами
Масив - це група даних, яка розташована на аркуші в суміжних комірках. За великим рахунком, будь-яку таблицю можна вважати масивом, але не кожен з них є таблицею, так як він може бути просто діапазоном. За своєю сутністю такі області можуть бути одновимірними або двовимірними (матриці). У першому випадку всі дані розташовуються тільки в одному стовпці або рядку.
У другому - в декількох одночасно.
Крім того, серед одновимірних масивів виділяють горизонтальний і вертикальний тип, в залежності від того, що вони собою представляють - рядок або стовпець.
Потрібно відзначити, що алгоритм роботи з подібними діапазонами дещо відрізняється від більш звичних операцій з поодинокими осередками, хоча і спільного між ними теж багато. Давайте розглянемо нюанси подібних операцій.
створення формули
Формула масиву - це вираз, за допомогою якого проводиться обробка діапазону з метою отримання підсумкового результату, відображуваного цільним масивом або в одній комірці. Наприклад, для того, щоб помножити один діапазон на другий застосовують формулу за наступним шаблоном:
=адрес_массива1*адрес_массива2
Над діапазонами даних можна також виконувати операції додавання, віднімання, ділення і інші арифметичні дії.
Координати масиву мають вигляд адрес першої її осередки і останньої, розділені двокрапкою. Якщо діапазон двовимірний, то перша і остання осередки розташовані по діагоналі один від одного. Наприклад, адреса одновимірного масиву може бути таким: A2: A7.
А приклад адреси двовимірного діапазону виглядає наступним чином: A2: D7.
- Щоб розрахувати подібну формулу, потрібно виділити на аркуші область, в яку буде виводитися результат, і ввести в рядок формул вираз для обчислення.
- Після введення слід натиснути не на кнопку Enter, як зазвичай, а набрати комбінацію клавіш Ctrl + Shift + Enter. Після цього вираз в рядку формул буде автоматично взято в фігурні дужки, а осередки на аркуші будуть заповнені даними, отриманими в результаті обчислення, в межах всього виділеного діапазону.
Зміна вмісту масиву
Якщо ви надалі спробуєте видалити вміст або змінити будь-яку з осередків, яка розташована в діапазоні, куди виводиться результат, то ваша дія закінчиться невдачею. Також нічого не вийде, якщо ви зробите спробу відредагувати дані в рядку функцій. При цьому з'явиться інформаційне повідомлення, в якому буде говоритися, що не можна змінювати частина масиву. Дане повідомлення з'явиться навіть у тому випадку, якщо у вас не було мети робити будь-які зміни, а ви просто випадково двічі клацнули мишею по клітинці діапазону.
Якщо ви закриєте, це повідомлення, натиснувши на кнопку «OK», а потім спробуєте перемістити курсор за допомогою мишки, або просто натиснете кнопку «Enter», то інформаційне повідомлення з'явиться знову. Чи не вийде також закрити вікно програми або зберегти документ. Весь час буде з'являтися це настирливе повідомлення, яке блокує будь-які дії. А вихід з ситуації є і він досить простий
- Закрийте інформаційне вікно, натиснувши на кнопку «OK».
- Потім натиснете на кнопку «Скасування», яка розташована в групі значків зліва від рядка формул, і являє собою піктограму у вигляді хрестика. Також можна натиснути на кнопку Esc на клавіатурі. Після будь-якої з цих операцій відбудеться скасування дії, і ви зможете працювати з листом так, як і раніше.
Але що робити, якщо дійсно потрібно видалити або змінити формулу масиву? В цьому випадку слід виконати нижченаведені дії.
- Для зміни формули виділіть курсором, затиснувши ліву кнопку миші, весь діапазон на аркуші, куди виводиться результат. Це дуже важливо, тому що якщо ви виділите тільки одну клітинку масиву, то нічого не вийде. Потім в рядку формул проведіть необхідне коригування.
- Після того, як зміни внесені, набираємо комбінацію Ctrl + Shift + Esc. Формула буде змінена.
- Для видалення формули масиву треба точно так же, як і в попередньому випадку, виділити курсором весь діапазон комірок, в якому вона знаходиться. Потім натиснути на кнопку Delete на клавіатурі.
- Після цього формула буде видалена зі всієї області. Тепер в неї можна буде вводити будь-які дані.
функції масивів
Найбільш зручно в якості формул використовувати вже готові вбудовані функції Excel. Доступ до них можна отримати через Майстер функцій, натиснувши кнопку «Вставити функцію» зліва від рядка формул. Або ж у вкладці «Формули» на стрічці можна вибрати одну з категорій, в якій знаходиться цікавить вас оператор.
Після того, як користувач в Майстрі функцій або на стрічці інструментів вибере найменування конкретного оператора, відкриється вікно аргументів функції, куди можна вводити вихідні дані для розрахунку.
Правила введення і редагування функцій, якщо вони виводять результат відразу в кілька осередків, ті ж самі, що і для звичайних формул масиву. Тобто, після введення значення обов'язково потрібно встановити курсор в рядок формул і набрати поєднання клавіш Ctrl + Shift + Enter.
оператор СУММ
Однією з найбільш затребуваних функцій в Ексель є СУМ. Її можна застосовувати, як для підсумовування вмісту окремих осередків, так і для знаходження суми цілих масивів. Синтаксис цього оператора для масивів виглядає наступним чином:
=СУММ(массив1;массив2;…)
Даний оператор виводить результат в одну клітинку, а тому для того, щоб зробити підрахунок, після внесення вступних даних досить натиснути кнопку «OK» у вікні аргументів функції або клавішу Enter, якщо введення виконувався вручну.
урок: Як порахувати суму в Ексель
оператор ТРАНСП
Функція ТРАНСП є типовим оператором масивів. Вона дозволяє перевертати таблиці або матриці, тобто, міняти рядки і стовпці місцями. При цьому вона використовує виключно висновок результату в діапазон комірок, тому після введення даного оператора обов'язково потрібно застосовувати поєднання Ctrl + Shift + Enter. Також потрібно відзначити, що перед введенням самого виразу потрібно виділити на аркуші область, у якій кількість осередків в стовпці буде дорівнює числу осередків в рядку вихідної таблиці (матриці) і, навпаки, кількість осередків в рядку повинна дорівнювати їх числа в стовпці исходника. Синтаксис оператора наступний:
=ТРАНСП(массив)
урок: Транспонування матриць в Excel
урок: Як перевернути таблицю в Ексель
оператор МОБР
Функція МОБР дозволяє виробляти обчислення оберненої матриці. Всі правила введення значень у цього оператора точно такі ж, як і у попереднього. Але важливо знати, що обчислення оберненої матриці можливо тільки в тому випадку, якщо вона містить рівну кількість рядків і стовпців, і якщо її визначник не дорівнює нулю. Якщо застосовувати цю функцію до області з різною кількістю рядків і стовпців, то замість коректного результату на виході відобразиться значення «# значить!». Синтаксис у цієї формули такий:
=МОБР(массив)
Для того щоб розрахувати визначник, застосовується функція з наступним синтаксисом:
=МОПРЕД(массив)
урок: Зворотній матриця в Excel
Як бачимо, операції з діапазонами допомагають заощадити час при обчисленнях, а також вільний простір аркуша, адже не потрібно додатково підсумовувати дані, які об'єднані в діапазон, для подальшої роботи з ними. Все це виконується «на льоту». А для перетворення таблиць і матриць тільки функції масивів і підходять, так як звичайні формули не в силах впоратися з подібними завданнями. Але в той же час потрібно врахувати, що до подібних виразів застосовуються додаткові правила введення і редагування.