Щоб стати досвідченим користувачем програми 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;"")}
