Как называется пересечение строки и столбца в excel
Для поиска значения на пересечении строки и столбца требуется 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:G16 (таблицу продаж вместе с заголовками);
- нажмите кнопку « Создать из выделенного фрагмента » ( Формулы/Определенные имена/ Создать из выделенного фрагмента );
- убедитесь, что стоят галочки « В строке выше » и « В столбце слева »;
- нажмите ОК.
Проверить, какие имена были созданы, можно через Диспетчер Имен ( Формулы/ Определенные имена/ Диспетчер имен ).
EXCEL создал 15 именованных диапазонов. В качестве имен использованы 6 названий месяцев и 9 марок автомобилей. Теперь произведем, собственно, поиск.
- введите в ячейки А10 и B9 два критерия: Янв и Saab (определим продажи Saab в январе)
- введите формулу =ДВССЫЛ(A10) ДВССЫЛ(B9) (между функциями ДВССЫЛ() - пробел).
- Нажмите ENTER .
При вычислении, вместо формул с ДВССЫЛ() будут подставлены, определенные ранее имена: Янв и Saab , совпадающие с критериями. Наличие пробела означает, что будет использован метод Пересечений – будет выведено значение на пересечении соответствующих именам строке и столбцу.
Разберем подробнее. В Строке формул выделите ДВССЫЛ(A10) и нажмите клавишу F9 . Получим значения соответствующего именованного диапазона Saab < 3600 ;1520;5480;4588;5336;2588>. Затем выделите ДВССЫЛ(B9) . Получим другой массив 3600 :5594:4218:3637>. Метод пересечений вернет значение на пересечении строки и столбца, т.е. 3600 .
Пример 2
Второй пример - это определение зарплаты сотрудника по ведомости (см. файл примера, лист Пример2).
Выбрав Фамилию и Квартал, можно узнать зарплату.
Решение основано на использовании формул, рассмотренных в предыдущем примере.
На рис. 121.1 показана таблица с диапазоном ячеек, где отображаются продажи продукции по месяцам. Чтобы извлечь продажи для определенного месяца и продукта, пользователь вводит месяц в ячейку В1 и название продукта в ячейку В2 .
Рис. 121.1. Таблица предоставляет возможность двустороннего поиска
Для упрощения данного процесса таблица применяет именованные диапазоны (табл. 121.1).
Название | Диапазон |
---|---|
Месяц | B1 |
Продукт | B2 |
Таблица | D1:H14 |
Список_месяцев | D1:D14 |
Список_продуктов | D1:H1 |
Следующая формула (в ячейке В4) использует функцию ПОИСКПО3 для возврата позиции месяца в диапазоне Список_месяцев. Например, если месяц — январь, то формула возвращает значение 2, поскольку январь является вторым пунктом диапазона Список_месяцев (первый пункт — пустая ячейка, D1): =ПОИСКПОЗ(Месяц;Список_месяцев;0) .
Формула в ячейке В5 работает аналогично, но использует диапазон Список_продуктов: =ПОИСКПОЗ(Продукт;Список_продуктов;0) .
Окончательная формула в ячейке В6 возвращает соответствующее количество продаж. Она использует функцию ИНДЕКС с результатами из ячеек В4 и В5: =ИНДЕКС(Таблица;B4;B5) .
Вы можете объединить эти формулы в одну, как показано здесь: =ИНДЕКС(Таблица;MATCH(ПОИСКПОЗ;Список_месяцев;0);ПОИСКПОЗ(Продукт;Список_продуктов;0)) .
Использование неявного пересечения
Второй метод выполнения двустороннего поиска чуть проще, но требует создания имени для каждой строки и столбца в таблице. Быстрый способ задать имя каждой строке и столбцу состоит в выборе таблицы и выполнении команды Формулы ► Определенные имена ► Создать из выделенного. В диалоговом окне Создание имен из выделенного диапазона укажите, что имена находятся в верхней строке и левом столбце. Нажмите ОК, и Excel создаст имена. После создания имен вы можете применять простую формулу для выполнения двустороннего поиска, например эту: =Колесо_Июль . Формула, в которой используется оператор пересечения диапазона, возвращает данные о продажах цепных колес за июль.
Сегодня мы узнаем, как используется связь таблиц с помощью комбинирования функций ИНДЕКС и ПОИСКПОЗ, а так же СМЕЩ и ПОИСКПОЗ в Excel. Представим, что имеется некая таблица следующего вида.
Необходимо найти по коду номенклатуры ее название. Все бы ничего, но название номенклатуры располагается слева от колонки с кодами, поэтому применить столь любимую многими функцию ВПР нельзя. Ну не работает она в левую сторону! Как вариант решения можно попробовать скопировать колонку с кодом в начало таблицы. Можно, но во избежание случайного удаления или искажения такие таблицы защищают от редактирования. Другими словами, в них нельзя добавлять столбцы или менять их местами. Как же быть? Выполнять поиск вручную? Вот в таких ситуациях и начинает работать функция ИНДЕКС и СМЕЩ совместно с ПОИСКПОЗ. Рассмотрим эти функции подробнее.
Данная функция позволяет найти порядковый номер элемента в списке. В ней по очереди, разделяя точкой с запятой в русской версии Windows или запятой в русифицированной версии, надо указать такие данные:
- Искомое значение. Это значение, которое мы будем искать в списке. Значение должно быть уникальным, иначе Excel найдет только первое!
- Диапазон списка. Может состоять только из одного столбца или одной сроки. Это важно! Если попробовать выделить две колонки или две строки, программа выдаст ошибку!
- Способ поиска. Тут надо указать вариант, согласно которому приложение выполнит поиск позиции для нашего искомого значения. Тут возможны следующие типы.
1 Будет найдено ближайшее к нашему значение, которое не превышает его, то есть НЕ БОЛЬШЕ нужного. Равным может быть, но не больше. Список должен располагаться ПО ВОЗРАСТАНИЮ.
-1 Excel найдет значение, которое самое близкое к заданному нами, но НЕ МЕНЬШЕ ЕГО. Список должен быть ПО УБЫВАНИЮ.
0 Запустится поиск ТОЧНОГО СООТВЕТСТВИЯ значения из списка заданному нами. Сортировка тут НЕ ТРЕБУЕТСЯ.
Наглядное применение ПОИСКПОЗ.
Посмотрите на скриншот.
Особое внимание обратите вот на что. Во второй и третьей формуле задан тип поиска равный 1 (единице). Однако если в первой из них Excel искал число, которое действительно присутствует в списке, то есть число 50, то он его порядковый номер и указал. А вот числа 68 в списке нет, поэтому он указал порядковый номер значения из списка, которое находится ближе всего к нужному нам, то есть к 68, но его не превышает. А это – число 60.
И еще одна тонкость. Если использовать тип поиска, или, как пишется в справке Excel по этой функции, тип сопоставления, единицу, и в качестве искомого задать значение, которое будет явно больше любого в списке, то Excel покажет номер самого последнего элемента. Таким образом можно узнать, какая строка является последней в таблице. Вот пример:
Замечу, что для текстовых списков надо указывать максимально возможное ТЕКСТОВОЕ значение, например, так: «ЯЯЯ». В случае с числовыми данными указывает максимальное ЧИСЛОВОЕ значение, например, 99999999.
Функция ИНДЕКС в Excel и ее особенности.
Функция ИНДЕКС показывает значение на пересечении заданных строки и столбца в указанной таблице. При ее написании надо последовательно указать таблицу, номер строки в ней и номер столбца в ней. Из пересечения указанных строки и столбца Excel и возьмет нужные нам данные.
Отметим, что функция индекс, включенная в состав другой функции, начинает срабатывать как часть адреса. В следующем примере Excel рассчитывает сумму за первые 6 месяцев. Информацию о номере последнего месяца он берет из ячейки R4, закрашенной зеленым цветом.
Стоит поменять в ней значение, и результат функции СУММ поменяется.
Кроме этого, надо помнить, что кроме варианта работы с единым массивом, существует вариант написания функции ИНДЕКС с выбором для расчета нужной области из указанных. При написании такой функции надо указать
- Области, которые будут обработаны. Их пишут в отдельных скобках, разделяя точкой с запятой или запятой в зависимости от ваших настроек.
- Строка в выбранной области, которая интересует
- Столбец, на пересечении которого с указанной строкой надо взять значение
- Область из перечисленных в начале.
Результат работы может выглядеть так, как на рисунке.
В этом примере одна и та же формула позволили получить данные по сервисному обслуживанию за первый период, то есть за январь, по очереди по трем областям. Однако чаще все-таки используется вариант функции ИНДЕКС, который был рассмотрен в начале.
Функция СМЕЩ в Excel и т онкости ее применения.
. Функция СМЕЩ показывает значение ячейки или диапазона, которые смещен относительно указанного адреса на нужное количество строк и столбцов. Ее синтаксис следующий.
СМЕЩ(1;2;3;4;5)
- Ссылка, от которой отсчитывается перемещение.
- На сколько строк надо переместиться.
- Сколько столбцов надо отсчитать для перемещения.
- Объем строк в диапазоне, на которые перемещаемся. Указывать не обязательно.
- Количество столбцов, на которые перемещаемся. Тоже указывать не обязательно.
Если четвертый и пятый параметр не указан, то программа считает, что нам требуется значение только одной ячейки. На нее мы перемещаемся, когда отсчитываем от указанной первым аргументом ссылки строки и столбцы. Не забываем, что если двигаемся влево или вверх, то смещение по колонкам и строкам соответственно указываем отрицательное. Если же двигаемся вправо или вниз, то значения уже будут положительные.
В примере выше происходит перемещение от ячейки G11 сначала на 2 строчки вверх, затем на 4 колонки влево. В найденной ячейки находится число 10, которое и является результатом работы функции.
Вариант же, когда указывается диапазон, на который переходим, чаще используется как исходные данные для другой функции. В примере ниже с помощью функции СМЕЩ вычисляется диапазон, по которому затем рассчитывается сумма. Если быть честным, то данный вариант на практике бесполезен, проще было бы сразу указать столбец Е для вычисления. Однако он хорошо показывает возможности СМЕЩ.
И еще. Как и в случае с ИНДЕКС, внутри другой формулы результат работы СМЕЩ может использоваться как часть адреса.
Первый вариант создания “левого ВПР”. Комбинируем ИНДЕКС и ПОИСКПОЗ.
Теперь попробуем связать функцию ИНДЕКС и ПОИСКПОЗ. Смысл здесь в том, что количество строк и (или) столбцов для функции ИНДЕКС можно найти с помощью функции ПОИСКПОЗ. В частности, задача, с которой мы начали занятие, может быть решена в два хода. Вначале с помощью ПОИСКПОЗ находим номер нужной строчки, а уже зная ее, переходим в ней с помощью ИНДЕКС в нужную сторону.
Данные формулы конечно можно объединить в одну. Если в последней формуле вместо адреса К8 указать ее содержимое, то есть записать ПОИСКПОЗ(K6;C7:C86;0), то результат работы не изменится. Итоговая формула будет уже такой:
Такую комбинацию ИНДЕКС и ПОИСКПОЗ часто называют ЛЕВЫМ ВПР. Полученная комбинация работает аналогично ВПР, но из-за алгоритма она, во-первых, работает быстрее, а во-вторых, поиск заданного значения не привязан только к первому столбцу, и формула получается более универсальной и гибкой.
Второй вариант создания “левого ВПР”. Комбинируем СМЕЩ и ПОИСКПОЗ.
Аналогично комбинированию ИНДЕКС и ПОИСКПОЗ, мы можем использовать для извлечения нужного значения из таблицы комбинирование СМЕЩ и ПОИСКПОЗ. В полученной составной функции с помощью ПОИСКПОЗ находим перемещение по строкам и столбцам. Расписывать все это по отдельности второй раз уже станем, а сразу покажем формулу.
В следующем примере с помощью сцепления формул СМЕЩ и ПОИСКПОЗ рассчитывается общая стоимость заказа по каждой позиции. При этом цена автоматически изменяется в зависимости от заказанного количества в соответствии с условиями, указанными в заголовке.
Особенности связки СМЕЩ и ПОИСКПОЗ
Сразу хотелось бы отметить два обстоятельства, которые можно заметить в данном практическом примере использования функций. Если рассматривать функцию СМЕЩ, то видно, что при отсутствии перемещения по строкам, как и по столбцам, ноль писать не обязательно. Можно просто указать место для значения, отделив его точкой с запятой. Именно так сделано в данном примере после первого указания ячейки J2 внутри функции СМЕЩ. Что же касается функции ПОИСКПОЗ, то для указания списка вовсе не обязательно указывать диапазон с ним. Список можно указать и внутри функции в фигурных скобках. В свою очередь это снижает время на подготовку к работе.
Давайте теперь подведем итог. Мы научились для связывания таблиц и получения данных из одной из них для вставки в другую использовать функции ИНДЕКС, СМЕЩ, ПОИСКПОЗ, а также различные их комбинации. Полученные формулы позволяют с успехом заменить функцию ВПР, при этом работая более быстро и гибко.
На этом наше занятие можно считать законченным. Попробуйте сами применить полученные знания. Вначале поработайте на учебных материалах, а уже потом и в рабочих документах. Всем удачи и успехов в использовании Excel !
При работе с данными время от времени возникают ситуации, когда нам необходимо найти значение ячейки на пересечении определенных строки и столбца.
В данной статье я Вам расскажу о трёх способах осуществления этой задачи.
1. ИНДЕКС (INDEX)
Данная функция позволяет извлечь значение из диапазона ячеек по номеру строки и столбца.
Аргументы функции выглядят следующим образом:
Массив – ссылка на диапазон ячеек, в котором нам нужно осуществить поиск;
Номер_строки – строка, из которой нужно извлечь значения;
Номер_столбца – столбец, из которого нужно извлечь значение.
Функция ИНДЕКС также может извлекать значение из разных выделенных областей, но в данной статье я не буду останавливаться на этой возможности.
Схематически работу функции можно изобразить так:
Для автоматического поиска строки/столбца функцию ИНДЕКС, как правило, дополняют двумя функциями ПОИСКПОЗ (MATCH).
В таком случае формула выглядит примерно так:
=ИНДЕКС($C$7:$F$9;ПОИСКПОЗ($I$5;$B$7:$B$9;0);ПОИСКПОЗ($I$6;$C$6:$F$6;0))
При смене параметров поиска, значение пересекающейся ячейки будет изменяться автоматически.
2. СУММПРОИЗВ (SUMPRODUCT)
Очень интересная функция, которой можно найти много применений. Она перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.
Для решения нашей задачи формула будет выглядеть так:
=СУММПРОИЗВ((C6:F6=I6)*(B7:B9=I5)*(C7:F9))
Простыми словами синтаксис функции можно представить так:
=СУММПРОИЗВ((условие_1)*(условие_2)*(что_суммировать))
Сумма умножений 0, 1 и значений таблицы извлечёт нам искомое значение.
3. ДВССЫЛ (INDIRECT)
Третий способ, который лично мне очень нравится своей простотой. Единственное условие для его работы – названия в столбцах и строках должны быть без пробелов (их можно удалить или заменить на нижнее подчеркивание «_»).
Функция ДВССЫЛ возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого. Функция ДВССЫЛ используется, если требуется изменить ссылку на ячейку в формуле без изменения самой формулы.
Если на первый взгляд функция выглядит простой и малополезной, то при дальнейшем её изучении Вы узнаете, что с её помощью мы можем перемещаться по листам, транспонировать таблицы, отбирать чётные (нечётные) строки и многое другое. Ведь для нее аргумент Ссылка_на_ячейку – всего лишь текстовая строка, которую можно изменять формулами.
Чтобы найти значение ячейки на пересечении определенных сроки и столбца нам понадобится сразу две функции ДВССЫЛ.
Для начала нам необходимо создать диапазоны для строк и столбцов.
Выделяем столбцы с заголовками и нажимаем комбинацию клавиш Ctrl+Shift+F3
Аналогичную операцию проделываем и для строк.
Сама формула будет иметь такой вид:
=ДВССЫЛ(I5) ДВССЫЛ(I6)
Обратите внимание что функции разделены пробелом.
Excel даёт нам возможность использовать несколько вариантов решения для одной задачи. Там, где невозможно применить один способ, практически всегда есть возможность подобрать другой. Настоятельно рекомендую Вам изучить функции, рассмотренные в данной статье и Ваша работа с данными, станет намного эффективнее.
Награды за освоение Microsoft Excel многочисленны, хотя первые шаги могут быть пугающими. Некоторые могут потеряться в жаргоне электронных таблиц и могут запутаться больше, чем при первом запуске. Давайте рассмотрим некоторые наиболее распространенные термины, с которыми вы столкнетесь как пользователь Excel.
Терминология Microsoft Excel
- Рабочая книга — рабочая книга относится к файлу электронной таблицы Excel. Рабочая книга содержит все введенные вами данные и позволяет сортировать или вычислять результаты. Рабочая книга, доступная для просмотра и редактирования несколькими пользователями в сети, называется общей книгой.
- Рабочий лист — в рабочей книге вы найдете документы, называемые рабочими листами . Также известный как электронные таблицы, вы можете иметь несколько рабочих листов, вложенных в рабочую книгу. Вкладки в нижней части экрана будут указывать, над какими рабочими листами вы сейчас работаете. Это также известно как активный лист или активный лист.
- Ячейка . Ячейка — это прямоугольник или блок, размещенный на листе. Любые данные, которые вы хотите ввести в свой рабочий лист, должны быть помещены в ячейку. Ячейки могут иметь цветовую кодировку, отображать текст, числа и результаты расчетов, исходя из того, что вы хотите достичь. Активная ячейка — это та, которая в данный момент открыта для редактирования.
- Столбцы и строки. Столбцы и строки указывают на то, как выровнены ячейки. Столбцы выровнены по вертикали, а строки — по горизонтали.
- Заголовки столбцов и строк. Эти заголовки представляют собой серые области, обозначенные буквами и пронумерованные, которые находятся за пределами столбцов и строк. При нажатии на заголовок будет выделена вся строка или столбец. Вы также можете изменить высоту строки или ширину столбца, используя заголовки.
- Рабочая область — во многом подобно рабочим листам в рабочей книге, рабочая область позволяет открывать множество файлов одновременно.
- Лента — Над книгой находится раздел командных вкладок, который называется Лента. Множество параметров находятся за каждой вкладкой ленты
- Ссылка на ячейку — ссылка на ячейку — это набор координат, который идентифицирует конкретную ячейку. Это комбинация букв и цифр. Например, A5 будет указывать на ячейку, расположенную в месте пересечения столбца A и строки 5.
- Диапазон ячеек — диапазон ячеек — это совокупность ячеек, которые были определены как группа на основе различных критериев. Используя двоеточие (:) между ссылками на ячейки, Excel может определить диапазон, также известный как массив. Например, диапазон в строке может выглядеть как A1: C1, что говорит формуле, что нужно смотреть на ячейки в ряду между A1 и C1, в то время как B4: D9 сообщает формуле, что нужно смотреть на все ячейки в рамке, ограниченной столбцы B и D и строки 4 и 9. Трехмерная ссылка относится к диапазону, который охватывает более одного листа в одной и той же книге.
- Объединенная ячейка — когда объединяются две или более ячейки, она становится так называемой объединенной ячейкой.
- Шаблон — шаблон представляет собой отформатированную рабочую книгу или рабочий лист, предназначенный для помощи пользователям в выполнении определенных задач в Excel. Примеры этого включают анализ запаса, карту процесса и календарь.
- Оператор — операторы — это символы или знаки, которые указывают, какие вычисления должны быть выполнены в выражении. Операторы не обязательно ссылаются на простые математические типы; операторы сравнения, объединения текста или ссылки также существуют.
- Формула — последовательность внутри ячейки, которая используется для получения значения. Он должен начинаться со знака равенства (=). Это может быть математическое уравнение, ссылки на ячейки, функции или оператор. Формула также известна как выражение.
- Панель формул. Панель формул, расположенная между лентой и книгой, будет отображать содержимое активной ячейки. В случае формул панель формул отобразит все компоненты формулы.
- Функция — функции — это формулы, которые предварительно встроены в Excel. Они предназначены для упрощения потенциально сложных формул на листе.
- Код ошибки — коды ошибок появляются, если Excel обнаруживает проблему с помощью предоставленной формулы.
- Форматирование ячеек — это действие по изменению отображения данных ячейки в электронной таблице. При форматировании ячеек изменяется только внешний вид ячеек; значение в ячейках остается постоянным.
- Условное форматирование. Форматирование применяется только в том случае, если ячейка соответствует определенным критериям, таким как повторяющиеся значения или значения выше или ниже порогового значения.
- Фильтр — Фильтры — это правила, которые вы можете использовать, чтобы решить, какие строки на рабочем листе отображать. Эти фильтры могут использовать такие данные, как условия или значения.
- Стоп-кадр — Стоп-кадр позволяет выбрать определенные столбцы и / или строки, чтобы они оставались видимыми на листе, даже если вы прокручиваете, например, ячейки заголовка, которые помечают столбец.
- Автозаполнение — это позволяет вам легко копировать данные в более чем одну ячейку.
- Автосумма — эта функция сложит числа, которые вы ввели в свой лист, и отобразит общее количество в ячейке по вашему выбору.
- Автоформат — это приложение для автоматического форматирования ячеек, которые соответствуют заранее заданным критериям. Это может быть так же просто, как выравнивание шрифта и размер.
- Проверка данных — эта функция помогает предотвратить ввод неправильных данных на рабочий лист. Это чаще всего используется для создания раскрывающихся списков для общих терминов. Проверка данных способствует согласованности и точности вводимых данных.
- Сводная таблица — это инструмент суммирования данных, наиболее часто используемый для сортировки, усреднения для автоматического суммирования данных. Информация берется из одной таблицы, а результаты отображаются в другой. Сводные таблицы позволяют легко получать конкретную информацию из большого источника данных.
- Сводная диаграмма — этот тип диаграммы предоставляет визуальную помощь для сводных таблиц. Предоставляя графическое представление данных сводной таблицы, пользователь может обеспечить уровень интерактивности с данными.
- Сводная область . Сводная область — это точка на рабочем листе, куда можно перетащить поле «Сводная таблица», чтобы реорганизовать способ отображения отчета.
- Исходные данные — это информация, используемая для создания сводной таблицы. Он может существовать либо внутри рабочего листа, либо из внешней базы данных.
- Область значений — в сводной таблице области значений определяются как ячейки, содержащие сводную информацию.
- Item — это подкатегории полей в вашей сводной таблице. Если у вас есть поле с пометкой «Штат», это могут быть Алабама, Аляска и так далее.
Завершение
Несмотря на то, что они охватывают множество других терминов Microsoft Excel, приведенный выше список поможет вам стать верным игроком в таблице. На какие термины вы наткнулись, когда впервые начали использовать Excel? Есть ли другие термины, которые вы бы предложили для этого списка? Дайте нам знать!
Читайте также: