history 24 октября 2021 г.
- Группы статей
- Последовательности Чисел
Пусть дана некая числовая последовательность, значения которой содержатся в отдельной строке или столбце. Будем искать в столбце с данными повтор такой последовательности.
На картинке выше Искомая последовательность размещена в строке 7 и выделена зеленым (шесть чисел). Поиск производится в столбце В, начиная с ячейки В11. На картинке показана найденная последовательность, которая также выделена зеленым для наглядности.
Найти последовательность можно разными способами, в статье приведено их 2.
Примечание: Приведенные в статье формулы будут работать во всех версиях MS EXCEL — EXCEL 365, Excel 2021/2019/2016/2013/2010/2007 и Excel 97–2003.
Способ 1
В 11-й строке (с этой строки начинается список) выведем первые 10 значений из списка. Это можно сделать формулой массиваТРАНСП() или простыми формулами.
Совет: О транспонировании строк подробно написано в этом разделе https://excel2.ru/gruppy-statey/transponirovanie. В этих статьях показано как можно транспонировать диапазоны и без формул массива.
Чтобы ввести функцию ТРАНСП()
- выделите диапазон ячеек С11:L11,
- затем в Строке формул введите =ТРАНСП(B11:B20),
- нажмите CTRL+SHIFT+ENTER.
После копирования указанных ячеек вниз (также нужно сначала выделить диапазон С11:L11 и протянуть вниз с помощью Маркера заполнения. В итоге будет сформирован массив чисел как на рисунке выше. В строке 12 будут размещены значения из списка, начиная со второго, в строке 13, начиная с третьего и т.д.
Теперь займемся построчным сравнением. В способе 1 это реализовано замысловатой формулой =НЕ(СУММПРОИЗВ(—(СМЕЩ(C11;;;;$A$8)<>СМЕЩ($C$8;;;;$A$8)))) в столбце N.
Разберем подробнее. Так как длина исходной последовательности может быть произвольной, то нам нужно «вырезать» из диапазона С8:L8 только те ячейки, которые содержат значения последовательности, а не пустые ячейки. Это сделано формулой СМЕЩ($C$8;;;;$A$8), где в А8 содержится длина последовательности. О функции СМЕЩ() читайте здесь.
Аналогично формируется диапазон для сравнения СМЕЩ(C11;;;;$A$8). Обратите внимание на отсутствие знаков $ абсолютной адресации. Т.к. этот диапазон должен изменяться при копировании формулы вниз (в отличие от первого диапазона).
Идем дальше — посмотрим на выражение (СМЕЩ(C11;;;;$A$8)<>СМЕЩ($C$8;;;;$A$8)). Для первого сравнения (строка 11 и исходная последовательность) это выражение даст {ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА}, т.е. ни одно из значений не совпало (этот массив можно получить выделив в Строке формул это выражение и нажать F9). Оно и понятно, исходная последовательность 5; 6; 11; 6; 8; 1 ничего не имеет общего со значениями 11-й строки 1; 2; 34; 5; 6; 11 (если сравнивать поэлементно).
Совсем другое дело, если сравнивать со строкой 14. Выражение вернет {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}, что означает, что первый элемент исходной последовательности (5) равен значению из ячейки С14, второй (6) равно значению из ячейки D14, и т.д.
Затем подвергаем полученный массив преобразованию с помощью двойной смены знака (—), при этом ЛОЖЬ будет переведена в число 0, а ИСТИНА — в 1 (так устроен EXCEL: он хранит ИСТИНА как 1, а ЛОЖЬ как 0). Вместо двукратного умножения на -1, можно было прибавить 0 или возвести в степень 1. Любое математическое действие со значениями логического типа данных будет преобразовывать их в число 0 или 1.
Функция СУММПРОИЗВ() просто сложит все 0 или 1. В результате получим 0 только если все значения попарно равны между собой. Можно вместо СУММПРОИЗВ() использовать функцию СУММ(), но тогда нам пришлось бы вводить ее как формулу массива.
Наконец, функция НЕ() переводит числовые значения в логический тип данных, изменяя на противоположное значение: 0 — будет переведен в ИСТИНА, а любое другое число в ЛОЖЬ. Можно обойтись и без этой функции, тогда будет выведено число от 0 (все значения совпали) до 6 (ни одно не совпало).
Формула в ячейке N8 =ПОИСКПОЗ(ИСТИНА;N11:N55;0) вернет номер позиции первой ячейки со значением ИСТИНА (их может быть несколько, об этом ниже). Начиная с этой позиции списка будет располагаться искомая последовательность. Ее можно выделить условным форматированием.
Чтобы настроить Условное форматирование нам потребуется написать простое правило =И($A11>=$N$8;$A11<$N$8+$A$8).
Вариантов написания формулы для поиска последовательности множество, например, формула =(СУММПРОИЗВ(—(СМЕЩ(C11;;;;$A$8)=СМЕЩ($C$8;;;;$A$8))))=$A$8, которая вернет ИСТИНА в тех же строках, что и предыдущая формула.
На листе «все найденные» приведен поиск сразу всех вхождений последовательности.
Для реализации этого решения добавлено несколько формул:
- используйте формулу =СУММПРОИЗВ(—N11:N55) чтобы найти общее количество исходных последовательностей в списке (ячейка О6)
- формулы в столбце О: =СЧЁТЕСЛИ($N$11:N11;ИСТИНА), будут показывать сколько последовательностей найдено в строках выше.
- в ячейке О7 (серая ячейка) для удобства сформирован выпадающий список, это реализовано формулой =СМЕЩ($A$11;;;$O$6). Если, например, найдено 3 совпадения, то будет сформирован список 1; 2; 3. Выбирая нужный повтор в списке будут подсвечены соответствующие найденные последовательности.
- если кому не удобно пользоваться выпадающим списком можно нажимать на элемент управления Счетчик.
В этой статье разжеваны все формулы, если и теперь не все понятно, то нужно почитать статьи на нашем сайте excel2.ru, на которые ведут ссылки в этой статье.
Способ 2
На листе Вариант2 приведено другое решение этой задачи (подсвечивается только первая найденная последовательность).
В этом варианте нет формулы массива ТРАНСП(). Исходный список многократно повторяется начиная со строки 14 и ниже (при этом последовательно отбрасывается первое значение из предыдущей строки и производится смещение всего списка на 1 позицию влево). Такой подход позволяет сравнивать исходный список (столбец D с зелеными ячейками) со столбцами значений, вырезанными из исходного списка.
При увеличении длины исходного списка соответственно растет количество столбцов в этих строках. При большой длине списка это не удобно.
Количество совпавших значений выводится в строке 11. Если это число совпадений равно длине искомой последовательности, то это значение подсвечивается красным. Найденная позиция отображается в строке 13.
Вариантов решения этой задачи множество, поэтому если будете решать самостоятельно, то наверняка придете к другому варианту решения.
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Описание
В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание образца листа
В этой статье используется образец листа для иллюстрации встроенных функций Excel. Рассматривайте пример ссылки на имя из столбца A и возвращает возраст этого человека из столбца C. Чтобы создать этот лист, введите указанные ниже данные в пустой лист Excel.
Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
|
A |
B |
C |
D |
E |
||
|
1 |
Имя |
Правитель |
Возраст |
Поиск значения |
||
|
2 |
Анри |
501 |
Плот |
Иванов |
||
|
3 |
Стэн |
201 |
19 |
|||
|
4 |
Иванов |
101 |
максималь |
|||
|
5 |
Ларри |
301 |
составляет |
Определения терминов
В этой статье для описания встроенных функций Excel используются указанные ниже условия.
|
Термин |
Определение |
Пример |
|
Массив таблиц |
Вся таблица подстановки |
A2: C5 |
|
Превышающ |
Значение, которое будет найдено в первом столбце аргумента «инфо_таблица». |
E2 |
|
Просматриваемый_массив |
Диапазон ячеек, которые содержат возможные значения подстановки. |
A2: A5 |
|
Номер_столбца |
Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение. |
3 (третий столбец в инфо_таблица) |
|
Ресулт_аррай |
Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор. |
C2: C5 |
|
Интервальный_просмотр |
Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение. |
ЛОЖЬ |
|
Топ_целл |
Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?. |
|
|
Оффсет_кол |
Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение «5» в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки). |
Функции
LOOKUP ()
Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.
Ниже приведен пример синтаксиса формулы подСТАНОВКи.
= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)
Следующая формула находит возраст Марии на листе «образец».
= ПРОСМОТР (E2; A2: A5; C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как «Мария» находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).
Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.
Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
Использование функции Просмотр в Excel
ВПР ()
Функция ВПР или вертикальный просмотр используется, если данные указаны в столбцах. Эта функция выполняет поиск значения в левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Функцию ВПР можно использовать для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Следующая формула находит возраст Марии на листе «образец».
= ВПР (E2; A2: C5; 3; ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется «3» в качестве Колумн_индекс (столбец C). Так как «Мария» находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).
Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
Как найти точное совпадение с помощью функций ВПР или ГПР
INDEX () и MATCH ()
Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.
Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:
= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)
Следующая формула находит возраст Марии на листе «образец».
= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как «Мария» находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
Обратите внимание Если ни одна из ячеек в аргументе «число» не соответствует искомому значению («Мария»), эта формула будет возвращать #N/А.
Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:
Поиск данных в таблице с помощью функции индекс
СМЕЩ () и MATCH ()
Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.
= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)
Эта формула находит возраст Марии на листе «образец».
= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как «Мария» находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
Использование функции СМЕЩ
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
17 авг. 2022 г.
читать 2 мин
Вы можете использовать следующую формулу, чтобы найти несколько значений в Excel:
=INDEX( $A$1:$B$12 ,SMALL(IF( $A$1:$A$12 = $F$1 ,ROW( $A$1:$A$12 )),ROW( 1:1 )),2)
Эта конкретная формула находит все значения в диапазоне B1:B12 , где соответствующее значение в диапазоне A1:A12 равно значению в ячейке F1 .
В следующем примере показано, как использовать эту формулу на практике.
Пример: поиск нескольких значений в Excel
Предположим, у нас есть следующий набор данных в Excel, показывающий, какие сотрудники продавали различные продукты в какой-то компании:
Теперь предположим, что мы хотим найти все продукты, продаваемые Майком.
Для этого мы можем ввести его имя в ячейку D2 :
Затем мы можем ввести следующую формулу в ячейку E2 :
=INDEX( $A$1:$B$12 ,SMALL(IF( $A$1:$A$12 = $D$2 ,ROW( $A$1:$A$12 )),ROW( 1:1 )),2)
Это вернет первый продукт, проданный Майком:
Затем мы можем автоматически заполнить эту формулу до оставшихся ячеек в столбце E, чтобы найти все продукты, продаваемые Майком:
Теперь мы можем видеть все четыре продукта, проданных Майком:
- Апельсины
- киви
- яблоки
- Бананы
Мы можем посмотреть на исходные данные в столбцах A и B, чтобы убедиться, что Майк действительно продал все четыре продукта.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как подсчитать количество вхождений в Excel
Как подсчитать частоту текста в Excel
Как рассчитать относительную частоту в Excel
Предположим, у вас есть длинный список порядковых номеров для маркировки элементов, таких как номера чеков в выписках по счету, обычно мы прокручиваем и находим недостающие порядковые номера вручную. Иногда это довольно сложно и требует много времени. Вы можете придумать хитрые способы справиться с этим. Да, есть несколько простых способов быстро и удобно определить и найти последовательность отсутствующих чисел в Excel 2007, Excel 2010 и Excel 2013.
Определите последовательность отсутствующих чисел с помощью формулы ЕСЛИ
Определить последовательность отсутствующих чисел с помощью формулы массива
Определить последовательность отсутствующих чисел с помощью Kutools for Быстро Excel
Найдите и выделите отсутствующие числа в последовательности:
Kutools for Excel Функция «Найти отсутствующий порядковый номер может помочь вам быстро и легко найти недостающую последовательность и вставить недостающие числа или пустые строки в существующую последовательность данных или заполнить цвет фона при обнаружении отсутствующей последовательности.
Kutools for Excel : с более чем 200 удобными надстройками Excel, можно бесплатно попробовать без ограничений в течение 60 дней. Скачать и бесплатную пробную версию прямо сейчас!
->
Содержание
- Определите последовательность пропущенных чисел с помощью формулы ЕСЛИ
- С помощью формула массива
- Быстрое определение последовательности отсутствующих чисел с помощью Kutools for Excel
- Быстрое определение последовательности отсутствующих чисел с помощью Kutools for Excel
Определите последовательность пропущенных чисел с помощью формулы ЕСЛИ
Определите последовательность пропущенных чисел с помощью формулы ЕСЛИ Как мы все знаем, большинство порядковых номеров имеют фиксированное приращение из 1, например 1, 2, 3,…, N. Следовательно, если вы можете определить, что число не меньше 1, чем следующее за ним число, это означает отсутствие числа.
Мы покажем вам представлены учебные пособия с примером, показанным на следующем снимке экрана:
1. В пустой ячейке введите формулу = IF (A3-A2 = 1, “”, “Отсутствует”) и нажмите клавишу Enter . В этом случае мы вводим формулу в ячейку B2.
Если нет пропущенных чисел, эта формула ничего не вернет; если пропущенные числа существуют, он вернет текст «Отсутствует» в активной ячейке.
2. Выделите ячейку B2 и перетащите маркер заполнения над диапазоном ячеек, который вы хотите содержать эту формулу. Теперь он идентифицирует отсутствующие числа с текстом «Отсутствует» в соответствующих ячейках столбца B. См. Следующий снимок экрана:
С помощью формула массива
Иногда требуется не только определить последовательность пропущенных чисел, но и перечислить отсутствующие числа. Вы можете справиться с этим, выполнив следующие шаги:
1. в соседней ячейке введите формулу = SMALL (IF (ISNA (MATCH (ROW (A $ 1: A $ 30), A $ 1: A $ 30,0)), ROW (A $ 1: A $ 30) ), СТРОКА (A1))
A1: A30 = диапазон чисел, последовательность для проверки против – от 1 до 30
2. Одновременно нажмите клавиши Ctrl + Shift + Enter , чтобы завершить формулу. Скопируйте формулу, пока не получите # ЧИСЛО! ошибки, означающие, что были перечислены все отсутствующие числа. См. Снимок экрана:
Быстрое определение последовательности отсутствующих чисел с помощью Kutools for Excel
Только указанные выше методы могут Определите отсутствующую чистую числовую последовательность, если у вас есть такая последовательность, как AA-1001-BB, AA-1002-BB, они могут не работать успешно. Но не волнуйтесь, мощная функция Kutools for Excel – Найти отсутствующий порядковый номер может помочь вам быстро определить недостающую последовательность.
Примечание. Чтобы применить этот Найти отсутствующий порядковый номер , во-первых, вы должны загрузить Kutools для Excel , а затем быстро и легко примените эту функцию.
После установки Kutools for Excel сделайте следующее:
1. Выберите последовательность данных, в которой вы хотите найти недостающую последовательность.
2. Нажмите Kutools > Вставить > Найти отсутствующий порядковый номер , см. Снимок экрана:
3. В диалоговом окне Найти отсутствующий порядковый номер :
(1.) Если вы выбрали Вставить новый столбец со следующим отсутствующим маркером вариант, все отсутствующие порядковые номера были отмечены текстом Missing в новом столбце рядом с вашими данными. См. Снимок экрана:
(2.) Если вы выберете Вставка отсутствующего порядкового номера , все недостающие номера были вставлены в список последовательностей. См. Снимок экрана:
(3.) Если вы выберете Вставка пустых строк при включении отсутствующих порядковых номеров , все пустые строки вставляются, если отсутствуют номера. См. Снимок экрана:
(4.) Если вы выберете Заполнить цвет фона , расположение недостающих чисел будет выделено сразу. См. Снимок экрана:
Быстрое определение последовательности отсутствующих чисел с помощью Kutools for Excel
Kutools for Excel : с более чем 300 удобными надстройками Excel, попробуйте бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
|
Поиск последовательности в части содержимого ячеек |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
