Функції індекс та пошукпоз у excel – найкраща альтернатива для впр. Функції індекс і пошукпоз в excel – найкраща альтернатива Програма для пошуку закономірності в коді

Допустимо ваш звіт містить таблицю з великою кількістю даних на множину стовпців. Проводити візуальний аналіз таких таблиць дуже складно. А одним із завдань по роботі зі звітом є аналіз даних щодо заголовків рядків і стовпців, що стосуються певного місяця. На перший погляд, це дуже просте завдання, але його не можна вирішити, використовуючи одну стандартну функцію. Так, звичайно, можна скористатися інструментом: «ГОЛОВНА»-«Редагування»-«Знайти» CTRL+F, щоб викликати вікно пошуку значень на аркуші Excel. Або створити для таблиці правило умовного форматування. Але тоді не можна буде виконати подальші обчислення з отриманими результатами. Тому необхідно створити та правильно застосувати відповідну формулу.

Пошук значення у масиві Excel

Схема вирішення завдання виглядає приблизно так:

• в осередок B1 ми будемо вводити дані, що цікавлять нас;
• у комірці B2 буде відображено заголовок стовпця, який містить значення комірки B1
• у комірці B3 буде відображатися назва рядка, що містить значення комірки B1.

Фактично необхідно виконати пошук координат Excel. Для чого це потрібно? Досить часто нам потрібно отримати координати таблиці за значенням. Небагато нагадує зворотний аналіз матриці. Конкретний приклад двома словами виглядає приблизно так. Поставлена ​​мета в цифрах є вихідним значенням, потрібно визначити, хто і коли найбільш наближений до цієї мети. Наприклад використовуємо просту матрицю даних зі звітом за кількістю проданих товарів за три квартали, як показано нижче на малюнку. Важливо, щоб усі числові показники збігалися. Якщо немає бажання вручну створювати та заповнювати таблицю Excel із чистого аркуша, то наприкінці статті можна завантажити вже з готовим прикладом.

Послідовно розглянемо варіанти розв'язання різної складності, а наприкінці статті – фінальний результат.

Пошук значення у стовпці Excel

Спочатку навчимося отримувати заголовки стовпців таблиці за значенням. Для цього виконайте такі дії:

1. У комірку B1 введіть значення взяте з таблиці 5277 і виділіть її фон синім кольором для читабельності поля введення (далі вводимо в комірку B1 інші числа, щоб експериментувати з новими значеннями).
2. У комірку C2 вводимо формулу для отримання заголовка стовпця таблиці, який містить це значення:
3. Після введення формули для підтвердження натискаємо комбінацію гарячих клавіш 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.

За функціональним програмуванням, що проводиться під егідою Фонду Підтримки Функціонального Програмування ФП(ФП). Традиційно я хотів би підбити підсумки конкурсу та розповісти про вирішення конкурсного завдання за допомогою мови програмування Haskell. Отже, всіх зацікавлених я запрошую ознайомитися з цією невеликою заміткою.

Як завдання на конкурс було запропоновано задачу пошуку закономірностей у ряді проявів здавалося б «випадкової» події. Але як і все в цьому світі суто випадковими є, мабуть, результати виміру квантових станів, так що в усьому іншому можна знайти якісь закономірності. Так і тут. Було дано список дат, коли сталася певна подія, і пропонувалося дати відповіді на два питання:

1. Яким є мінімальний період, у якому частотна ймовірність прояву події хоча б в один день періоду дорівнює або більше 50 %?
2. Необхідно було дати прогноз вияву події з дати конкурсу до кінця поточного року.

Лише два конкурсанти змогли надати рішення. Втім, обидва вони були неправильними, оскільки правильною відповіддю на перше запитання є число 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. "02.05.2014", "15.05.2014", "17.05.2014", "18.05.2014", "19.05.2014", "20.05.2014", "25.05.2014", "26.0.2. .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 datas 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 (з назви має бути зрозуміло, якому кроку методу вона відповідає). Решта - лише обв'язка для виведення отриманих значень у файл у заданому умовами конкурсу форматі.

Загалом, все. Тепер залишилося лише дочекатися кінця року та порівняти прогноз із фактом.

