Поиск значений в списке данных
Предположим, что вы хотите найти расширение телефона сотрудника, используя его номер эмблемы или правильную ставку комиссионных за объем продаж. Вы можете искать данные для быстрого и эффективного поиска определенных данных в списке, а также для автоматической проверки правильности данных. После поиска данных можно выполнить вычисления или отобразить результаты с возвращаемой величиной. Существует несколько способов поиска значений в списке данных и отображения результатов.
Что необходимо сделать
- Точное совпадение значений по вертикали в списке
- Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
- Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
- Точное совпадение значений по горизонтали в списке
- Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
- Создание формулы подступа с помощью мастера подметок (только в Excel 2007)
Точное совпадение значений по вертикали в списке
Для этого можно использовать функцию ВLOOKUP или сочетание функций ИНДЕКС и НАЙТИПОЗ.
Примеры ВРОТ
Дополнительные сведения см. в этой информации.
Примеры индексов и совпадений
=ИНДЕКС(нужно вернуть значение из C2:C10, которое будет соответствовать ПОИСКПОЗ(первое значение «Капуста» в массиве B2:B10))
Формула ищет в C2:C10 первое значение, соответствующее значению «Ольга» (в B7), и возвращает значение в C7(100),которое является первым значением, которое соответствует значению «Ольга».
Подыыывка значений по вертикали в списке с помощью приблизительного совпадения
Для этого используйте функцию ВЛВП.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ВРОТ ищет имя учащегося, у которого 6 просмотров в диапазоне A2:B7. В таблице нет записи для 6 просмотров, поэтому ВРОТ ищет следующее самое высокое совпадение меньше 6 и находит значение 5, связанное с именем Виктор,и таким образом возвращает Его.
Дополнительные сведения см. в этой информации.
Подстановка значений по вертикали в списке неизвестного размера с использованием точного совпадения
Для этого используйте функции СМЕЩЕНИЕ и НАЙТИВМЕСЯК.
Примечание: Используйте этот подход, если данные в диапазоне внешних данных обновляются каждый день. Вы знаете, что цена находится в столбце B, но вы не знаете, сколько строк данных возвращает сервер, а первый столбец не отсортировали по алфавиту.
C1 — это левые верхние ячейки диапазона (также называемые начальной).
MATCH(«Оранжевая»;C2:C7;0) ищет «Оранжевые» в диапазоне C2:C7. В диапазон не следует включать запускаемую ячейку.
1 — количество столбцов справа от начальной ячейки, из которых должно быть возвращено значение. В нашем примере возвращается значение из столбца D, Sales.
Точное совпадение значений по горизонтали в списке
Для этого используйте функцию ГГПУ. См. пример ниже.
Г ПРОСМОТР ищет столбец «Продажи» и возвращает значение из строки 5 в указанном диапазоне.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
Подыыывка значений по горизонтали в списке с использованием приблизительного совпадения
Для этого используйте функцию ГГПУ.
Важно: Убедитесь, что значения в первой строке отсортировали в порядке возрастания.
В примере выше ГЛЕБ ищет значение 11000 в строке 3 указанного диапазона. Она не находит 11000, поэтому ищет следующее наибольшее значение меньше 1100 и возвращает значение 10543.
Дополнительные сведения см. в сведениях о функции Г ПРОСМОТР.
Создание формулы подступа с помощью мастера подметок (толькоExcel 2007 )
Примечание: В Excel 2010 больше не будет надстройки #x0. Эта функция была заменена мастером функций и доступными функциями подменю и справки (справка).
В Excel 2007 создается формула подытов на основе данных на основе данных на основе строк и столбцов. Если вы знаете значение в одном столбце и наоборот, мастер под поисков помогает находить другие значения в строке. В формулах, которые он создает, используются индекс и MATCH.
- Щелкните ячейку в диапазоне.
- На вкладке Формулы в группе Решения нажмите кнопку Под поиск.
- Если команда Подытов недоступна, вам необходимо загрузить мастер под надстройка подытогов. Загрузка надстройки «Мастер подстройок»
- Нажмите кнопку Microsoft Office , выберите Параметры Excel и щелкните категорию Надстройки.
- В поле Управление выберите элемент Надстройки Excel и нажмите кнопку Перейти.
- В диалоговом окне Доступные надстройки щелкните рядом с полем Мастер подстрок инажмите кнопку ОК.
- Следуйте инструкциям мастера.
Как интерполировать пропущенные значения в Excel
Часто у вас может быть одно или несколько отсутствующих значений в ряду в Excel, которые вы хотели бы заполнить.
Самый простой способ заполнить пропущенные значения — использовать функцию « Заполнить серию» в разделе « Редактирование » на вкладке « Главная ».
В этом руководстве представлены два примера практического использования этой функции.
Пример 1. Заполнение пропущенных значений для линейного тренда
Предположим, у нас есть следующий набор данных с несколькими отсутствующими значениями в Excel:
Если мы создадим быструю линейную диаграмму этих данных, мы увидим, что данные следуют линейному тренду:
Чтобы заполнить пропущенные значения, мы можем выделить диапазон, начинающийся до и после пропущенных значений, затем нажать « Главная»> «Редактирование»> «Заливка»> «Серия» .
Если мы оставим Type как Linear , Excel будет использовать следующую формулу, чтобы определить, какое значение шага использовать для заполнения недостающих данных:
Шаг = (Конец – Начало) / (#Отсутствующие наблюдения + 1)
В этом примере он определяет значение шага следующим образом: (35-20) / (4+1) = 3 .
Как только мы нажимаем OK , Excel автоматически заполняет пропущенные значения, добавляя 3 к каждому последующему значению:
Пример 2. Заполнение пропущенных значений для тренда роста
Предположим, у нас есть следующий набор данных с несколькими отсутствующими значениями в Excel:
Если мы создадим быструю линейную диаграмму этих данных, мы увидим, что данные следуют экспоненциальному (или «ростовому») тренду:
Чтобы заполнить пропущенные значения, мы можем выделить диапазон, начинающийся до и после пропущенных значений, затем нажать « Главная»> «Редактирование»> «Заливка»> «Серия» .
Если мы выберем Type as Growth и установим флажок рядом с Trend , Excel автоматически определит тенденцию роста в данных и заполнит пропущенные значения.
Как только мы нажмем OK , Excel заполнит недостающие значения:
Из графика видно, что заполненные значения достаточно хорошо соответствуют общему тренду данных.
Вы можете найти больше учебников по Excel здесь .
Как найти недостающие значения в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Найти недостающие значения
Если вы хотите выяснить, какие значения в одном списке отсутствуют из другого списка, вы можете использовать простую формулу, основанную на функции СЧЕТЕСЛИ.
Функция СЧЕТЕСЛИ подсчитывает ячейки, которые отвечают критериям, возвращая число найденных вхождений. Если такие ячейки не найдены, СЧЕТЕСЛИ возвращает ноль.
В показанном примере, формула в G5 является:
Где «список» является именованный диапазон, что соответствует диапазону B6: B11.
Функция ЕСЛИ требует логического теста, чтобы вернуть значение ИСТИНА или ЛОЖЬ. В этом случае, если значение найдено, положительное число возвращается СЧЕТЕСЛИ, который имеет значение ИСТИНА, в результате чего, если вернуть «ОК». Если значение не найдено, возвращается ноль, который имеет значение ЛОЖЬ, и ЕСЛИ возвращает «Отсутствует».
Количество пропущенных значений
Для подсчета значений в одном списке, которые отсутствуют в другом списке, вы можете использовать формулу, основанную на функциях СЧЕТЕСЛИ и СУММПРОИЗВ.
Функции СЧЕТЕСЛИ проверяет значения в диапазоне от критериев. Часто, только один критерий подается, но в этом случае мы поставляем больше чем один критерий.
Для диапазона, мы даем СЧЕТЕСЛИ именованному диапазону лист1 (B6: B11) и критериям мы обеспечиваем именованный диапазон лист2 (F6: F8).
Потому что мы даем СЧЕТЕСЛИ более чем один критерий, мы получим более одного результата в массиве, который выглядит следующим образом:
Мы хотим, чтобы рассчитывались только те значения, которые отсутствуют, которые по определению имеют счетчик, равный нулю, поэтому мы преобразуем эти значения ИСТИНА и ЛОЖЬ с «= 0» заявлением, что дает:
Тогда мы изменим значения ИСТИНА/ЛОЖЬ в 1 и 0 с двойным отрицательным оператором (-), который производит:
Наконец, мы используем СУММПРОИЗВ, чтобы сложить элементы в массиве и получить общее количество пропущенных значений.