Чи знаєте ви хитрий трюк створення посилання на заголовок стовпця в останній порожній клітинці рядка? Найкраще: Вам не потрібні допоміжні рядки або стовпці. Це так легко:
Об'єднайте чотири функції IFERROR (), INDEX (), MAX () та IF ()
Генеральний директор з продажу надішле вам перелік укладених щомісячно контрактів на продукти, які поступово припиняються ①. Ви повинні використовувати формулу у стовпці N, щоб вказати останній місяць продажів для кожного товару - без будь -яких допоміжних рядків або стовпців. Якщо більше не було укладено контрактів, введіть порожню клітинку у стовпець N.
Цей приклад, банальний на перший погляд, виявляється міцним горіхом, який можна зламати без використання допоміжних ліній або стовпців. як Excel для практики-Читачі ламають горіх! Ми маємо таку формулу масиву у клітинці для вирішення проблем N2 створено ②:
{= ПОМИЛКА (ІНДЕКС ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; СТОЛБА (B2: M2) -1; -1))); "")}}
Подивіться, як це працює поетапно
Почніть з умови IF (), вбудованої у формулу масиву: {= IFERROR (ІНДЕКС ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Умова IF () створює фіктивний допоміжний рядок у формулі масиву та перевіряє наявність комірок В2 до М2порожні вони чи ні. Якщо клітинка порожня, значення -1 повертається в іншому випадку за допомогою функції COLUMN (), номер відповідного стовпця мінус значення 1.
Віднімання 1 потрібно у формулі, оскільки перший стовпець у таблиці містить не назву місяця, а назву продукту. Нижче ви дізнаєтесь, як використовувати функцію INDEX () для відображення відповідної назви місяця, яка - якщо ви не відняли 1 - через додатковий стовпець, що використовується А. буде неправильним для одного стовпця.
Якщо всі клітинки в діапазоні В2: М2 є порожніми, значення -1 (немає продажів продукту) створює помилку, яку ми використовуємо для представлення порожньої клітинки. Активну допоміжну лінію можна побачити на рис. ③ у рядку 3.
На наступному кроці ви зчитуєте найбільше значення за допомогою функції MAX (), в якій умова IF () вкладена. Це значення 12 у рядку 3 (стовпець 13 мінус 1; див. Допоміжний рядок на малюнку ③):
{= ПОМИЛКА (ІНДЕКС ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", COLUMN (B2: M2) -1, -1)));"")}
Ви передаєте це значення MAX функції INDEX (). Після цього відповідний календарний місяць зачитується у рядку 1. Область даних функції INDEX () - це область $ B $ 1: $ M $ 1. Передане значення MAX - у прикладі 12 - означає, що дванадцяте значення у списку, тобто Груд за грудень місяць:
{= ПОМИЛКА(ІНДЕКС ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; СТОЛБА (B2: M2) -1; -1)));"")}
Якщо всі клітинки в місячному діапазоні таблиці порожні, то найбільше значення дорівнює -1 (див. Умову IF () на початку). Якщо значення -1 передається функції INDEX (), це неминуче призводить до значення помилки, оскільки область списку функції INDEX () містить лише дванадцять записів і тому не може знайти запис -1. Ви вловлюєте це значення помилки за допомогою функції IFERROR () і замість цього повертаєте порожній рядок. У прикладі це стосується продукту С у рядку 4, оскільки більше не можна укласти контракт на цей продукт:
{=ПОМИЛКА(ІНДЕКС ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", СТОЛОЧКА (B2: M2) -1, -1)));"")}
Оскільки це формула масиву, заповніть запис формули комбінацією клавіш Ctrl + Shift + Enter.
