Допустимо ваш звіт містить таблицю з великою кількістю даних на множину стовпців. Проводити візуальний аналіз таких таблиць вкрай складно. А одним із завдань по роботі зі звітом є аналіз даних щодо заголовків рядків і стовпців, що стосуються певного місяця. На перший погляд, це дуже просте завдання, але його не можна вирішити, використовуючи одну стандартну функцію. Так, звичайно, можна скористатися інструментом: «ГОЛОВНА»-«Редагування»-«Знайти» CTRL+F, щоб викликати вікно пошуку значень на аркуші Excel. Або створити для таблиці правило умовного форматування. Але тоді не можна буде виконати подальші обчислення з отриманими результатами. Тому необхідно створити та правильно застосувати відповідну формулу.
Пошук значення в масиві Excel
Схема вирішення завдання виглядає приблизно так:
- в комірку B1 ми будемо вводити дані, що цікавлять нас;
- у комірці B2 буде відображено заголовок стовпця, який містить значення комірки B1
- у комірці B3 буде відображатися назва рядка, що містить значення комірки B1.
Фактично необхідно виконати пошук координат Excel. Для чого це потрібно? Досить часто нам потрібно одержати координати таблиці за значенням. Дещо нагадує зворотний аналіз матриці. Конкретний приклад двома словами виглядає приблизно так. Поставлена мета у цифрах є вихідним значенням, потрібно визначити, хто і коли найбільш наближений до цієї мети. Наприклад використовуємо просту матрицю даних зі звітом за кількістю проданих товарів за три квартали, як показано на малюнку нижче. Важливо, щоб усі числові показники збігалися. Якщо немає бажання вручну створювати та заповнювати таблицю Excel із чистого аркуша, то наприкінці статті можна завантажити вже з готовим прикладом.
Послідовно розглянемо варіанти розв'язання різної складності, а наприкінці статті – фінальний результат.
Пошук значення у стовпці Excel
Спочатку навчимося отримувати заголовки стовпців таблиці за значенням. Для цього виконайте такі дії:
- У комірку B1 введіть значення взяте з таблиці 5277 і виділіть її фон синім кольором для читабельності поля введення (далі вводимо в комірку B1 інші числа, щоб експериментувати з новими значеннями).
- У комірку C2 вводимо формулу для отримання заголовка стовпця таблиці, який містить це значення:
- Після введення формули для підтвердження натискаємо комбінацію гарячих клавіш CTRL+SHIFT+Enter, оскільки формула має бути виконана в масиві. Якщо все зроблено правильно, у рядку формул по краях з'являться фігурні дужки ( ).
Пошук значення у рядку Excel
Тепер отримаємо номер рядка для цього значення (5277). Для цього в комірку C3 введіть таку формулу:
Після введення формули для підтвердження знову натискаємо комбінацію клавіш CTRL+SHIFT+Enter та отримуємо результат:
Формула повернула номер 9 – знайшла заголовок рядка аркуша за відповідним значенням таблиці. В результаті ми маємо повну адресу значення D9.
Як отримати заголовок стовпця та назву рядка таблиці
Тепер навчимося отримувати за значенням координати не цілого аркуша, а поточної таблиці. Одним словом, нам потрібно знайти за значенням 5277 замість D9 отримати заголовки:
- для стовпця таблиці – Березень;
- для рядка - Товар4.
Щоб вирішити це завдання будемо використовувати формулу з вже отриманими значеннями в осередках C2 і C3. Для цього робимо так:
В результаті отримано внутрішні координати таблиці за значенням – березень; Товар 4:
Пошук однакових значень у діапазоні Excel
Щоб проконтролювати наявність дублікатів серед значень таблиці, створимо формулу, яка зможе інформувати нас про наявність дублікатів та підраховувати їх кількість. Для цього в комірку E2 вводимо формулу:
Більше того, для діапазону табличної частини створимо правило умовного форматування:
Як видно за наявності дублікатів формула для заголовків бере заголовок з першого дубліката по горизонталі (зліва направо). А формула для отримання назви (номера) рядка бере номер із першого дубліката по вертикалі (згори донизу). Для виправлення цього рішення є 2 шляхи:
У цьому випадку змінюємо формули чи одну чи іншу, але з дві одночасно. У комірці С3 повинна залишатися стара формула:
Тут правильно відображаються координати першого дубліката по вертикалі (згори донизу) – I7 для аркуша та Серпень; Товар2 для таблиці. Залишимо такий варіант для наступного завершального прикладу.
Пошук найближчого значення в діапазоні Excel
Ця таблиця все ще не досконала. Адже при аналізі потрібно точно знати її значення. Якщо введене число у комірку B1 формула не знаходить у таблиці, тоді повертається помилка – #ЗНАЧ! Ідеально було б, щоб формула за відсутності в таблиці вихідного числа сама підбирала найближче значення, яке містить таблиця. Щоб створити таку програму для аналізу таблиць у клітинку F1, введіть нову формулу:
Після чого слід у всіх інших формулах змінити посилання замість B1 має бути F1!Також потрібно змінити посилання в умовному форматуванні. Виберіть: «ГОЛОВНА»-«Стилі»-«Умовне форматування»-«Управління правилами»-«Змінити правило». І тут у параметрах вкажіть F1 замість B1. Щоб перевірити роботу програми, введіть у комірку B1 число якої немає в таблиці, наприклад: 8000. Це призведе до завершального результату:
Тепер можна вводити будь-яке вихідне значення, а програма сама підбере найближче число, яке містить таблиця. Після цього виводить заголовок стовпця та назву рядка для поточного значення. Наприклад, якщо ввести число 5000 отримуємо новий результат:
Наша програма в Excel знайшла найближче значення 4965 для вихідного - 5000. Така програма може стати в нагоді для автоматичного вирішення різних аналітичних завдань при бізнес-плануванні, постановки цілей, пошуку раціонального рішення і т.п. А отримані рядки та стовпці дозволяють далі розширювати обчислювальні можливості такого роду звітів за допомогою нових формул Excel.
Цей підручник розповідає про головні переваги функцій ІНДЕКСі ПОШУКПОЗв Excel, які роблять їх більш привабливими в порівнянні з ВВР. Ви побачите кілька прикладів формул, які допоможуть Вам легко впоратися з багатьма складними завданнями, перед якими функція ВВРбезсила.
У кількох недавніх статтях ми доклали всіх зусиль, щоб роз'яснити початківцям основи функції ВВРі показати приклади складніших формул для просунутих користувачів. Тепер ми спробуємо, якщо не відмовити Вас від використання ВВР, хоча б показати альтернативні способи реалізації вертикального пошуку в Excel.
Навіщо це нам? - Запитайте Ви. Та тому що ВВР– це не єдина функція пошуку в Excel, і її численні обмеження можуть завадити Вам отримати бажаний результат у багатьох ситуаціях. З іншого боку, функції ІНДЕКСі ПОШУКПОЗ- Гнучкіші і мають ряд особливостей, які роблять їх більш привабливими, в порівнянні з ВВР.
Базова інформація про ІНДЕКС та ПОШУКПОЗ
Оскільки завдання цього підручника – показати можливості функцій ІНДЕКСі ПОШУКПОЗДля реалізації вертикального пошуку в Excel, ми не будемо затримуватись на їх синтаксисі та застосуванні.
Наведемо тут необхідний мінімум для розуміння суті, а потім докладно розберемо приклади формул, які показують переваги використання ІНДЕКСі ПОШУКПОЗзамість ВВР.
ІНДЕКС – синтаксис та застосування функції
Функція INDEX(ІНДЕКС) Excel повертає значення з масиву за заданими номерами рядка і стовпця. Функція має такий синтаксис:
Кожен аргумент має дуже просте пояснення:
- array(Масив) - це діапазон осередків, з якого необхідно отримати значення.
- row_num(Номер_рядка) – це номер рядка в масиві, з якого потрібно отримати значення. Якщо не вказано, то обов'язково потрібен аргумент column_num(Номер_стовпця).
- column_num(Номер_стовпця) – це номер стовпця в масиві, з якого потрібно отримати значення. Якщо не вказано, то обов'язково потрібен аргумент row_num(номер рядка)
Якщо вказано обидва аргументи, то функція ІНДЕКСповертає значення з комірки, що знаходиться на перетині зазначених рядків та стовпців.
Ось найпростіший приклад функції INDEX(ІНДЕКС):
INDEX (A1: C10, 2, 3)
= ІНДЕКС (A1: C10; 2; 3)
Формула виконує пошук у діапазоні A1:C10і повертає значення комірки в 2-йрядку та 3-мстовпці, тобто з комірки C2.
Дуже просто, правда? Однак, на практиці Ви далеко не завжди знаєте, які рядок та стовпець Вам потрібні, і тому потрібна допомога функції ПОШУКПОЗ.
ПОШУКПОЗ – синтаксис та застосування функції
Функція MATCH(ПОШУКПОЗ) в Excel шукає вказане значення в діапазоні осередків і повертає відносну позицію цього значення в діапазоні.
Наприклад, якщо в діапазоні B1:B3містяться значення New-York, Paris, London, тоді наступна формула поверне цифру 3 оскільки “London” – це третій елемент у списку.
MATCH("London",B1:B3,0)
=ПОШУКПОЗ("London";B1:B3;0)
Функція MATCH(ПОШУКПОЗ) має такий синтаксис:
MATCH(lookup_value,lookup_array,)
ПОШУКПОЗ(шукане_значення;проглядається_масив;[тип_порівнювання])
- lookup_value(шукане_значення) – це число або текст, який Ви шукаєте. Аргумент може бути значенням, у тому числі логічним, або посиланням на комірку.
- lookup_array(Масив, що переглядається) - діапазон осередків, в якому відбувається пошук.
- match_type(тип_сопоставления) – цей аргумент повідомляє функції ПОШУКПОЗ, чи Ви хочете знайти точний або приблизний збіг:
- 1 або не вказано- Знаходить максимальне значення, менше або рівне шуканому. Масив, що переглядається, повинен бути впорядкований за зростанням, тобто від меншого до більшого.
- 0 - Знаходить перше значення, рівне шуканому. Для комбінації ІНДЕКС/ПОШУКПОЗзавжди потрібен точний збіг, тому третій аргумент функції ПОШУКПОЗповинен дорівнювати 0 .
- -1 - Знаходить найменше значення, більше або дорівнює шуканому значенню. Масив, що переглядається, повинен бути впорядкований за зменшенням, тобто від більшого до меншого.
На перший погляд, користь від функції ПОШУКПОЗвикликає сумнів. Кому потрібно знати розташування елемента в діапазоні? Ми хочемо знати значення цього елемента!
Дозвольте нагадати, що відносне положення шуканого значення (тобто номер рядка та/або стовпця) – це те, що ми повинні вказати для аргументів row_num(номер_рядки) та/або column_num(номер_стовпця) функції INDEX(Індекс). Як Ви пам'ятаєте, функція ІНДЕКСможе повернути значення, що знаходиться на перетині заданих рядки та стовпця, але вона не може визначити, які саме рядок та стовпець нас цікавлять.
Як використовувати ІНДЕКС та ПОШУКПОЗ в Excel
Тепер, коли Вам відома базова інформація про ці дві функції, вважаю, що вже стає зрозуміло, як функції ПОШУКПОЗі ІНДЕКСможуть працювати разом. ПОШУКПОЗвизначає відносну позицію шуканого значення в заданому діапазоні осередків, а ІНДЕКСвикористовує це число (або числа) і повертає результат із відповідного осередку.
Ще не зовсім зрозуміло? Уявіть функції ІНДЕКСі ПОШУКПОЗу такому вигляді:
INDEX (, (MATCH ( потрібне значення,стовпець в якому шукаємо,0))
= ІНДЕКС ( стовпець з якого витягуємо;(ПОШУКПОЗ( потрібне значення;стовпець в якому шукаємо;0))
Думаю, ще простіше буде зрозуміти на прикладі. Припустимо, у Вас є такий список столиць держав:
Давайте знайдемо населення однієї зі столиць, наприклад, Японії, використовуючи таку формулу:
INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ІНДЕКС($D$2:$D$10;ПОШУКПОЗ("Japan";$B$2:$B$10;0))
Тепер давайте розберемо, що робить кожен елемент цієї формули:
- Функція MATCH(ПОШУКПОЗ) шукає значення “Japan” у стовпці B, а саме – в осередках B2: B10, і повертає число 3 , оскільки “Japan” у списку третьому місці.
- Функція INDEX(ІНДЕКС) використовує 3
для аргументу row_num(номер_рядка), який вказує з якого рядка потрібно повернути значення. Тобто. виходить проста формула:
INDEX($D$2:$D$10,3)
=ІНДЕКС($D$2:$D$10;3)Формула каже приблизно таке: шукай у осередках від D2до D10і витягни значення з третього рядка, тобто з комірки D4, Оскільки рахунок починається з другого рядка.
Ось такий результат вийде в Excel:
Важливо! Кількість рядків та стовпців у масиві, який використовує функція INDEX(ІНДЕКС), має відповідати значенням аргументів row_num(номер_рядки) та column_num(номер_стовпця) функції MATCH(ПОШУКПОЗ). Інакше результат формули буде хибним.
Стоп, стоп… чому ми не можемо легко використовувати функцію VLOOKUP(ВВР)? Чи є сенс витрачати час, намагаючись розібратися у лабіринтах ПОШУКПОЗі ІНДЕКС?
VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)
В даному випадку – немає сенсу! Ціль цього прикладу – виключно демонстраційна, щоб Ви могли зрозуміти, як функції ПОШУКПОЗі ІНДЕКСпрацюють у парі. Наступні приклади покажуть Вам справжню силу зв'язки ІНДЕКСі ПОШУКПОЗ, яка легко справляється з багатьма складними ситуаціями, коли ВВРвиявляється у глухому куті.
Чому ІНДЕКС/ПОШУКПОЗ краще, ніж ВПР?
Вирішуючи, яку формулу використовувати для вертикального пошуку, більшість гуру Excel вважають, що ІНДЕКС/ПОШУКПОЗнабагато краще, ніж ВВР. Однак, багато користувачів Excel, як і раніше, вдаються до використання ВВР, т.к. ця функція набагато простіше. Так відбувається, тому що дуже мало людей до кінця розуміє всі переваги переходу з ВВРна зв'язку ІНДЕКСі ПОШУКПОЗ, А витрачати час на вивчення складнішої формули ніхто не хоче.
4 головні переваги використання ПОШУКПОЗ/ІНДЕКС в Excel:
1. Пошук праворуч наліво.Як відомо будь-якому грамотному користувачеві Excel, ВВРне може дивитися вліво, а це означає, що потрібне значення має обов'язково перебувати в крайньому лівому стовпці досліджуваного діапазону. У випадку з ПОШУКПОЗ/ІНДЕКС, стовпець пошуку може бути, як у лівій, так і у правій частині діапазону пошуку. Приклад: покаже цю можливість у дії.
2. Безпечне додавання чи видалення стовпців.Формули з функцією ВВРперестають працювати або повертають помилкові значення, якщо видалити чи додати стовпець до таблиці пошуку. Для функції ВВРбудь-який вставлений чи видалений стовпець змінить результат формули, оскільки синтаксис ВВРвимагає вказувати весь діапазон та конкретний номер стовпця, з якого потрібно витягти дані.
Наприклад, якщо у Вас є таблиця A1:C10, і потрібно витягти дані зі стовпця B, то потрібно задати значення 2 для аргументу col_index_num(номер_стовпця) функції ВВР, ось так:
VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)
Якщо пізніше Ви вставите новий стовпець між стовпцями Aі B, то значення аргументу доведеться змінити з 2 на 3 , інакше формула поверне результат із щойно вставленого стовпця.
Використовуючи ПОШУКПОЗ/ІНДЕКС, Ви можете видаляти або додавати стовпці до досліджуваного діапазону, не спотворюючи результат, оскільки визначено безпосередньо стовпець, що містить потрібне значення. Справді, це велика перевага, особливо коли доводиться працювати з великими обсягами даних. Ви можете додавати та видаляти стовпці, не турбуючись про те, що потрібно буде виправляти кожну функцію, що використовується. ВВР.
3. Немає обмеження на розмір шуканого значення.Використовуючи ВВР, пам'ятайте про обмеження на довжину шуканого значення 255 символів, інакше ризикуєте отримати помилку #VALUE!(#ЗНАЧ!). Отже, якщо таблиця містить довгі рядки, єдине рішення, що діє, - це використовувати ІНДЕКС/ПОШУКПОЗ.
Припустимо, Ви використовуєте ось таку формулу з ВВР, яка шукає в осередках від B5до D10значення, вказане в комірці A2:
VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;БРЕХНЯ)
Формула не буде працювати, якщо значення в осередку A2довше за 255 символів. Замість неї Вам потрібно використати аналогічну формулу ІНДЕКС/ПОШУКПОЗ:
INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
= ІНДЕКС (D5: D10; ПОШУКПОЗ (ІСТИНА; ІНДЕКС (B5: B10 = A2; 0); 0))
4. Вища швидкість роботи.Якщо Ви працюєте з невеликими таблицями, то різниця у швидкодії Excel буде, швидше за все, непомітна, особливо в останніх версіях. Якщо ж Ви працюєте з великими таблицями, які містять тисячі рядків та сотні формул пошуку, Excel працюватиме значно швидше, при використанні ПОШУКПОЗі ІНДЕКСзамість ВВР. В цілому, така заміна збільшує швидкість роботи Excel на 13% .
Вплив ВВРна продуктивність Excel особливо помітно, якщо робоча книга містить сотні складних формул масиву, таких як ВПР+СУМ. Справа в тому, що перевірка кожного значення в масиві потребує окремого виклику функції ВВР. Тому чим більше значень містить масив і чим більше формул масиву містить Ваша таблиця, тим повільніше працює Excel.
З іншого боку, формула з функціями ПОШУКПОЗі ІНДЕКСпросто здійснює пошук та повертає результат, виконуючи аналогічну роботу помітно швидше.
ІНДЕКС та ПОШУКПОЗ – приклади формул
Тепер, коли Ви розумієте причини, через які варто вивчати функції ПОШУКПОЗі ІНДЕКСдавайте перейдемо до найцікавішого і побачимо, як можна застосувати теоретичні знання на практиці.
Як виконати пошук з лівого боку, використовуючи ПОШУКПОЗ та ІНДЕКС
Будь-який підручник з ВВРстверджує, що ця функція не може дивитися вліво. Тобто. якщо стовпець, що переглядається, не є крайнім лівим в діапазоні пошуку, то немає шансів отримати від ВВРбажаний результат.
Функції ПОШУКПОЗі ІНДЕКСв Excel набагато більш гнучкі, і їм все одно, де знаходиться стовпець зі значенням, яке потрібно витягти. Наприклад, знову повернемося до таблиці зі столицями країн і населенням. На цей раз запишемо формулу ПОШУКПОЗ/ІНДЕКС, яка покаже, яке місце населення займає столиця Росії (Москва).
Як видно на малюнку нижче, формула чудово справляється з цим завданням:
INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))
Тепер у Вас не повинно виникати проблем із розумінням, як працює ця формула:
- По-перше, задіємо функцію MATCH(ПОШКОЗ), яка знаходить положення “Russia” у списку:
MATCH("Russia",$B$2:$B$10,0))
=ПОШУКПОЗ("Russia";$B$2:$B$10;0)) - Далі, задаємо діапазон для функції INDEX(ІНДЕКС), з якого потрібно отримати значення. У нашому випадку це A2:A10.
- Потім з'єднуємо обидві частини та отримуємо формулу:
INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
=ІНДЕКС($A$2:$A$10;ПОШУКПОЗ("Russia";$B$2:$B$10;0))
Підказка:Правильним рішенням завжди буде використовувати абсолютні посилання для ІНДЕКСі ПОШУКПОЗ, щоб діапазони пошуку не збилися при копіюванні формули до інших осередків.
Обчислення за допомогою ІНДЕКС та ПОШУКПОЗ в Excel (СРЗНАЧ, МАКС, МІН)
Ви можете вкладати інші функції Excel у ІНДЕКСі ПОШУКПОЗнаприклад, щоб знайти мінімальне, максимальне або найближче до середнього значення. Ось кілька варіантів формул, стосовно таблиці з :
1. MAX(МАКС). Формула знаходить максимум у стовпці D Cтого ж рядка:
INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ІНДЕКС($C$2:$C$10;ПОШУКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))
Результат: Beijing
2. MIN(МІН). Формула знаходить мінімум у стовпці Dі повертає значення зі стовпця Cтого ж рядка:
INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ІНДЕКС($C$2:$C$10;ПОШУКПОЗ(МІН($D$2:I$10);$D$2:D$10;0))
Результат: Lima
3. AVERAGE(СРЗНАЧ). Формула обчислює середнє в діапазоні D2:D10потім знаходить найближче до нього і повертає значення зі стовпця Cтого ж рядка:
INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ІНДЕКС($C$2:$C$10;ПОШУКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))
Результат: Moscow
Про що потрібно пам'ятати, використовуючи функцію СРЗНАЧ разом з ІНДЕКС та ПОШУКПОЗ
Використовуючи функцію Відміннику комбінації з ІНДЕКСі ПОШУКПОЗ, як третій аргумент функції ПОШУКПОЗнайчастіше потрібно буде вказувати 1 або -1 у випадку, якщо Ви не впевнені, що діапазон, що переглядається, містить значення, що дорівнює середньому. Якщо Ви впевнені, що таке значення є, – ставте 0 для пошуку точного збігу.
- Якщо вказуєте 1 , Значення в стовпці пошуку повинні бути впорядковані за зростанням, а формула поверне максимальне значення, менше або дорівнює середньому.
- Якщо вказуєте -1 , значення в стовпці пошуку повинні бути впорядковані за спаданням, а повернуто буде мінімальне значення, більше або дорівнює середньому.
У нашому прикладі значення у стовпці Dупорядковані за зростанням, тому ми використовуємо тип зіставлення 1 . Формула ІНДЕКС/ПОШУК ПОЗповертає “Moscow”, оскільки величина населення міста Москва – найближче менше середнього значення (12 269 006).
Як за допомогою ІНДЕКС та ПОШУКПОЗ виконувати пошук за відомим рядком та стовпцем
Ця формула еквівалентна двовимірному пошуку ВВРі дозволяє знайти значення на перетині певного рядка та стовпця.
У цьому прикладі формула ІНДЕКС/ПОШУКПОЗбуде дуже схожа на формули, які ми вже обговорювали в цьому уроці, з однією лише відмінністю. Вгадайте яким?
Як Ви пам'ятаєте, синтаксис функції INDEX(ІНДЕКС) дозволяє використовувати три аргументи:
INDEX(array,row_num,)
ІНДЕКС (масив; номер_рядка; [номер_стовпця])
І я вітаю тих із Вас, хто здогадався!
Почнемо із того, що запишемо шаблон формули. Для цього візьмемо вже знайому нам формулу ІНДЕКС/ПОШУКПОЗі додамо до неї ще одну функцію ПОШУКПОЗ, яка повертатиме номер стовпця.
INDEX(Ваша таблиця ,(MATCH(, стовпець, в якому шукати,0)),(MATCH(, рядок у якому шукати,0))
= ІНДЕКС (Ваша таблиця, (MATCH ( значення для вертикального пошуку,стовпець, в якому шукати,0)),(MATCH( значення для горизонтального пошуку,рядок у якому шукати,0))
Зауважте, що для двовимірного пошуку потрібно вказати всю таблицю в аргументі array(масив) функції INDEX(Індекс).
А тепер давайте випробуваємо цей шаблон на практиці. Нижче Ви бачите список найнаселеніших країн світу. Припустимо, наше завдання дізнатися про населення США в 2015 році.
Добре, запишемо формулу. Коли мені потрібно створити складну формулу Excel з вкладеними функціями, то я спочатку кожну вкладену записую окремо.
Отже, почнемо з двох функцій ПОШУКПОЗ, які повертатимуть номери рядка та стовпця для функції ІНДЕКС:
- ПОШУКПОЗ для стовпця– ми шукаємо у стовпці B, а точніше в діапазоні B2:B11, значення, яке вказано в комірці H2(USA). Функція виглядатиме так:
MATCH($H$2,$B$1:$B$11,0)
=ПОШУКПОЗ($H$2;$B$1:$B$11;0)4 , оскільки “USA” – це 4-ий елемент списку у стовпці B(Включаючи заголовок).
- ПОШУКПОЗ для рядка– ми шукаємо значення осередку H3(2015) у рядку 1
, тобто в осередках A1:E1:
MATCH($H$3,$A$1:$E$1,0)
=ПОШУКПОЗ($H$3;$A$1:$E$1;0)Результатом цієї формули буде 5 , оскільки “2015” знаходиться у 5-му стовпці.
Тепер вставляємо ці формули у функцію ІНДЕКСта вуаля:
INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ІНДЕКС($A$1:$E$11;ПОШУКПОЗ($H$2;$B$1:$B$11;0);ПОШУКПОЗ($H$3;$A$1:$E$1;0))
Якщо замінити функції ПОШУКПОЗна значення, які вони повертають, формула стане легкою та зрозумілою:
INDEX($A$1:$E$11,4,5))
=ІНДЕКС($A$1:$E$11;4;5))
Ця формула повертає значення на перетині 4-ийрядки та 5-гостовпця в діапазоні A1:E11, тобто значення осередку E4. Просто? Так!
Пошук за кількома критеріями з ІНДЕКС та ПОШУКПОЗ
У підручнику з ВВРми показували приклад формули з функцією ВВРдля пошуку за кількома критеріями. Проте істотним обмеженням такого рішення була необхідність додавати допоміжний стовпець. Хороша новина: формула ІНДЕКС/ПОШУКПОЗможе шукати значення у двох стовпцях, без необхідності створення допоміжного стовпця!
Припустимо, ми маємо список замовлень, і ми хочемо знайти суму за двома критеріями – ім'я покупця(Customer) та продукт(Product). Справа ускладнюється тим, що один покупець може купити відразу кілька різних продуктів, і імена покупців у таблиці на аркуші Lookup tableрозташовані у довільному порядку.
Ось така формула ІНДЕКС/ПОШУКПОЗвирішує завдання:
(=INDEX("Lookup table"!$A$2:$C$13,MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13),0),3))
(=ІНДЕКС("Lookup table"!$A$2:$C$13;ПОШУКПОЗ(1;(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13);0);3))
Ця формула складніша за інші, які ми обговорювали раніше, але озброєні знанням функцій ІНДЕКСі ПОШУКПОЗВи переможете її. Найскладніша частина – це функція ПОШУКПОЗ, гадаю, її треба пояснити першою.
MATCH(1,(A2="Lookup table"!$A$2:$A$13),0)*(B2="Lookup table"!$B$2:$B$13)
ПОШУКПОЗ(1;(A2="Lookup table"!$A$2:$A$13);0)*(B2="Lookup table"!$B$2:$B$13)
У формулі, показаній вище, потрібне значення – це 1 , А масив пошуку – це результат множення. Добре, що ми повинні перемножити і чому? Давайте розберемо все по порядку:
- Беремо перше значення у стовпці A(Customer) на аркуші Main tableі порівнюємо його з усіма іменами покупців у таблиці на аркуші Lookup table(A2: A13).
- Якщо збіг знайдено, рівняння повертає 1 (ІСТИНА), а якщо ні – 0 (БРЕХНЯ).
- Далі, ми робимо те саме для значень стовпця B(Product).
- Потім перемножуємо отримані результати (1 та 0). Тільки якщо збіги знайдені в обох стовпцях (тобто обидва критерії є істинними), Ви отримаєте 1 . Якщо обидва критерії помилкові, або виконується лише один із них – Ви отримаєте 0 .
Тепер розумієте, чому ми поставили 1 як шукане значення? Правильно, щоб функція ПОШУКПОЗповертала позицію лише, коли обидва критерії виконуються.
Зверніть увагу:У цьому випадку необхідно використати третій необов'язковий аргумент функції ІНДЕКС. Він потрібний, т.к. у першому аргументі ми задаємо всю таблицю і маємо вказати функції, з якого стовпця потрібно отримати значення. У нашому випадку це стовпець C(Sum), і тому ми запровадили 3 .
І, зрештою, т.к. нам потрібно перевірити кожну комірку в масиві, ця формула має бути формулою масиву. Ви можете бачити це за фігурними дужками, в які вона поміщена. Тому, коли закінчите вводити формулу, не забудьте натиснути Ctrl+Shift+Enter.
Якщо все зроблено правильно, Ви отримаєте результат як на малюнку нижче:
ІНДЕКС і ПОШУКПОЗ у поєднанні з ЕСЛИПОМИЛКА в Excel
Як Ви, ймовірно, вже помітили (і не раз), якщо вводити некоректне значення, наприклад, якого немає в масиві, що проглядається, формула ІНДЕКС/ПОШУКПОЗповідомляє про помилку #N/A(#Н/Д) або #VALUE!(#ЗНАЧ!). Якщо Ви хочете замінити таке повідомлення на щось зрозуміліше, то можете вставити формулу з ІНДЕКСі ПОШУКПОЗу функцію ЯСЛИПОМИЛКА.
Синтаксис функції ЯСЛИПОМИЛКАдуже простий:
IFERROR(value,value_if_error)
ЯСЛИПОМИЛКА (значення; значення_якщо_помилка)
Де аргумент value(значення) – це значення, що перевіряється щодо наявності помилки (у разі – результат формули ІНДЕКС/ПОШУКПОЗ); а аргумент value_if_error(значення_якщо_помилка) - це значення, яке потрібно повернути, якщо формула видасть помилку.
Наприклад, можна вставити в функцію ЯСЛИПОМИЛКАось таким чином:
IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Збігів не знайдено. Спробуйте ще раз!") =ЯКЩОПОМИЛКА(ІНДЕКС($A$1:$E$11;ПОШУКПОЗ($G$2;$B$1:$B$11;0);ПОШУКПОЗ($G$3;$A$1 :$E$1;0));
"Збігів не знайдено. Спробуйте ще раз!")
І тепер, якщо хтось введе помилкове значення, формула видасть такий результат:
Якщо Ви віддаєте перевагу в разі помилки залишити комірку порожньою, то можете використовувати лапки (“”), як значення другого аргументу функції ЯСЛИПОМИЛКА. Ось так:
IFERROR(INDEX(масив,MATCH(шукане_значення,проглядається_масив,0),"")
ЕСЛИПОМИЛКА (ІНДЕКС (масив; ПОШУКПОЗ (пошукова_значення; переглядається_масив; 0); "")
Сподіваюся, що хоча б одна формула, описана в цьому підручнику, видалася Вам корисною. Якщо Ви стикалися з іншими завданнями пошуку, для яких не змогли знайти відповідне рішення серед інформації в цьому уроці, сміливо опишіть свою проблему в коментарях, і ми разом намагатимемося вирішити її.
За функціональним програмуванням, що проводиться під егідою Фонду Підтримки Функціонального Програмування ФП(ФП). Традиційно я хотів би підбити підсумки конкурсу та розповісти про рішення конкурсного завдання за допомогою мови програмування Haskell. Отже, всіх зацікавлених я запрошую ознайомитися з цією невеликою заміткою.
Як завдання на конкурс було запропоновано завдання щодо пошуку закономірностей у ряді проявів здавалося б «випадкової» події. Але як і все в цьому світі суто випадковими є, мабуть, результати виміру квантових станів, так що в усьому іншому можна знайти якісь закономірності. Так і тут. Було дано список дат, коли сталася певна подія, і пропонувалося дати відповіді на два питання:
- Яким є мінімальний період, у якому частотна ймовірність прояву події хоча б в один день періоду дорівнює або більше 50 %?
- Необхідно було дати прогноз вияву події з дати конкурсу до кінця поточного року.
Лише два конкурсанти змогли надати рішення. Втім, обидва вони були неправильними, оскільки правильною відповіддю на перше запитання є число 24. А ось друге завдання буде опрацьовано наприкінці року, коли буде виявлено статистику щодо проявів подій. Отже приз за перше запитання залишився нерозіграним, а приз за друге питання буде надано конкурсанту, прогноз якого набере більше очок, наступного року.
Ну а тут залишається розглянути вирішення цих завдань мовою програмування Haskell.
Пошук періоду
Насамперед було визначено список проявів події:
Dates:: dates = ["27.09.2013", "06.10.2013", "23.10.2013", "06.11.2013", "26.11.2013", "27.11.2013", "21.12.2013" .2013", "06.01.2014", "16.01.2014", "17.01.2014", "21.01.2014", "25.01.2014", "26.01.2014", "05.02.2014", "05.02.2014" "," 21.02.2014", "02.03.2014", "07.03.2014", "30.03.2014", "08.04.2014", "18.04.2014", "23.04.2014", "27.04. "02.05.2014", "15.05.2014", "17.05.2014", "18.05.2014", "19.05.2014", "20.05.2014", "25.05.2014", "26.0. .2014"]
Навіть побіжний погляд покаже, що в цьому списку немає явних закономірностей. Але медитування над ним кілька днів поспіль може виявити щось. Щоб не вдаватися до безглуздої витрати часу на медитації, якраз і вирішено було написати програму для пошуку закономірностей у періодах різної довжини. Почнемо писати цю програму, як це зазвичай буває у розробці функціональними мовами програмування, зверху вниз. Ось головна функція програми:
Main:: IO () main = do putStrLn ("У послідовності дат виявлено " ++ "закономірності в мінімальному періоді довжиною " ++ show (length findMinimalPeriod) ++ " днів.") revealSequences findMinimalPeriod
Тут виклик двох функцій, які ми розглянемо далі. Перша, findMinimalPeriod , повертає період мінімальної довжини заданого порога ймовірності. Уважний читач зауважить, що їй не передається жодних аргументів, тому поріг ймовірності визначений у вигляді константи. Загалом це порочна практика для парадигми функціонального програмування, але іноді в дослідницьких цілях це допомагає швидко досягти результатів. Так що ось визначення:
Significance:: Int significance = 5 lowProbability:: Float lowProbability = 0.5 findMinimalPeriod:: [(Int, Float)] findMinimalPeriod = head $ filter (l -> maximum (map snd l) >= lowProbability) $ map process
Константа significance визначає мінімальну «висоти» циліндра, на який намотується шкала часу (адже для того, щоб знайти періоди, можна відзначити дати прояву події на довгій стрічці, яку потім намотати спіраллю на циліндр із заданим довжиною кола, яке визначає період; відповідно закономірності будуть виглядати як вертикальні лінії). Ну а константа lowProbability ставить мінімальний поріг ймовірності прояву події. Сама ж функція findMinimalPeriod бере голову списку, отриманого після фільтрації списку на наявність ймовірності не менш заданого порогу, який (список) отримано за допомогою обробки (функція process) чисел від 1 до деякої верхньої межі.
Верхня межа визначається за допомогою функції interval, визначення якої таке:
Interval :: Int interval = finish - start where start = stringToDayOfYear $ head dates finish = 365 + stringToDayOfYear (last dates)
Як бачимо, тут ми вважаємо довжину інтервалу, у якому задані дати проявів події. Віднімаємо з останньої дати першу (можливо, що треба було б додати 1). Ця функція не дуже хороша, оскільки в ній є число 365, а це означає, що вона не є універсальною. Ну та гаразд. До того ж, попередня функція (findMinimalPeriod) взагалі написана дуже погано і може викинути помилку часу виконання через відсутність перевірки на порожнечу списку, що передається в функцію head .
Тепер перейдемо до визначення функції:
Process:: Fractional a => Int -> [(Int, a)] process p = map (l -> (fst $head l, ((/) `on` fromInteger) (sum $ map snd l) (toEnum $ length l))) $ groupBy ((==) `on` fst) $ sortBy (comparing fst) $ map (first (`mod` p) . i -> if i `elem` ds3 then (i, 1) else (i, 0)) where ds1 = map stringToDayOfYear dates ds2 = uncurry (++) $ second (map (+ 365)) $ span (>= head ds1) ds1 ds3 = map (subtract (head ds2)) ds2
Функція отримує на вхід довжину періоду, а повертає список пар (гістограму), у яких першим елементом є номер дня у періоді, а другим – частотна ймовірність прояву події цього дня. За допомогою локальних визначень ds1, ds2 та ds3 будується список послідовних номерів днів прояву події, починаючи з першого дня списку dates . Далі цей перелік піддається такій процедурі. Для всіх номерів від 1 до номера останньої дати вияву події шукається залишок від розподілу на довжину періоду. Для всіх таких залишків ставиться прапор 0 якщо у відповідний день прояви події не було, і прапор 1 - якщо було. Потім список залишків з прапорами групується по залишках, після чого групи схлопываются у пари виду (номер дня періоді, ймовірність прояви події). Всі.
Тут слід розглянути ще дві сервісні функції:
Double:: a -> (a, a) double x = (x, x) stringToDayOfYear:: String -> Int stringToDayOfYear = uncurry (monthAndDayToDayOfYear False) . (read. Take 2. Drop 3 *** read. Take 2). double
Про першу і говорити нічого (дивно тільки те, що її визначення немає в стандартному модулі Prelude; хоча це і зрозуміло, оскільки воно настільки тривіальне). Друга функція переводить дату з рядкового подання "DD.MM.YYYY" в числове, прийняте в модулі Data.Time.Calendar.MonthDay , з якого обробляються дати.
Нарешті, визначимо функцію revealsequences:
RevealSequences:: [(Int, Float)] -> IO () revealSequences ps = do let l = length ps (d1, p1) = maximumBy (comparing snd) ps (d2, p2) = maximumBy (comparing snd) $ delete ( d1, p1) ps putStrLn ("Максимальний прояв події (ймовірність: "++ show p1 ++") відбувається на "++ show (d1 + 1) ++ "-й день" ++ show l ++ "-денного періоду.") putStrLn ("Другий максимум (ймовірність: "++ show p2 ++ ") відбувається на "++ show (d2 + 1) ++ "-й день.")
Як видно, вона просто знаходить і виводить на екран два піки вияву події у заданому списку (гістограмі). Ця функція реалізована виключно для зручності дослідження і вже не має відношення до вирішення поставленого у конкурсі завдання.
Прогнозування
Тепер перейдемо до другого питання конкурсу – прогнозування дат вияву події до кінця року. Слід зазначити, що загалом завдання прогнозування є справою невдячною. Одне питання, коли ми маємо чітку закономірність (хоча б навіть і імовірнісну), і можна більш-менш застосувати детерміновану формулу. Зовсім інша річ, коли є деяка (зовсім невелика) кількість значень, на підставі яких треба збудувати можливе майбутнє. Точок біфуркацій міріади, тож навіть допуски та інтервальна арифметика не допоможуть. Тим не менш, можна придумати якийсь метод і спробувати оцінити його застосування вже заднім розумом, коли пройде певний період часу, і ми зможемо порівняти наванговані значення з фактами, що фактично відбулися.
Вчинимо тут саме таким чином. Як метод можна запропонувати такий. Розглянемо всі можливі періоди, починаючи від мінімального знайденого на попередньому етапі (менші від нього, чесно кажучи, не викликають довіри з погляду статистичної правдоподібності) і закінчуючи довжиною відомого періоду спостережень, поділеної на норму допуску («товщину», як ми її назвали раніше) . Для кожного з таких періодів отримаємо ймовірність прояву подій у день періоду, як ми вже навчилися робити це за допомогою функції process . Далі просто знайдемо середню ймовірність для кожного дня за всіма ймовірностями, знайденими для кожного періоду, що розглядається.
Саме цей метод реалізує таку функцію:
Forecast:: FilePath -> String -> String -> IO () forecast fp sd fd = do let (b, e) = (length findMinimalPeriod, interval `div` significance) writeFile fp $ unlines $ map (((n, q ) -> let (m, d) = dayOfYearToMonthAndDay False (n - 365) in prettyShowInt d ++ "." ++ prettyShowInt m ++ ".2014: "++ prettyShowFloat q) . + 1))) $ foldr1 (zipWith ((d, q1) (_, q2) -> (d, q1 + q2))) $ map getProbabilities where getProbabilities p = let ds = stringToDayOfYear $ head dates fs = 365 + stringToDay (last dates) d1 = 365 + stringToDayOfYear sd d2 = 365 + stringToDayOfYear fd in drop (interval + (d1 - fs)) $ zipWith (x (_, q) -> (x, q)) $ cycle $ process p leadingZero :: String -> String leadingZero [c] = "0" : [c] leadingZero c = c prettyShowInt i = leadingZero $ show i prettyShow f * 100) in leadingZero d ++ таке 5 (r ++ cycle "0")
Її визначення виглядає дещо монструозним, але ядром обчислень є локальне визначення getProbabilities (з назви має бути зрозуміло, якому кроку методу воно відповідає). Решта - лише обв'язка для виведення отриманих значень у файл у заданому умовами конкурсу форматі.
Загалом, все. Тепер залишилося лише дочекатися кінця року та порівняти прогноз із фактом.
Розглянемо одну з корисних опцій, яку пропонує програма Microsoft Excel. До речі, ліцензійну версію цієї програми ви можете придбати у нашому інтернет-магазині зі знижкою. Ціни та версії можна подивитися.
Сьогодні йтиметься про «Умовне форматування». Воно призначене виділення осередків таблиці, мають спільні риси. Це може бути ідентичний шрифт, значення, тло і т.д. Ця операція передбачає різні зміни: строгість перевірки, зміст збігів, їх тотожність і варіативність.
- Почнемо із запуску програми Microsoft Excel, що містить потрібну нам таблицю. Слідом виділяємо діапазон осередків, які потребують обробки. Мова йде про спільність стовпців і осередків, що формують частину таблиці, або кілька незв'язаних між собою областей таблиці.
- Далі нам потрібно пройти наступний шлях:
- Програма має широку лінійку можливостей, зокрема: можна вибрати підсвічування осередків, що потрапили у відбір, передбачений варіант вибору фону заливки (програма надає 6 колірних рішень), варіації шрифтів та табличних рамок. Можливий вибір «КОРИСТУВАЛЬНОГО ФОРМАТУ», який дозволяє створити свій варіант оформлення осередків. Щоб скасувати вибірку комірок, натисніть OK.
Використання функції «РІВНО»
Якщо потрібні для виділення комірки мають абсолютно конкретне значення, скористайтесь пунктом «РІВНЕ» у списку «УМОВНЕ ФОРМАТУВАННЯ», що знаходиться в розділі «ПРАВИЛА ВИДІЛЕННЯ осередків». У діалоговому вікні відзначте комірки, що вас цікавлять, вимагають виявлення дублікатів, при цьому їх адреса з'явиться в сусідньому діалоговому вікні. Опанувавши ці нехитрі навички, ви зможете значно скоротити час на обробку табличних даних та угруповання загальних значень.
Відео: Пошук збігів у Excel