Для боротьби з комбінаторним вибухом потрібен «комбінаторний брухт». Є два інструменти, що дозволяють практично вирішувати складні комбінаторні завдання. Перший – це масове розпаралелювання обчислень. І тут важливо не тільки мати велику кількість паралельних процесорів, але й підібрати такий алгоритм, який дозволяє розпаралелити завдання та завантажити всі доступні обчислювальні потужності.

Другий інструмент – це принцип обмеженості. Основний метод, який використовує принцип обмеженості - це метод «випадкових підпросторів». Іноді комбінаторні завдання допускають сильне обмеження вихідних умов і при цьому зберігають надію, що після цих обмежень в даних збережеться достатньо інформації, щоб можна було знайти необхідне рішення. Варіантів, як можна обмежити вихідні умови, може бути багато. Не всі можуть бути вдалими. Але якщо, все ж таки, ймовірність, що вдалі варіанти обмежень є, тоді складна задача може бути розбита на велику кількість обмежених завдань, кожна з яких вирішується значно простіше вихідної.

Комбінуючи ці два принципи, можна побудувати рішення нашого завдання.

Комбінаторний простір

У чому сенс цього простору? Ми припускаємо, що вхідний сигнал невипадковий, а містить певні закономірності. Закономірності може бути двох основних типів. Щось у вхідному описі може з'являтися дещо частіше, ніж інше. Наприклад, у разі окремі літери з'являються частіше ніж їх поєднання. При бітовому кодуванні це означає, що певні комбінації бітів зустрічаються частіше, ніж інші.

Інший тип закономірностей - це коли крім вхідного сигналу є супутній йому сигнал навчання і щось, що міститься у вхідному сигналі, виявляється пов'язане з чимось, що міститься у сигналі навчання. У разі активні вихідні біти – це реакція на комбінацію певних вхідних бітів.

Якщо шукати закономірності «в лоб», тобто, дивлячись на весь вхідний і весь вихідний вектори, то не дуже зрозуміло, що робити і куди рухатися. Якщо починати будувати гіпотези щодо того, що від чого може залежати, то моментально настає комбінаторний вибух. Кількість можливих гіпотез виявляється жахливо.

Класичний метод, який широко використовується в нейронних мережах, - градієнтний спуск. Для нього важливо зрозуміти, в який бік рухатись. Зазвичай це нескладно, коли вихідна ціль одна. Наприклад, якщо ми хочемо навчити нейронну мережу написання цифр, ми показуємо їй зображення цифр і вказуємо, що за цифру вона бачить. Мережі відомо «як і куди спускатися». Якщо ми показуватимемо картинки відразу з кількома цифрами і називатимемо всі ці цифри одночасно, не вказуючи, де що, то ситуація стає значно складнішою.

Коли створюються точки комбінаторного простору з дуже обмеженим «оглядом» (випадкові підпростори), то виявляється, що деяким точкам може повезти і вони побачать закономірність якщо не зовсім чистою, то принаймні значно очищеному вигляді. Такий обмежений погляд дозволить, наприклад, провести градієнтний спуск і набути вже чистої закономірності. Імовірність для окремої точки натрапити на закономірність може бути не дуже висока, але завжди можна підібрати таку кількість точок, щоб гарантувати собі, що будь-яка закономірність «десь спливе».

Звичайно, якщо робити розміри точок занадто вузькими, тобто кількість бітів у точках вибирати приблизно рівним тому, скільки бітів очікується в закономірності, то розміри комбінаторного простору почнуть прагнути кількості варіантів повного перебору можливих гіпотез, що повертає нас до комбінаторного вибуху. Але, на щастя, можна збільшувати огляд точок, знижуючи їхню загальну кількість. Це зниження не дається безкоштовно, комбінаторика "переноситься в точки", але до певного моменту це не смертельно.

Створимо вихідний вектор. Просто зведемо у кожен біт виходу кілька точок комбінаторного простору. Які це будуть точки, виберемо випадковим чином. Кількість точок, що потрапляють в один біт, буде відповідати тому, скільки разів ми хочемо зменшити комбінаторний простір. Такий вектор виходу буде хеш-функцією для стану комбінаторного простору. Про те, як цей стан вважається, ми поговоримо трохи згодом.

