Пересечение диапазона в Excel

Обновлено: 22.11.2024

Формула ПОИСКПОЗ ИНДЕКС представляет собой комбинацию двух функций Excel: ИНДЕКС и ПОИСКПОЗ.

  • =INDEX() возвращает значение ячейки в таблице на основе номера столбца и строки.
  • =MATCH() возвращает позицию ячейки в строке или столбце.

В сочетании две формулы могут искать и возвращать значение ячейки в таблице на основе вертикального и горизонтального критериев. Для краткости это называется просто функцией сопоставления индексов.

Это особенно полезно в Clio, так как позволяет вводить данные из других отчетов, если в обоих отчетах есть общие данные (например, "Имя", "Номер дела" или "Идентификатор" поле, позволяющее делать перекрестные ссылки.)

Основная структура формулы выглядит следующим образом:

  • contacts.csv!$H:$H = заблокированный диапазон, содержащий значения, которые вы хотите извлечь
  • важно.csv! F2 = разблокирована отдельная ячейка в текущем отчете, содержащая данные поиска (например, имя клиента или номер дела), которые вы используете для перекрестных ссылок.
  • contacts.csv!$A:$A = Заблокированный диапазон, содержащий список значений, на которые вы ссылаетесь (например, столбец "Имя клиента" или "Номер дела")

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

