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