Вивчіть цю цифру:
Припустимо, ви хочете скласти з цього звіт так, ніби ви фільтрували регіон. Тобто, якщо ви фільтруєте на північ, ви побачите:
Але що, якби ви хотіли версію того самого, що базується на формулі?
Ось результат, який ви шукаєте у стовпцях I: K:
Очевидно, це той самий звіт, але тут немає відфільтрованих елементів. Якщо ви хочете отримати новий звіт про Схід, було б непогано просто змінити значення в G1 на Схід:
Ось як це робиться. Перш за все, це не робиться за допомогою VLOOKUP. Тож я збрехав про назву цієї техніки!
Стовпець F раніше не відображався, і його можна приховати (або перемістити в інше місце, щоб це не заважало звіту).
У стовпці F показано номер рядка, де G1 знаходиться у стовпці A; тобто, які рядки містять значення «Північ»? Цей метод включає в себе використання осередку вище, тому вона повинна починатися принаймні в рядку 2. Вона збігається зі значенням «Північ» проти колонка А, але замість того , щоб весь стовпець, потрібно скористатися функцією OFFSET: OFFSET($A$1,F1,0,1000,1).
Оскільки F1 дорівнює 0, це те, OFFSET(A1,0,0,1000,1)що є A1: A1000. (1000 є довільним, але досить великим, щоб виконати роботу - ви можете зробити будь-яке інше число).
Значення 2 у F2 - це те місце, де знаходиться перший “північ”. Ви також хочете додати значення F1 наприкінці, але це поки що нуль.
“Магія” оживає у клітині F3. Ви вже знаєте, що перша північ знаходиться у рядку 2. Отже, ви хочете розпочати пошук у двох рядках нижче А1. Ви можете зробити це, вказавши 2 як другий аргумент функції OFFSET.
Формула в F3 автоматично вказуватиме на 2, яке було розраховано в комірці F2: Коли ви копіюєте формулу вниз, ви побачите =OFFSET($A$1,F2,0,1000,1), OFFSET($A$1,2,0,1000,1)що таке A3: A1000. Отже, ви порівнюєте Північ із цим новим діапазоном, і він знаходить Північ у третій комірці цього нового діапазону, тому МАТЧ дає 3.
Додавши назад значення з клітинки вище, F2, ви побачите 3 плюс 2 або 5, тобто рядок, що містить другий північ.
Ця формула заповнена досить далеко, щоб отримати всі значення.
Це дасть вам номери рядків, де знайдені всі записи Півночі.
Як перекласти ці номери рядків до результатів у стовпцях від I до K? Все це робиться за однією формулою. Введіть цю формулу в I2: =IFERROR(INDEX(A:A,$F2),””). Скопіюйте праворуч, а потім скопіюйте вниз.
Навіщо використовувати IFERROR? Де помилка? Зверніть увагу на клітинку F6 - вона містить # N / A (саме тому ви хотіли б приховати стовпець F), оскільки після рядка 15 більше немає півночі. Тож якщо стовпець F є помилкою, поверніть порожній. В іншому випадку візьміть значення зі стовпця A (а при заповненні праворуч B & C).
$ F2 є абсолютним посиланням на стовпець F, тому право заповнення все ще посилається на стовпець F.
Ця гостьова стаття від Excel MVP Боба Умласа. Це одна з його улюблених технік з його книги "Excel Outside the Box".
Excel нестандартно »
