Как найти пересечение данных в таблице

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

Для чего нужен оператор пересечения

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

Стандартное использование оператора пересечения

Для начала давайте разберемся с тем, как просто использовать оператор пересечения и вывести в пустой ячейке то самое искомое значение. Вам понадобится любая таблица, состоящая из нескольких столбцов и строк с заполненными данными. Далее остается выполнить следующие действия:

1. Выделите пустую ячейку для вывода результата, в ней добавьте знак = и выделите левой кнопкой мыши всю строку.

2. После этого поставьте пробел. Этот знак и является оператором пересечения.

3. После пробела укажите диапазон столбца, точно так же выделив его левой кнопкой мыши.

4. Нажмите клавишу Enter, чтобы ознакомиться с результатом. Как видно, оператор узнал, какая ячейка есть как в столбце, так и в строке, отобразив ее значение в клетке.

Использование оператора пересечения в формулах

Описанная выше формула с применением оператора пересечения хоть и кажется интересной, но в чистом виде практически не используется, поскольку и внешне понятно, какое значение есть в столбце и строке. Чаще этот оператор используют вместе с разными функциями при расчетах. Вы можете сделать строку по примеру =СУММ(A3:E3 C1:C5)+500, чтобы оператором пересечения найти ячейку и добавить к ней еще 500. Это довольно простая и стандартная операция. 

Примерно то же самое можно сделать и с функцией ЕСЛИ. Для логического значения используется оператор пересечения и срабатывает определенный результат лжи или истины, который вы настраиваете самостоятельно при помощи аргументов функции.

Дополнительные сведения про оператор пересечения

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

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

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

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

В заключение

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

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

1. Как включить калькулятор в Microsoft Excel.
2. Как работать с шаблонами в Microsoft Excel.
3. Как защитить диапазон, лист и книгу в Microsoft Excel.

I’m trying to find the (set) intersection between two columns in the same table in MySQL. I basically want to find the rows that have either a col1 element that is in the table’s col2, or a col2 element that is in the table’s col1.

Initially I tried:

SELECT * FROM table WHERE col1 IN (SELECT col2 FROM table)

which was syntactically valid, however the run-time is far too high. The number of rows in the table is ~300,000 and the two columns in question are not indexed. I assume the run time is either n^2 or n^3 depending on whether MySQL executes the subquery again for each element of the table or if it stores the result of the subquery temporarily.

Next I thought of taking the union of the two columns and removing distinct elements, because if an element shows up more than once in this union then it must have been present in both columns (assuming both columns contain only distinct elements).

Is there a more elegant (i.e. faster) way to find the set intersection between two columns of the same table?

Для поиска значения на пересечении строки и столбца требуется наличие таблицы специального вида: в строке заголовков и самом левом столбце должны быть неповторяющиеся значения.

Для поиска значения на пересечении строки и столбца требуется 2 критерия: «координаты» по строке и столбцу. Для однозначного поиска требуется, чтобы в строке заголовков и самом левом столбце, по которым будет производиться поиск значений, находились

неповторяющиеся

значения.

Примером такой задачи может служить таблица умножения: первый множитель определяет координату в строке, а второй – в столбце, результат (произведение) – на пересечении.

Другой пример — табель учета рабочего времени: ищется дата (диапазон поиска — строка), затем сотрудник (диапазон поиска — столбец), а на их пересечении – результат (присутствовал на работе, был в отгуле или в отпуске).

Задача

Создадим таблицу продаж машин различных марок по месяцам. Выбирая марку машины и месяц, пользователь получает число проданных машин.

Для поиска значения на пересечении строки и столбца можно использовать разные подходы (см.

файл примера, лист Пример1

).

Поиск с помощью функции ИНДЕКС()

Запишем длинную, но простую для понимания формулу

=ИНДЕКС($B$13:$G$21;ПОИСКПОЗ(D10;$A$13:$A$21;0);ПОИСКПОЗ(E9;$B$12:$G$12;0))

Две функции

ПОИСКПОЗ()

определяют номер строки и столбца для функции

ИНДЕКС()

. Для однозначного поиска номера строки (столбца), в этих диапазонах не должно быть повторов.

Поиск с помощью функции СУММПРОИЗВ()

Другой вариант поиска – использование функции

СУММПРОИЗВ()

=СУММПРОИЗВ((B12:G12=J9)*(A13:A21=I10)*(B13:G21))

Поиск методом пересечения

В EXCEL существует малоизвестный

метод

Пересечений

, основанный на использовании

именованных диапазонов

. Для создания пересечения сделайте следующее:

• выделите диапазон
  
  A7:
  
  G
  
  16
  
  (таблицу продаж вместе с заголовками);
• нажмите кнопку «
  
  Создать из выделенного фрагмента
  
  »
  
  (
  
  );
• убедитесь, что стоят галочки «
  
  В строке выше
  
  » и «
  
  В столбце слева
  
  »;
• нажмите ОК.

Проверить, какие

имена

были созданы, можно через

Диспетчер Имен

(

).

EXCEL создал 15 именованных диапазонов. В качестве имен использованы 6 названий месяцев и 9 марок автомобилей. Теперь произведем, собственно, поиск.

• введите в ячейки
  
  А10
  
  и
  
  B9
  
  два критерия:
  
  Янв
  
  и
  
  Saab
  
  (определим продажи Saab в январе)
• введите формулу
  
  =ДВССЫЛ(A10) ДВССЫЛ(B9)
  
  (между функциями
  
  ДВССЫЛ()
  
  — пробел).
• Нажмите
  
  ENTER
  
  .

При вычислении, вместо формул с

ДВССЫЛ()

будут подставлены, определенные ранее имена:

Янв

и

Saab

, совпадающие с критериями. Наличие пробела означает, что будет использован

метод Пересечений

– будет выведено значение на пересечении соответствующих именам строке и столбцу.

Разберем подробнее. В

Строке формул

выделите

ДВССЫЛ(A10)

и нажмите

клавишу

F9

.

Получим значения соответствующего именованного диапазона

Saab

{

3600

;1520;5480;4588;5336;2588}. Затем выделите

ДВССЫЛ(B9)

. Получим другой массив {4064:1992:812:3185:4617:

3600

:5594:4218:3637}. Метод пересечений вернет значение на пересечении строки и столбца, т.е.

3600

.

Пример 2

Второй пример — это определение зарплаты сотрудника по ведомости (см. файл примера, лист Пример2).

Выбрав Фамилию и Квартал, можно узнать зарплату.

Решение основано на использовании формул, рассмотренных в предыдущем примере.