Используйте INDEX и MATCH для простых запросов к базе данных в Excel

Первоначально Excel не был предназначен для реальной базы данных. Его ранние функции базы данных были ограничены по количеству и качеству. И поскольку каждая запись в базе данных Excel видна сразу на экране - что означает все сразу в памяти - базы данных Excel должны быть действительно маленькими: несколько полей с несколькими записями или несколько полей с большим количеством записей; и минимальные вычисления.

VLOOKUP (вертикальный) и HLOOKUP (по горизонтали) были единственными функциями, доступными для запроса базы данных для конкретной информации. Например, вы можете запросить поиск и извлечение всех записей с объемом продаж более 1000 долларов США, но менее 5000 долларов США, но только на плоских файлах (только одна матрица базы данных).

Таблицы сводных таблиц были разработаны таким образом, чтобы пользователи могли создавать реляционные базы данных, которые проще запросить, использовать меньше памяти и обеспечить более точные результаты. Если, однако, у вас нет или нужна реляционная база данных, но для этого требуются более мощные и надежные функции базы данных, попробуйте их для начала.

[Дополнительная информация: ваш новый компьютер нуждается в этих 15 бесплатных, отличных программах]

Индекс, совпадение и соответствие индексов

В Excel функция INDEX возвращает элемент из определенной позиции (в списке, таблице, базе данных).

Функция MATCH возвращает позицию значения (в списке , таблица, база данных). И функции INDEX-MATCH, используемые совместно, делают извлечение данных из таблицы ветром.

Синтаксис для функции INDEX: INDEX (array, row_num, [column_num]). Массив - это диапазон ячеек, с которыми вы работаете. Row_num - это, очевидно, номер строки в диапазоне, который содержит данные, которые вы ищете. Column_num - это номер столбца в диапазоне, который содержит данные, которые вы ищете. Формула INDEX не распознает буквы столбцов, поэтому вы должны использовать числа (считая слева).

Синтаксис функции MATCH: MATCH (lookup_value, lookup_array, [match_type]). Lookup_value - это число или текст, который вы ищете, который может быть значением, логическим значением или ссылкой на ячейку. Lookup_array - это диапазон ячеек, с которыми вы работаете. Функция match_type определяет функцию MATCH, то есть точное совпадение или ближайшее совпадение.

A. Функция INDEX

В нашем примере знаменитый коммодор Джеймс Норрингтон имеет электронную таблицу, которая отслеживает все пиратские корабли в Карибском бассейне. Список Норрингтона устроен боевыми формированиями кораблей, которые соответствуют его морским картам области. Когда он видит, что судно продвигается вперед, он вводит формулу Индекса в свою электронную таблицу, чтобы он мог определить корабль и его мощности. В этом первом запросе Норрингтон хочет узнать тип корабля, который продвигается.

1. Выберите местоположение (ячейку или диапазон ячеек) для ваших запросов (то есть функции и результаты), затем переместите курсор в эту ячейку. Например: любая ячейка в строке 18.

2. Введите функцию INDEX (впереди знак равенства) плюс открывающую скобку, затем выделите (или введите) диапазон базы данных / таблицы следующим образом: = INDEX (A2: I16

Примечание: если вы хотите абсолютную ссылку (которая , в этом случае означает жесткое кодирование формулы, поэтому, когда / если оно скопировано, диапазон не изменяется), нажмите F4 один раз после каждой ссылки на ячейку. Вы также можете выделить диапазон: просто нажмите F4 один раз после того, как вы выберете полный диапазон , и добавляются полные абсолютные ссылочные символы.

3. Затем введите запятую для разделения аргументов (т. е. отдельных частей формулы), затем введите номер строки и запятую, а затем номер столбца (да , это должно быть число, а не обычная буква столбца) и правая скобка (или просто нажмите Enter и пусть Excel добавит к вам конечную скобку). Полная формула выглядит так: = INDEX ($ A $ 2: $ I $ 16 , 15,2).

Примечание. Нумерация строк начинается с первого числа в диапазоне, а не первого номера в электронной таблице. Например, eve n, хотя пиратский корабль Cavalleria находится на шестнадцатой строке Excel 16, это фактически строка 15 в нашей формуле, потому что наш диапазон начинается с A2 и проходит через I16. Если A2 - строка 1, то A16 - строка 15).

4. Обратите внимание, что тип корабля, который Норрингтон искал, - это военный шлюз.

JD Sartain

Используйте функцию индекса для поиска конкретной информации в вашей базе данных.

B. Диапазоны INDEX

Теперь мы можем сделать намного больше с этой таблицей базы данных. Вам не нужно переопределять диапазон каждый раз, когда вы хотите что-то знать. Чтобы это было легко, мы определим диапазон один раз, а затем назовите его. Тогда мы можем просто поместить имя диапазона в наши формулы.

1. Перейдите к A2 и выделите диапазон от A2 до I16.

2. На вкладке «Формулы» выберите «Определить имя » в группе «Определенные имена».

3. В диалоговом окне всплывающего окна введите имя своего диапазона в поле «Имя».

4. Затем введите область действия (где находится диапазон), который является либо рабочим, либо одним из рабочих листов в книге.

5. Введите комментарий, если необходимо.

6. И, наконец, убедитесь, что в поле Refres To отображается правильное имя и диапазон, затем нажмите OK .

7. Если вы хотите проверить, что ваш диапазон действительно сохранен в Excel, попробуйте этот небольшой тест: нажмите Ctrl + G (команда GoTo). Выберите Корабли в диалоговом окне GoTo, затем нажмите OK , а Excel снова выделит диапазон A2: I16.

Как определить и сохранить диапазон

C. INDEX с формулой SUM & AVERAGE

Норрингтон оценивает боевые возможности флота. Сначала он хочет знать, сколько пушек у пиратов, среднее количество пушек на корабль и общее количество экипажей, которые управляют всеми этими пиратскими кораблями. Он вводит следующие формулы:

1. = SUM (INDEX (Ships, 8)) равно 334, общее количество пушек и

2. = СРЕДНИЙ (ИНДЕКС (Корабли, 8)) равен 22,27, или приблизительно 22,27 пушки на судно.

3. = SUM (INDEX (Ships, 7)) равно 2350, общее количество всех членов экипажа на всех кораблях.

Почему есть запятая, пробел, запятая между кораблями и номером 8 и что означают эти цифры? Ships - это диапазон (за которым следует запятая), аргумент Row пуст (или пробел), потому что Norrington хочет все строки, а 8 представляет восьмой столбец (который является столбцом H, Cannons).

Некоторые могут спросить , почему бы просто не ввести формулы SUM и / или AVERAGE внизу этих столбцов? В этой крошечной таблице, да, было бы так же легко. Но если таблица содержит 5000 строк и 300 столбцов, вы можете использовать INDEX

JD Sartain

03 формулы INDEX, используя SUM и AVERAGE.

После того, как диапазон указан, Norrington может открыть пустую электронную таблицу в эту же рабочую книгу и написать свои запросы (формулы) в столбце B (которые показывают результаты вместо формул) с описанием, которое определяет эти запросы в столбце A. (Примечание: столбец C показывает фактические формулы, которые находятся в столбце B).

Ему не нужно визуально видеть свою огромную базу данных из 5000 записей или ждать несколько секунд, пока формулы вычисляются. Он может получить всю необходимую информацию из своего листа запросов. Помните, чем крупнее электронная таблица, тем медленнее она функционирует, особенно если есть много формул.

JD Sartain

04 Информация о файле / запросе пиратского корабля Commodore James Norrington.

D. INDEX MATCH с MAX

Теперь Норрингтон хочет знать, сколько пиратов находится на самом населенном корабле, и какой корабль это? Он использует INDEX с формулой MAX, чтобы получить максимальное количество пиратов, но он также должен знать, какой корабль несет их. Поэтому он использует формулу INDEX / MATCH с формулой MAX, чтобы узнать, на каком корабле есть самые пираты на борту.

1. = MAX (INDEX (Ships, 7)) равно 300, максимальное количество пиратов на одном из кораблей

2. = INDEX ($ A $ 2: $ A $ 16, MATCH (MAX (Корабли), $ G $ 2: G $ 16, 0)) равно Королевскому Джеймсу, кораблю с самыми пиратами на борту

3. = INDEX ($ F $ 2: $ F $ 16, MATCH (MAX (Ships), $ G $ 2: G $ 16, 0)) равно Stede Bonnet, капитан Королевского Джеймса с пиратской командой 300

JD Sartain

Используйте INDEX-MATCH и MAX для получения определенной информации из вашей базы данных.