Знайти перше непусте значення у рядку
Завдання: потрібна формула, яка дозволяла знайти перше непусте значення в рядку, тобто повертала б номер першого непустого осередку в рядку. Припустимо, дані представлені в стовпцях З:K (рис. 1).
Мал. 1. Формула знаходить перший непустий осередок у кожному рядку і повертає її номер у масиві
Завантажити нотатку у форматі Word або pdf, приклади у форматі Excel
Рішення: формула в А2: = ПОШУКПОЗ (1; ІНДЕКС (1-ЕПУСТО (C2: K2); 1; 0); 0). Хоча ця формула має справу з масивом осередків, вона зрештою повертає одне значення, так що використовувати при введенні натискання Ctrl+Shift+Enter не потрібно (про формули масиву див. Майкл Гірвін. Ctrl+Shift+Enter. Освоєння формул масиву в Excel ).
Розглянемо роботу формули докладніше. Функція ЕПУСТО повертає ІСТИНА, якщо осередок є порожнім, і БРЕХНЯ, якщо осередок – не порожній. Подивіться рядок даних у С2:К2. ЕПУСТО (С2: К2) поверне масив: .
Зверніть увагу, що далі цей масив віднімається з 1. При спробі використовувати значення ІСТИНА і Брехня в математичній формулі, значення ІСТИНА інтерпретується як 1, а значення Брехня – як 0. Задаючи 1-ЕПУСТО(С2:К2), ви перетворюєте масив логічних значень ІСТИНА/БРЕХНЯ в числову послідовність нулів та одиниць: .
Отже, фрагмент формули 1-ЕПУСТО(С2:К2) повертає масив. Це трохи дивно, тому що від такого фрагмента Excel очікує, що повернеться одне значення. Дивно, але не смертельно. Функція ІНДЕКС зазвичай повертає одне значення. Але ось, що написано в Довідці Excel: Якщо вказати як аргумент рядка або номер стовпця значення 0 (нуль), функція ІНДЕКС поверне масив значень для цілого стовпця або цілого рядка відповідно. Щоб використати значення, повернуті як масив, введіть функціюІНДЕКС як формулу масиву горизонтальний діапазон осередків для рядка і вертикальний — для стовпця.
Якщо функція ІНДЕКС повертає масив, її можна використовувати всередині інших функцій, які очікують, що аргумент є масивом.
Отже, вказавши як третій аргумент функції ІНДЕКС(1-ЕПУСТО(C2:K2);1;0 ) значення нуль, ми отримаємо масив .
Функція ПОШУКПОЗ виконує пошук шуканого значення в одновимірному масиві та повертає відносну позицію першого знайденого збігу. Формула = ПОШУКПОЗ (1, МАСИВ, 0) просить Excel знайти номер осередку в МАСИВІ, яка містить першу одиницю, що зустрілася. Функція ПОШУКПОЗ визначає, у якому стовпці міститься перший непустий осередок. Коли ви просите пошук знайти першу 1 в масиві , вона повертає 3.
Отже =ПОШУКПОЗ(1;ІНДЕКС(1-ЕПУСТО(C2:K2);1;0);0) перетворюється на =ПОШУКПОЗ(1;;0) і повертає результат 3.
У цей момент ви знаєте, що третій стовпець рядка С2:К2 містить перше непусте значення. Звідси досить просто, використовуючи функцію ІНДЕКС, дізнатися саме це перше непусте значення: = ІНДЕКС (МАСИВ; 1; 3) або = ІНДЕКС (C2: K2; 1; ПОШУКПОЗ (1; ІНДЕКС (1-ЕПУСТО (C2: K2); 1) ;0);0)).
Мал. 2. Формула знаходить перший непустий осередок у кожному рядку і повертає значення цього осередку
Додаткові відомості: якщо всі комірки порожні, формула повертає помилку #Н/Д.
Альтернативні стратегії: коли ви з одиниці віднімаєте значення ЕПУСТО, ви перетворюєте логічні значення ІСТИНА/БРЕХНЯ в числові 1/0. Ви могли б пропустити цей крок, але тоді вам доведеться шукати Брехня в якості першого аргументу функція ПОШУКПОЗ: =ІНДЕКС(C2:K2;1;ПОШУКПОЗ(БРЕХНЯ;ІНДЕКС(ЕПУСТО(C2:K2);1;0);0)) .