Выполните следующие действия:

  1. Введите «=INDEX(» и выберите область таблицы, затем добавьте запятую
  2. Введите номер строки для Кевина (4) и добавьте запятую.
  3. Введите номер столбца для высоты (2) и закройте квадратную скобку.
  4. Результат: «5,8».

Используя тот же пример, что и выше, давайте воспользуемся ПОИСКПОЗ, чтобы выяснить, в какой строке находится Кевин.

Выполните следующие действия:

  1. Введите «=ПОИСКПОЗ(» и укажите ссылку на ячейку, содержащую «Кевин»… имя, которое мы хотим найти
  2. Выделите все ячейки в столбце "Имя" (включая заголовок "Имя").
  3. Введите ноль «0» для точного совпадения.
  4. В результате Кевин находится в строке «4».

Используйте ПОИСКПОЗ еще раз, чтобы выяснить, в каком столбце находится высота.

Выполните следующие действия:

  1. Введите «=ПОИСКПОЗ(» и укажите ссылку на ячейку, содержащую «Рост»… критерии, которые мы хотим найти
  2. Выберите все ячейки в верхней строке таблицы.
  3. Введите ноль «0» для точного совпадения.
  4. В результате высота находится в столбце "2"

Спасибо за отзыв.

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

Оператор пересечения в Excel можно использовать для поиска пересекающихся значений двух списков/диапазонов. Это необычный оператор, так как он представлен пробелом (да, верно).

Если вы используете пробел между двумя диапазонами, он становится оператором пересечения в Excel.

Оператор пересечения в Excel

Вы можете использовать оператор пересечения в Excel, чтобы найти:

  • Пересечение одной строки и столбца.
  • Пересечение нескольких строк и столбцов.
  • Пересечение именованных диапазонов.

Пересечение одной строки и столбца

Предположим, есть набор данных, как показано ниже:

Теперь, если вы используете =C2:C13 B5:D5 [Обратите внимание, что между диапазонами есть один пробел, который также является нашим оператором пересечения в Excel], он вернет 523 (значение в ячейке C5), что является пересечением этих двух диапазонов.

Пересечение нескольких строк и столбцов

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

Вот формула, которая может это сделать: =B2:C13 B5:D5

Обратите внимание, что результат этой формулы будет отображать ошибку "Значение", однако, когда вы выберете формулу и нажмете F9, она отобразит результат как . Эта формула возвращает массив значений пересечения.Вы можете использовать это в формулах, таких как SUM (чтобы получить сумму значений пересечения) или MAX (чтобы получить максимальное значение пересечения).

Пересечение именованных диапазонов

Вы также можете использовать именованные диапазоны для поиска пересечения с помощью оператора пересечения в Excel.

Вот пример, где я назвал значения продукта 1 как Prdt1, значения продукта 2 как Prdt2 и значения за апрель как апр.

Теперь вы можете использовать формулу =Prdt1 Apr, чтобы получить пересечение этих двух диапазонов. Точно так же вы можете использовать =Prdt1:Prdt2 Apr, чтобы получить пересечение Product 1, Product 2 и April.

Практический пример использования оператора пересечения в Excel

Вот ситуация, когда этот прием может пригодиться. У меня есть набор данных о торговых представителях и продажах, которые они совершали каждый месяц в 2012 году.

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

Как показано ниже:

Как это создать:

  1. Выделите весь набор данных (B3:N13) и нажмите Control + Shift + F3, чтобы создать именованные диапазоны (это также можно сделать с помощью команды «Формула» -> «Определенные имена» -> «Создать из выделенного»). Откроется диалоговое окно «Создать имена из выделенного».
  2. Выберите параметры «Верхняя строка» и «Левый столбец» и нажмите «ОК».
  3. Это создаст именованные диапазоны для всех торговых представителей и всего месяца.
  4. Теперь перейдите в ячейку B16 и создайте раскрывающийся список для всех торговых представителей.
  5. Аналогичным образом перейдите в ячейку C15 и создайте раскрывающийся список для всех месяцев.
  6. Теперь в ячейке C16 используйте следующую формулу =ДВССЫЛ(B16) ДВССЫЛ(C15)

    7. Как это работает?

    Обратите внимание, что между двумя НЕПРЯМЫМИ формулами есть пробел.

    Функция ДВССЫЛ возвращает диапазон для именованных диапазонов — Торговый представитель и Месяц, а пробел между ними работает как оператор пересечения и возвращает значение пересечения.

    Примечание. Этот невидимый оператор пересечения имеет приоритет над другими операторами. Поэтому, если в этом случае, если вы используете =ДВССЫЛ(B16) ДВССЫЛ(C15)>5000, он вернет ИСТИНА или ЛОЖЬ в зависимости от пересекающегося значения.

    Как и карта, каждая ячейка в Excel имеет ссылку, на которую можно ссылаться в других листах и ​​файлах Excel. Вот как.

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

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

    Перекрестные ссылки на отдельные ячейки в Excel

    Если вы когда-либо использовали карту, вам знакома идея привязки к сетке. Карта разделена на сетку, что позволяет указать конкретное место с помощью отдельной ссылки на сетку.

    Таблица Excel работает аналогичным образом. Электронная таблица в Excel разбита на ячейки, на которые можно ссылаться, используя буквы для столбцов и числа для строк. Ячейка C7, например, находится в ячейке, которая пересекает третий столбец (столбец C) и строку 7.

    Чтобы сослаться на эту ячейку на активном листе, введите =C7 в другой ячейке или используйте формулу, содержащую ссылку на эту ячейку, которая объединяет несколько ссылок на ячейки. Например, =C7*C8 умножает данные из C7 на данные из ячейки C8.

    Если вы хотите сослаться на ячейку из другого рабочего листа, вам нужно поставить имя рабочего листа перед ячейкой, а затем поставить восклицательный знак. Правильный формат для этого: =Лист2!C7.

    Формула =Лист2!C7*Лист2!C8 умножает данные из ячеек C7 и C8 на листе с именем Лист2.

    Вы также можете ссылаться на ячейки из другого файла электронной таблицы Excel.Вам нужно будет использовать квадратные скобки, чтобы идентифицировать файл, назвать рабочий лист, из которого он взят, а затем использовать ссылку на ячейку. Например, =[Spreadsheet.xlsx]Лист2!C7.

    Используя =[Spreadsheet.xlsx]Sheet2!C7*[Spreadsheet.xlsx]Sheet2!C8, вы сможете умножить данные из ячеек C7 и C8 на листе Sheet2 из файла Spreadsheet.xlsx.

    Ссылки на диапазоны ячеек в Excel

    В Excel также можно ссылаться на несколько ячеек, если они следуют последовательно. В некоторых формулах можно использовать ссылки на диапазоны ячеек.

    Например, найти сумму ячеек от C7 до C10 очень просто — просто введите =СУММ(C7:C10), используя двоеточие, чтобы соединить начальную (C7) и конечную (C10) ячейки в диапазоне.

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

    Чтобы сослаться на диапазон ячеек из другого рабочего листа в том же файле, укажите имя рабочего листа перед диапазоном ячеек. Например, =СУММ(Лист2!C7:C10).

    Чтобы сослаться на диапазон ячеек из другого файла, используйте квадратные скобки для имени файла, затем ссылку на рабочий лист и, наконец, диапазон ячеек. Например, =СУММ([Таблица.xlsx]Лист2!A7:A10).

    Ссылки на именованные ячейки в Microsoft Excel

    Microsoft Excel позволяет вам присвоить ячейке заданное имя, заменив ссылку в виде сетки. Для этого выберите свою ячейку, а затем введите для нее имя в текстовом поле рядом с панелью формул.

    Затем вы можете использовать это определенное имя ячейки в качестве ссылки в других ячейках. Например, именованная ячейка с именем namedcell может упоминаться в другой ячейке как =namedcell.

    Эту именованную ссылку на ячейку можно использовать как есть на всем листе. Например, если бы он находился на листе с именем Sheet2, вы все равно использовали бы ссылку =namedcell для ссылки на него.

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

    Если ввести =Spreadsheet.xlsx!namedcell, вместо этого будет указана ячейка с именем namedcell, сохраненная во внешнем файле электронной таблицы Excel с именем Spreadsheet.xlsx.

    Организация данных в Microsoft Excel

    Знание перекрестных ссылок на ячейки в Excel позволяет более эффективно организовывать данные. Вам не нужно работать с одним листом — вы можете использовать данные из одной ячейки в разных листах и ​​файлах электронных таблиц.

    Затем вы можете взять эти данные и использовать их в другом программном обеспечении. Например, вы можете начать встраивать Excel в свои заметки OneNote. Если вы новичок в Excel, не волнуйтесь — есть множество советов по Excel, которые помогут вам быстро освоиться.

    Спасибо за попытку!
    Извините, я должен был загрузить изображение. Мне нужно вычислить тысячи таких строк.

    Фуок

    Известный участник

    mdwgno01

    Новый участник

    Эрик В

    MrExcel MVP

    Вот еще одна версия:

    Формулы ячеек
    ДиапазонФормула
    C2:F2C2=MAX(0,NETWORKDAYS(MAX(DATE(C1,1, 1),$A2),MIN(DATE(C1,12,31),$B2)))
    G2G2=SUM(C2:F2)

    Что касается праздников, в ЧИСТРАБДНИ есть необязательный параметр для праздников. Настройте это так:

    Формулы ячеек
    ДиапазонФормула
    C2:F2C2=MAX(0,NETWORKDAYS(MAX(DATE(C1,1, 1),$A2),MIN(DATE(C1,12,31),$B2),$I$2:$I$20))
    G2 G2=SUM(C2:F2)

    Вам потребуется настроить список для каждого праздника в диапазоне. Есть способы справиться с праздниками, которые всегда выпадают на понедельник, и тому подобными вещами. Дайте нам знать, если вам нужна помощь в этом.

    Похожие темы

    Excel содержит более 450 функций, и с каждым годом их становится все больше. Это огромная цифра, так с чего же начать? Прямо здесь, с этим набором.

    Статистика форума

    Поделиться этой страницей

    Мы обнаружили, что вы используете блокировщик рекламы.

    Какой блокировщик рекламы вы используете?

    Отключить AdBlock

    Следуйте этим простым шагам, чтобы отключить AdBlock

    1)Нажмите значок на панели инструментов браузера.
    2)Нажмите значок на панели инструментов браузера.
    2)Нажмите кнопку «Приостановить на этом сайте».

    Отключить AdBlock Plus

    Следуйте этим простым шагам, чтобы отключить AdBlock Plus

    1)Нажмите значок на панели инструментов браузера.
    2)Нажмите на переключатель, чтобы отключить его для «mrexcel.com».

    Отключить источник uBlock

    Следуйте этим простым шагам, чтобы отключить uBlock Origin

    1)Нажмите значок на панели инструментов браузера.
    2)Нажмите кнопку "Питание".
    3)Нажмите кнопку "Обновить".

    Отключить uBlock

    Следуйте этим простым шагам, чтобы отключить uBlock

    1)Нажмите значок на панели инструментов браузера.
    2)Нажмите кнопку "Питание".
    3)Нажмите кнопку "Обновить".

    Читайте также: