Значение в 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).
Выбрав Фамилию и Квартал, можно узнать зарплату.
Решение основано на использовании формул, рассмотренных в предыдущем примере.
1. Работа с таблицами
Таблица — список, перечень сведений, числовых данных, приведённых в определённую систему и разнесённых по графам; сводка, ведомость.
Строение любой таблицы выглядит следующим образом.
Элемент таблицы на пересечении строки и столбца называется ячейкой . В ячейках таблиц могут быть размещены данные различных типов (текст, числа или изображения).
Таблицы используются при создании текстовых документов, содержащих большое количество однотипных названий (например, расписание уроков), числовых данных (например, таблица умножения) или изображений с текстовой подписью (например, алфавит в букваре).
3. MS Excel
Для автоматизации обработки данных, представленных в табличной форме, используются специальные программы, называемые электронными таблицами или табличными процессорами .
Мы познакомимся с табличным процессором Microsoft Excel .
Окно табличного процессора Excel содержит стандартную строку заголовка, строку меню и панели инструментов. В основном мы будем использовать панели инструментов Стандартная и Форматирование.
Документ в Excel называется рабочей книгой , которая в свою очередь делится на рабочие листы . Между рабочими листами можно переключаться внизу окна.
Основная часть окна табличного процессора — рабочий лист . Рабочий лист состоит из \(256\) столбцов и \(65 536\) строк. Столбцы именуются латинскими буквами (одно- и двухбуквенными именами) в алфавитном порядке в направлении слева направо. Строки нумеруются сверху вниз, начиная с \(1\).
Ячейка — это место пересечения столбца и строки.
Имя ячейки составляется из буквенного обозначения столбца и номера строки.
На рисунке выделена ячейка А1 . Она имеет такое имя, потому что находится в столбце А и первой строке.
Адрес ячейки — имя ячейки.
Диапазон — идущие подряд ячейки в строке или прямоугольнике.
Диапазон задаётся следующим образом: D4:D15, F6:F18, B2:K10 . Указывается начальная и конечная ячейки диапазона, в прямоугольном диапазоне — ячейки левого верхнего и правого нижнего углов.
Табличный курсор — элемент таблицы, который показывает на текущую или активную ячейку.
Активная ячейка — это ячейка электронной таблицы, которая используется в данный момент или готова к вводу или редактированию содержащихся в ней данных.
Обрати внимание!
В ячейках может содержаться текст, число либо формула.
Текст используется для оформления таблиц, числами задаются количественные характеристики, с помощью формул производят математические вычисления.
Формулы являются своеобразными инструкциями, определяющими порядок вычислительных действий. Они могут содержать имена ячеек, числа, знаки операций и обращения к функциям.
Арифметические операции «сложение», «вычитание», «умножение» и «деление» обозначаются соответственно символами «+», «-», «*» и «/».
При решении задач мы будем использовать функции суммирования (СУММ), вычисления среднего арифметического значения (СРЗНАЧ), нахождения максимума (МАКС) и минимума (МИН).
При вычислении по формуле используется порядок операций, принятый в математике.
Для его изменения можно применять круглые скобки.
Изменение содержимого любой ячейки приводит к автоматическому пересчёту значений всех ячеек таблицы, в которых есть ссылки на данную.
Электронные таблицы не только автоматизируют расчёты, но и являются эффективным средством моделирования различных вариантов и ситуаций.
Меняя значения исходных данных, можно проследить за изменением получаемых результатов и из множества вариантов решения задачи выбрать наиболее подходящий.
Что находится на пересечении столбца и строки
Основные понятия электронных таблиц
Документ Excel называется рабочей книгой. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц. В окне документа в программе Excel отображается только текущий рабочий лист, с которым и ведется работа. Каждый рабочий лист имеет название, которое отображается на ярлычке листа, отображаемом в его нижней части. С помощью ярлычков можно переключаться к другим рабочим листам, входящим в ту же самую рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щелкнуть на его ярлычке. Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от А до IV. Строки последовательно нумеруются цифрами, от 1 до 65 536 (максимально допустимый номер строки).
Ячейки и их адресация. На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами для хранения данных. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (в этом порядке), на Пересечении которых она расположена, например: А1 или DE234. Обозначение ячейки (ее номер) выполняет функции ее адреса. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. Одна из ячеек всегда является активной и выделяется рамкой активной ячейки. Эта рамка в программе Excel nrpsieT роль курсора. Операции ввода и редактирования всегда производятся в активной ячейке. Переместить рамку активной ячейки можно с помощью курсорных клавиш или указателя мыши.
Диапазон ячеек. На данные, расположенные в соседних ячейках, можно ссылаться в формулах как на единое целое. Такую группу ячеек называют диапазоном. Наиболее часто используют прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и группы последовательно идущих столбцов. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например: А1 :С15. Если требуется выделить прямоугольный диапазон ячеек, это можно сделать протягиванием указателя от одной угловой ячейки до противоположной по диагонали. Рамка текущей ячейки при этом расширяется, охватывая весь выбранный диапазон. Чтобы выбрать столбец или строку целиком, следует щелкнуть на заголовке столбца (строки). Протягиванием указателя по заголовкам можно выбрать несколько идущих подряд столбцов или строк.