У випадку, наприклад, як показано малюнку вище, розмір входу і виходу може бути різні. У прикладі з перекодуванням рядків ці розміри збігаються.

Кластери рецепторів

Будемо називати запис номера одного біта рецептором до цього біта. Це передбачає, що рецептор стежить станом відповідного біта вхідного вектора і реагує, коли там з'являється одиниця.

Набір рецепторів називатимемо кластером рецепторів або рецептивним кластером. Коли вхідний вектор, рецептори кластера реагують, якщо у відповідних позиціях вектора стоять одиниці. Для кластера можна підрахувати кількість рецепторів, що спрацювали.

Оскільки інформація у нас кодується не окремими бітами, а кодом, то від того, скільки бітів ми візьмемо до кластера, залежить точність, з якою ми сформулюємо гіпотезу. До статті доданий текст програми, який вирішує завдання з перекодуванням рядків. За замовчуванням у програмі задані наступні установки:

• довжина вхідного вектора – 256 біт;
• довжина вихідного вектора – 256 біт;
• окрема буква кодується 8 бітами;
• довжина рядка – 5 символів;
• кількість контекстів зміщення – 10;
• розмір комбінаторного простору – 60 000;
• кількість бітів, що перетинаються у точці – 32;
• поріг створення кластеру – 6;
• поріг часткової активації кластера – 4.

При таких налаштуваннях практично кожен біт, який є в коді однієї літери, повторюється в коді іншої літери, а то й у кодах кількох літер. Тому одиночний рецептор неспроможна надійно зазначити закономірність. Два рецептори вказують на букву значно краще, але вони можуть вказувати на поєднання зовсім інших букв. Можна ввести якийсь поріг довжини, починаючи з якого можна достовірно судити про те, чи є в кластері потрібний нам фрагмент коду.

Введемо мінімальний поріг за кількістю рецепторів, необхідні формування гіпотези (у прикладі він дорівнює 6). Приступимо до навчання. Подаватимемо вихідний код і код, який ми хочемо отримати на виході. Для вихідного коду легко підрахувати, скільки активних бітів потрапляє у кожну точку комбінаторного простору. Виберемо тільки ті точки, які підключені до активних біт вихідного коду і в яких кількість активних бітів вхідного коду, що потрапили в неї, виявиться не менше порога створення кластера. У таких точках створимо кластери рецепторів із відповідними наборами бітів. Збережемо ці кластери саме у тих точках, де вони були створені. Щоб не створювати дублікати, попередньо перевіримо, що ці кластери унікальні для цих точок і точки ще не містять таких же кластерів.

Скажімо те саме іншими словами. По вихідному вектору ми знаємо, які біти мають бути активними. Відповідно ми можемо вибрати пов'язані з ними точки комбінаторного простору. Для кожної такої точки ми можемо сформулювати гіпотезу про те, що те, що вона зараз бачить на вхідному векторі, це і є закономірність, яка відповідає за активність того біта, до якого ця точка підключена. Ми не можемо сказати за одним прикладом, чи вірна ця гіпотеза, але висунути припущення нам ніхто не заважає.

Навчання. Консолідація пам'яті

Можливі різні підходи до вирішення поставленого завдання. Опишу один із них, не стверджуючи, що він найкращий. Я перебрав безліч варіантів, цей підкупив мене якістю роботи та простотою, але це не означає, що його не можна покращити.

Зручно сприймати кластери як автономні обчислювачі. Якщо кожен кластер може перевіряти свою гіпотезу і приймати рішення незалежно від інших, це дуже добре для потенційного розпаралелювання обчислень. Кожен кластер рецепторів після створення починає самостійне життя. Він стежить за сигналами, що надходять, накопичує досвід, змінює себе і приймає якщо необхідно рішення про самоліквідацію.

Кластер - це набір бітів, про який ми припустили, що всередині нього сидить закономірність, пов'язана зі спрацьовуванням того вихідного біта, якого підключена точка, що містить цей кластер. Якщо закономірність є, то, швидше за все, вона зачіпає лише частину бітів, причому ми заздалегідь не знаємо, яку. Тому фіксуватимемо всі моменти, як у кластері спрацьовує значної кількості рецепторів (у прикладі щонайменше 4). Можливо, що в ці моменти закономірність, якщо вона є, виявляє себе. Коли накопичиться певна статистика, ми зможемо спробувати визначити, чи є в таких часткових спрацьовуваннях кластер щось закономірне чи ні.

Приклад статистики показаний нижче. Плюс на початку рядка показує, що в момент часткового спрацьовування кластера вихідний біт також був активним. Біти кластера сформовані з бітів вхідного вектора.

Що нас має цікавити у цій статистиці? Нам важливо, які біти найчастіше спрацьовують спільно. Не сплутайте це з найчастішими бітами. Якщо порахувати для кожного біта частоту його появи і взяти найпоширеніші біти, це буде усереднення, яке зовсім не те, що нам треба. Якщо точці зійшлися кілька стійких закономірностей, то за усередненні вийде середня з-поміж них «не закономірність». У нашому прикладі, видно, що 1,2 та 4 рядки схожі між собою, також схожі 3,4 та 6 рядки. Нам треба вибрати одну з цих закономірностей, бажано найсильнішу, та очистити її від зайвих бітів.

Найбільш поширена комбінація, яка проявляється як спільне спрацьовування певних бітів, є для цієї статистики. Щоб визначити головну компоненту, можна скористатися фільтром Хебба. Для цього можна задати вектор з поодинокою початковою вагою. Потім отримувати активність кластера, перемножуючи вектор ваги на поточний стан кластера. А потім зрушувати ваги у бік поточного стану тим сильніше, чим вища ця активність. Щоб ваги не росли безконтрольно, після зміни ваг треба нормувати, наприклад, на максимальне значення з вектора ваг.

Така процедура повторюється всім наявних прикладів. В результаті вектор терезів все більше наближається до головної компоненти. Якщо тих прикладів, що є, не вистачає, щоб зійтись, то можна кілька разів повторити процес на тих самих прикладах, поступово зменшуючи швидкість навчання.

Основна ідея в тому, що з наближенням до головної компоненти кластер починає все сильніше реагувати на зразки, схожі на неї і все менше на інші, за рахунок цього навчання в потрібну сторону йде швидше, ніж «погані» приклади намагаються його зіпсувати. Результат роботи такого алгоритму після кількох ітерацій показано нижче.

Якщо тепер підрізати кластер, тобто залишити ті рецептори, які мають високі ваги (наприклад, вище 0.75), ми отримаємо закономірність, очищену від зайвих шумових бітів. Цю процедуру можна повторити кілька разів у міру накопичення статистики. В результаті можна зрозуміти, чи є в кластері якась закономірність, чи ми зібрали разом випадковий набір бітів. Якщо закономірності немає, то результаті підрізання кластера залишиться занадто короткий фрагмент. У цьому випадку такий кластер можна видалити як гіпотезу, що не відбулася.

Крім підрізування кластера треба стежити за тим, щоб було спіймано саме потрібну закономірність. У вихідному рядку змішані коди кількох букв, кожен із них є закономірністю. Будь-який з цих кодів може бути "спійманий" кластером. Але нас цікавить код лише тієї літери, яка впливає формування вихідного біта. Тому більшість гіпотез будуть помилковими і їх необхідно відкинути. Це можна зробити за тими критеріями, що часткове або навіть повне спрацьовування кластера занадто часто не співпадатиме з активністю потрібного вихідного біта. Такі кластери підлягають видаленню. Процес такого контролю та видалення зайвих кластерів разом із їх «підрізуванням» можна назвати консолідацією пам'яті.

Процес накопичення нових кластерів досить швидкий, кожен новий досвід формує кілька тисяч нових кластерів-гіпотез. Навчання доцільно проводити етапами із перервою на «сон». Коли кластерів створюється критично багато, потрібно перейти в режим холостої роботи. У цьому режимі прокручується раніше запам'ятаний досвід. Але при цьому не створюється нових гіпотез, а лише триває перевірка старих. В результаті «сну» вдається видалити величезний відсоток хибних гіпотез і залишити лише гіпотези, що пройшли перевірку. Після «сну» комбінаторний простір як виявляється очищено готовий до прийому нової інформації, а й набагато впевненіше орієнтується у цьому, що було вивчено «вчора».

Вихід комбінаторного простору

Далі із цієї активності сформуємо вихід як хеш комбінаторного простору. При цьому враховуватимемо, що чим довше кластер, тим вищий шанс, що ми зловили закономірність. Для коротких кластерів є можливість, що поєднання бітів виникло випадково як комбінація інших понять. Для підвищення завадостійкості скористаємося ідеєю бустингу, тобто вимагатимемо, щоб для коротких кластерів активація вихідного біта відбувалася тільки коли таких спрацьовувань буде кілька. У разі довгих кластерів будемо вважати, що достатньо і одиничного спрацьовування. Це можна уявити через потенціал, що виникає при спрацьовуванні кластерів. Цей потенціал тим вищий, чим довший кластер. Потенціали точок, підключених до одного вихідного біта, складаються. Якщо підсумковий потенціал перевищує певний поріг, біт активується.

Після деякого навчання на виході починає відтворюватися частина, що збігається з тим, що хочемо отримати (малюнок нижче).

Поступово вихід комбінаторного простору починає краще відтворювати необхідний код виходу. Після кількох тисяч кроків навчання вихід відтворюється із досить високою точністю (малюнок нижче).

Щоб наочно уявити, як усе це працює, я записав відео з процесом навчання. Крім того, мої пояснення допоможуть краще розібратися у всій цій кухні.

Посилення правил

Неважко вигадати правила перевірки гальмівних гіпотез і запустити консолідацію гальмівних рецептивних кластерів.

Оскільки гальмівні кластери створюються у конкретних точках, всі вони впливають не так на блокування вихідного біта взагалі, але в блокування його спрацьовування від правил, виявлених у цій точці. Можна ускладнити архітектуру зв'язків та ввести гальмівні правила, загальні для групи точок або для всіх точок, підключених до вихідного біта. Схоже, що можна придумати ще багато чого цікавого, але поки що зупинимося на описаній простій моделі.

Випадковий ліс

Цей метод починається з ідеї «випадкових підпросторів». Якщо у вихідних даних занадто багато змінних і ці змінні слабкі, але корельовані, то на повному обсязі даних стає важко відокремити окремі закономірності. У такому випадку можна створити підпростори, в яких будуть обмежені як змінні, так і навчальні приклади. Тобто кожен підпростір міститиме лише частину вхідних даних, і ці дані будуть представлені не всіма змінними, а їх випадковим обмеженим набором. Для деяких із цих підпросторів шанси виявити закономірність, погано видиму на повному обсязі даних, значно підвищуються.

Потім у кожному підпросторі на обмеженому наборі змінних та навчальних прикладів проводиться навчання вирішального дерева. Вирішальне дерево – деревоподібна структура (малюнок нижче), у вузлах якої відбувається перевірка вхідних змінних (атрибутів). За результатами перевірки умов у вузлах визначається шлях від вершини до термінального вузла, який називають листом дерева. У листі дерева є результат, який може бути значенням будь-якої величини або номером класу.

Для вирішальних дерев існують різні алгоритми навчання, які дозволяють побудувати дерево з більш-менш оптимальними атрибутами у його вузлах.

На завершальному етапі застосовується ідея бустингу. Вирішальні дерева формують комітет для голосування. На підставі колективної думки створюється найправдоподібніша відповідь. Головна перевага бустингу – це можливість при об'єднанні безлічі «поганих» алгоритмів (результат яких лише трохи краще за випадковий) отримати як завгодно «хороший» підсумковий результат.

У нашому алгоритмі, що експлуатує комбінаторний простір і кластери рецепторів, використовуються самі фундаментальні ідеї, що й у методі випадкового лісу. Тому немає нічого дивного, що наш алгоритм працює та видає непоганий результат.

Біологія навчання

На мембрані нейрона розташовується багато різних рецепторів. Більшість цих рецепторів перебувають у «вільному плаванні». Мембрана створює для рецепторів середовище, в якому вони можуть вільно переміщатися, легко змінюючи своє положення на поверхні нейрона (Sheng, M., Nakagawa, T., 2002) (Tovar K. R., Westbrook G. L., 2002).

У класичному підході на причинах такої "свободи" рецепторів зазвичай акцент не робиться. Коли синапс посилює свою чутливість, це супроводжується переміщенням рецепторів із внесинаптичного простору до синаптичної щілини (Malenka R.C., Nicoll R.A., 1999). Цей факт негласно сприймається як виправдання рухливості рецепторів.

У нашій моделі можна припустити, що основна причина рухливості рецепторів – це необхідність «на льоту» формувати їх кластери. Тобто картина виглядає так. По мембрані вільно дрейфують різні рецептори, чутливі до різних нейромедіаторів. Інформаційний сигнал, що виник у мініколонці, викликає викид нейромедіаторів аксонними закінченнями нейронів та астроцитами. У кожному синапсі, де випускаються нейромедіатори, крім основного нейромедіатора є своя унікальна добавка, яка ідентифікує саме цей синапс. Нейромедіатори виплескуються з синаптичних щілин у навколишній простір, за рахунок чого в кожному місці дендриту (точках комбінаторного простору) виникає специфічний коктейль нейромедіаторів (інгредієнти коктейлю вказують на біти, що потрапляють у крапку). Ті вільно блукаючі рецептори, які знаходять у цей момент свій нейромедіатор у цьому коктейлі (рецептори конкретних бітів вхідного сигналу), переходять у новий стан – стан пошуку. У цьому стані у них є невеликий час (до того моменту, поки не настане наступний такт), за який вони можуть зустріти інші активні рецептори і створити загальний кластер (кластер рецепторів, чутливих до певного поєднання бітів).

Метаботропні рецептори, а йдеться про них мають досить складну форму (рисунок нижче). Вони складаються із семи трансмембранних доменів, які з'єднані петлями. Крім того, вони мають два вільні кінця. За рахунок різних за знаком електростатичних зарядів вільні кінці можуть через мембрану «залипати» один на одного. За рахунок таких сполук рецептори об'єднуються в кластери.

Після об'єднання починається спільне життя рецепторів у кластері. Можна припустити, що положення рецепторів щодо один одного може змінюватися в широких межах і кластер може набувати химерних форм. Якщо припустити, що рецептори, спрацьовують спільно, прагнутимуть зайняти місце ближче друг до друга, наприклад, з допомогою електростатичних сил, то вийде цікаве слідство. Чим ближче виявлятимуться такі «спільні» рецептори, тим сильнішим буде їхнє спільне тяжіння. Зблизившись, вони почнуть посилювати вплив один одного. Така поведінка відтворює поведінку фільтра Хебба, що виділяє першу головну компоненту. Чим точніше фільтр налаштовується на головну компоненту, тим сильнішою є його реакція, коли вона з'являється в прикладі. Таким чином, якщо після низки ітерацій спільно спрацьовують рецептори виявляться разом в умовному «центрі» кластера, а «зайві» рецептори на віддаленні, на його краях, то, в принципі, такі «зайві» рецептори можуть у якийсь момент самоліквідуватися, то є просто відірватись від кластера. І тоді ми отримаємо поведінку кластера, аналогічну тому, що описано вище в нашій обчислювальній моделі.

Кластери, які пройшли консолідацію, можуть переміститися кудись у тиху гавань, наприклад, в синаптичну щілину. Там існує постсинаптичне ущільнення, за яке кластери рецепторів можуть якоритися, втрачаючи вже непотрібну їм рухливість. Поблизу них будуть знаходитися іонні канали, якими вони зможуть керувати через G-білки. Тепер ці рецептори почнуть впливати на формування локального постсинаптичного потенціалу (потенціалу точки).

Локальний потенціал складається із спільного впливу розташованих поруч активуючих та гальмівних рецепторів. У нашому підході активи відповідають за впізнавання закономірностей, що закликають активувати вихідний біт, що гальмують визначення закономірностей, які блокують дію локальних правил.

Синапси (крапки) розташовані на дендритному дереві. Якщо десь на цьому дереві знаходиться місце, де на невеликій ділянці спрацьовує відразу кілька рецепторів, що активують, і це не блокується гальмівними рецепторами, то виникає дендритний спайк, який поширюється до тіла нейрона і, дійшовши до аксонного горбка, викликає спайк самого нейрона. Дендритне дерево поєднує безліч синапсів, замикаючи їх на один нейрон, що дуже схоже на формування вихідного біта комбінаторного простору.

Об'єднання сигналів із різних синапсів одного дендритного дерева може бути не простим логічним складанням, а бути складнішим і реалізовувати якийсь алгоритм хитрого бустингу.

Нагадаю, що базовий елемент кори – це кортикальна мініколонка. У мініколонці близько ста нейронів розташовані один під одним. При цьому вони щільно огорнуті зв'язками, які набагато сильніші всередині мініколонки, ніж зв'язки, що йдуть до сусідніх мініколонок. Вся кора мозку – це простір таких мініколонок. Один нейрон мініколонки може відповідати одному вихідному біту, всі нейрони однієї кортикальної мініколонки можуть бути аналогом вихідного бінарного вектора.

Кластери рецепторів, описані у цьому розділі, створюють пам'ять, відповідальну пошук закономірностей. Раніше ми описували, як за допомогою кластерів рецепторів створити голографічну пам'ять. Це два різні типи пам'яті, що виконують різні функції, хоча і засновані на загальних механізмах.

Сон

Можна припустити, що уві сні відбувається основна робота з розчищення кластерів рецепторів, що накопичилися протягом дня. У обчислювальній моделі ми описали процедуру "холостого" навчання. Старий досвід пред'являється мозку, не викликаючи формування нових кластерів. Ціль – перевірка вже існуючих гіпотез. Така перевірка складається із двох етапів. Перший - обчислення головної компоненти закономірності і перевірка те, що кількість бітів, відповідальних неї, досить чіткої ідентифікації. Другий – перевірка істинності гіпотези, тобто те, що закономірність опинилася у потрібній точці, що з необхідним вихідним бітом. Можна припустити, частина стадій нічного сну пов'язані з такими процедурами.

Всі процеси, пов'язані зі змінами у клітинах, супроводжуються експресією певних білків та транскрипційних факторів. Є білки та фактори, про які показано, що вони беруть участь у формуванні нового досвіду. Так ось, виявляється, що їхня кількість сильно збільшується під час неспання і різко зменшується під час сну.

Побачити та оцінити концентрацію білків можна через фарбування зрізу мозкової тканини барвником, вибірково реагує на необхідний білок. Подібні спостереження показали, що наймасштабніші зміни для білків, пов'язаних з пам'яттю, відбуваються саме під час сну (Chiara Cirelli, Giulio Tononi, 1998) (Cirelli, 2002) (рисунки нижче).

У такі міркування про роль сну добре вкладається відома кожному особливість – «ранок вечора мудріший». Вранці ми краще орієнтуємося в тому, що ще вчора було не особливо зрозуміло. Все стає чіткішим і очевиднішим. Можливо, цим ми зобов'язані саме масштабної розчищення кластерів рецепторів, що відбулася під час сну. Видаляються помилкові та сумнівні гіпотези, достовірні проходять консолідацію та починають активніше брати участь в інформаційних процесах.

При моделюванні було видно, що кількість хибних гіпотез у багато тисяч разів перевищує кількість істинних. Так як відрізнити одні від інших можна тільки часом і досвідом, то мозку не залишається нічого іншого, крім як збирати всю цю інформаційну руду, сподіваючись знайти в ній з часом грами радію. При отриманні нового досвіду кількість кластерів із гіпотезами, що вимагають перевірки, постійно зростає. Кількість кластерів, що формуються за день і містять руду, яку ще потрібно обробити, може перевищувати кількість кластерів, які відповідають за кодування накопиченого за все попереднє життя перевіреного досвіду. Ресурс мозку для зберігання сирих гіпотез, що вимагають перевірки, повинен бути обмежений. Схоже, що за 16 годин денного неспання кластерами рецепторів практично повністю забивається весь доступний простір. Коли настає цей момент, мозок починає змушувати нас перейти в режим сну, щоб дозволити йому виконати консолідацію та розчистити вільне місце. Мабуть, процес повного розчищення займає близько 8 годин. Якщо розбудити нас раніше, частина кластерів залишиться необробленою. Звідси походить феномен того, що втома накопичується. Якщо кілька днів недосипати, то потім доведеться надолужувати втрачений сон. В іншому випадку мозок починає «аварійно» видаляти кластери, що ні до чого доброго не призводить, тому що позбавляє нас можливості отримати знання з отриманого досвіду. Подію пам'ять швидше за все збережеться, але закономірності залишаться невиявленими.
До речі, моя особиста порада: не нехтуйте якісним сном, особливо якщо ви вчитеся. Не намагайтеся заощадити на сні, щоби більше встигнути. Сон не менш важливий у навчанні, ніж відвідування лекцій та повторення матеріалу на практичних заняттях. Недарма діти у періоди розвитку, коли накопичення та узагальнення інформації йде найактивніше, більшу частину часу проводять уві сні.

Швидкодія мозку

Тепер можна припустити, кожен кластер рецепторів може працювати як автономний обчислювальний елемент, виконуючи весь цикл обчислень з перевірки своєї гіпотези. Таких кластерів в одній лише кортикальній колонці може бути сотні мільйонів. Це означає, що хоча частоти, з якими працює мозок, далекі від частот, на яких працюють сучасні комп'ютери, турбуватися про швидкодію мозку не варто. Сотні мільйонів кластерів рецепторів, що працюють паралельно в кожній мініколонці кори, дозволяють успішно вирішувати складні завдання, що знаходяться на кордоні з комбінаторним вибухом. Чудес не буває. Але можна навчитися ходити за межею.

Розглянемо одну з корисних опцій, яку пропонує програма Microsoft Excel. Ліцензійну версію цієї програми ви можете купити в нашому інтернет-магазині зі знижкою. Ціни та версії можна подивитися.

Сьогодні йтиметься про «Умовне форматування». Воно призначено виділення осередків таблиці, мають спільні риси. Це може бути ідентичний шрифт, значення, тло і т.д. Ця операція передбачає різні зміни: строгість перевірки, зміст збігів, їх тотожність і варіативність.

1. Почнемо із запуску програми Microsoft Excel, що містить необхідну нам таблицю. Потім виділяємо діапазон осередків, що потребують обробки. Мова йде про спільність стовпців і осередків, що формують частину таблиці, або кілька незв'язаних між собою областей таблиці.
2. Далі нам потрібно пройти наступний шлях:

1. Програма має широку лінійку можливостей, зокрема: можна вибрати підсвічування осередків, що потрапили у відбір, передбачений варіант вибору фону заливки (програма надає 6 колірних рішень), варіації шрифтів та табличних рамок. Можливий вибір «КОРИСТУВАЛЬНОГО ФОРМАТУ», який дозволяє створити свій варіант оформлення осередків. Щоб скасувати вибірку комірок, натисніть OK.

Використання функції «РІВНЕ»

Якщо потрібні для виділення осередки мають абсолютно конкретне значення, скористайтесь пунктом «РІВНЕ» у списку «УМОВЕ ФОРМАТУВАННЯ», що знаходиться в розділі «ПРАВИЛА ВИДІЛЕННЯ осередків». У діалоговому вікні відзначте осередки, що вас цікавлять, що вимагають виявлення дублікатів, при цьому їх адреса з'явиться в сусідньому діалоговому вікні. Опанувавши ці нехитрі навички, ви зможете значно скоротити час на обробку табличних даних та угруповання загальних значень.

Відео: Пошук збігів у 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);"")

Сподіваюся, що хоча б одна формула, описана в цьому підручнику, видалася Вам корисною. Якщо Ви стикалися з іншими завданнями пошуку, для яких не змогли знайти відповідне рішення серед інформації в цьому уроці, сміливо опишіть свою проблему в коментарях, і ми всі намагатимемося вирішити її.