Как задать координаты ячейки в excel

Обновлено: 04.07.2024

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Описание

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

Создание образца листа

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

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

Определения терминов

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

Определение

Вся таблица подстановки

Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».

Просматриваемый_массив
-или-
Лукуп_вектор

Диапазон ячеек, которые содержат возможные значения подстановки.

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

3 (третий столбец в инфо_таблица)

Ресулт_аррай
-или-
Ресулт_вектор

Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.

Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.

Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение "5" в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Функции

LOOKUP ()

Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.

Ниже приведен пример синтаксиса формулы подСТАНОВКи.

= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

Следующая формула находит возраст Марии на листе "образец".

= ПРОСМОТР (E2; A2: A5; C2: C5)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как "Мария" находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:

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

Ниже приведен пример синтаксиса формулы ВПР :

= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)

Следующая формула находит возраст Марии на листе "образец".

= ВПР (E2; A2: C5; 3; ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется "3" в качестве Колумн_индекс (столбец C). Так как "Мария" находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:

INDEX () и MATCH ()

Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.

Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:

= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

Следующая формула находит возраст Марии на листе "образец".

= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как "Мария" находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

СМЕЩ () и MATCH ()

Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

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

= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

Эта формула находит возраст Марии на листе "образец".

= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как "Мария" находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:

Система адресации ячеек очень похожа на используемую в игре «Морской бой»: колонки (столбцы) обозначаются буквами английского алфавита, а строчки – натуральными числами.

В результате получается пара буква–число, записываемая подряд: A1, F15.

Современные электронные таблицы строятся из листов, имеющих 2 8 (256) ячеек и 2 16 (65536) строчек. Таким образом на листе содержится 2 24 (16777216) ячеек.

Очевидно, что 26 букв английского алфавита не хватает, в связи с чем, начиная с 27 колонки, в начало буквенной части добавляется буква A: AA, AB, AC, AD. AZ. С 53 колонки вместо A используется B: BA, BB, BC. Последняя колонка обозначается IV.

Диапазон ячеек (Range)

A1:A10 – буква не менялась, то есть берем одну колонку A.
В ней – ячейки с 1 по 10.
B1:D10 – колонки B, C и D (три), строки с 1 по 10. Итого: 3*10 = 30 ячеек.
A:A – колонка A целиком (все 65536 ячеек).

Задавать диапазон буксировкой мыши намного проще!

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

Копирование формул

Предположим, что в колонке А содержится цена, а в колонке B – количество. В колонке C мы хотим получить сумму. Для этого надо в ячейке C1 записать формулу =A1*B1, то есть перемножить значения соответствующих ячеек.

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

Так, при вставке скопированной ячейки C1 в C2 мы сместимся на одну строку. В результате числовые составляющие адресов ячеек увеличатся на 1 и из =A1*B1 мы получим =A2*B2.

При её же копировании в C3 они увеличатся уже на 2 (съехали на 2 строки) и из =A1*B1 мы получим =A3*B3 и т.д.

Но, как же быть, если подобная помощь нам только навредит? Например, мы хотим получить сумму ввалюте, курс которой хранится в ячейке D1? В C1 потребуется формула =A1*B1/D1. При копировании она преобразуется в =A2*B2/D2.

Но в ячейке D2 нет никакой информации и произойдет ошибка – деление на 0! Хуже того, там может храниться другое число, а подобные ошибки крайне сложно заметить.

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

Для облегчения труда можно воспользоваться горячей клавишей F4 . Для этого следует установить курсор в любое место адреса и нажать указанную клавишу. Произойдет вставка $ перед обоими координатами. Следующее нажатие оставит $ только перед числом, следующее – только беред буквами, следующее – уберет все доллары из адреса.

Ограничения

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

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

Число колонок, составляющее 256 (2^8), совершенно недопустимо для бумажной таблицы. Но если мы захотим отобразить последовательный календарь, где каждая строка описывает ежедневное состояние одного объекта, то колонок явно не хватит.

Пользуясь знанием, что в году 52 недели и вычтя 104 выходных дня, мы получим 261 рабочий день. Ой, тоже не хватает!

Число строк, составляющее 65536 (2^16), можно представить и на бумаге. Что уж говорить об электронных вариантах.

Так, число жителей в вашем районе или городе в большинстве случаев окажется большим.

Очень многие банки совершают гораздо больше транзакций (финансовых проводок) всего за час.

Однако, не следует забывать, что в повседневной жизни мы сталкиваемся с гораздо меньшими объемами информации, которые, тем не менее, крайне сложно обработать вручную.

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

Начиная с версии Excel-2007, размеры листа таблицы увеличены до 16384*1048576 (2^14*2^20). Всли его заполнить формулами, то открытие, равно как и пересчет будет длиться часами (проверено на Intel i7). Будьте вдумчивы при планировании!

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

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

Некоторые дополнительные пояснения можно получить из Справки (Help) нажав клавишу F1 при открытом диалоге.

Число (Number)

Закладка позволяет указать, как должны показываться числа:

Выравнивание (Alignment)

Параметры размещения текста внутри ячейки:

Выравнивание внутри ячейки по осям облегчает для некоторых случаев считывание. Не стоит менять их без нужды. Во многих ситуациях Excel сам сменит их на наиболее подходящие.
Отступ – расстояние от текста до правой/левой границ ячейки – позволяет создать сложное выравнивание в столбце.
Переносить по словам. Очень важный параметр! При снятой галочке (по умолчанию), текст, не влезающий в ячейку, накладывается на следующие пустые ячейки. Если же встречается заполненная ячейка, текст как бы уходит под неё и мы перестаем его дальше видеть. Это достаточно удобно, так видеть весь текст нужно не всегда, а прочитать его можно в строке редактирования формул, перейдя на эту ячейку.
Если галочку установить, то высота ячейки увеличится настолько, чтобы вместить весь текст. Необходимо при распечатке, но может усложнить чтение строк из-за их разной высоты.
Автоподбор ширины (сжатие, точнее уменьшения кегля, до состояния размещения) уменьшает надписи в ячейках, если их ширина недостаточна. Очень удобно для особых случаев, хотя придает оттенок неряшливости, да и уменьшение может привести к нечитаемому размеру символов.
Объединение ячеек. Дублирует одноименный инструмент панели форматирования.
Ориентация. Поворачивает надпись относительно горизонтальной оси до ±90°. По российским стандартам не допускается поворот по часовой стрелке. (Но если очень надо. )

Шрифт (Font)

Оформление символов в ячейке:

Стандартный диалог форматирования атрибутов текста. Бросается в глаза скудность возможностей по сравнению с Word, но это естественно, так как Excel не является текстовым редактором, о чем многие частенько забывают.
Следует обратить внимание на флажок Обычный (Normal font), сбрасывающий все параметры закладки на стандартные значения. Это намного удобнее, чем менять несколько пунктов, которые надо ещё и помнить.
Требует использования только для присвоения параметров Видоизменения (Effects).

Граница (Border)

Дает возможность очертить рамки вокруг ячеек:

Работа с диалогом требует определенных навыков. Его важность определяется тем, что если на экран выводится сетка, помогающая воспринимать информацию, то при распечатке табличные данные могут стать совершенно нечитабельными. Единственное принципиальное замечание: объектом для присвоения рамки является ячейка. Каждой ячейке могут быть присвоены индивидуалные границы. Но ячейки граничат друг с другом и, по концепции Excel, их граница – общая. (В Word всё совершенно по другому!)

Вид (Patterns)

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

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

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

А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:

Поудобнее, чем линейка, правда?

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

Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец

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

Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:

Измените адрес рабочего диапазона на свой - именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.

Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off - выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.

Плюсы этого способа:

относительная простота реализации
выделение - операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть

Минусы этого способа:

такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки - выделяются сразу все строки и столбцы, входящие в объединение
если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца

Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование

Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA - высший пилотаж ;)

Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке - высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:

кодовое слово для параметра, например "столбец" или "строка"
адрес ячейки, для которой мы хотим определить значение этого параметра

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

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

Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
В Excel 2003 и более старших версиях открываем меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) . В Excel 2007 и новее - жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
Вводим формулу для нашего координатного выделения:

Все почти готово, но остался один нюанс. Дело в том, что Excel не считает изменение выделения изменением данных на листе. И, как следствие, не запускает пересчет формул и перекраску условного форматирования только при изменении положения активной ячейки. Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:

Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.

Плюсы этого способа:

Условное форматирование не нарушает пользовательское форматирование таблицы
Этот вариант выделения корректно работает с объединенными ячейками.
Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
Макросы используются минимально

Минусы этого способа:

Формулу для условного форматирования надо вводить вручную.
Нет быстрого способа включить-выключить такое форматирование - оно включено всегда, пока не будет удалено правило.

Способ 3. Оптимальный. Условное форматирование + макросы

Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.

Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.

Способ 4. Красивый. Надстройка FollowCellPointer

Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:

Красивое решение. Не без глюков местами, но попробовать точно стоит. Качаем архив, распаковываем на диск и устанавливаем надстройку:

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