Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как получить букву строки или столбца текущей ячейки в Excel?

Стили ссылок R1C1 и A1 в Excel

Приветствую всех, дорогие читатели блога TutorExcel. Ru блога.

Адресация к ячейкам листа в Excel является одним из основополагающих аспектов работы в программе, и большинство пользователей программы работают со стилем ячеек вида A1, в котором столбцы задаются буквами, а строки числами (в данном случае A — литера столбца, а 1 — номер строки, соответственно, ячейка — пересечение столбца A и строки 1).
Например, ячейка D5 стоит на пересечении столбца D и строки 5, ячейка F8 на пересечении столбца F и строки 8 и т.д.
Вдобавок в Excel стиль A1 используется по умолчанию, поэтому все привыкли работать именно с ним.

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

Система координат (Excel, шахматы, игра "морской бой")

Однако иногда встречается использование другого стиля — R1C1, где и столбцы и строки задаются числами (R1 означает строку 1, где R — Row (строка в переводе с английского), а C1 означает столбец 1, где C — Column (столбец в переводе с английского), соответственно, ячейка — пересечение строки 1 и столбца 1).

Как включить или отключить стиль ссылок R1C1 в Excel?

Чтобы работать с R1C1, необходимо сначала включить его в Excel, поскольку по умолчанию используется A1. Нажмите Файл -> Параметры, а затем выберите вкладку Формулы. В блоке Работа с формулами установите флажок в поле Стиль ссылки R1C1.

Настройка параметров вида формул

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

Особенности и отличия стилей адресации A1 и R1C1

В первую очередь, при работе с ячейками обратите внимание, что для стиля R1C1 в адресе сначала идет строка, а потом столбец, а для A1 все наоборот — сначала столбец, а потом строка.
Например, ячейка $H$4 будет записана как R4C8 (а не как R8C4), поэтому будьте внимательнее при ручном вводе формул.

Еще одно отличие между A1 и R1C1 — внешний вид окна программы Excel, в котором по-разному обозначаются столбцы на рабочем листе (A, B, C для стиля A1 и 1, 2, 3, … для стиля R1C1) и имя ячейки:

Внешнее отличие рабочего окна

Как известно, в Excel есть 3 типа ссылок (тут можно почитать подробнее): относительные (А1), абсолютные ($А$1) и смешанные ($А1 и А$1), где знак доллара ($) служит закреплением номера строки или столбца.
В случае со стилем R1C1 также можно использовать любой тип ссылки, но принцип их составления будет несколько другим:

  • RC. Относительная ссылка на текущую ячейку;
  • R1C1. Абсолютная ссылка на ячейку на пересечении строки 1 и столбца 1 (аналогично $A$1);
  • RC2. Ссылка на ячейку в столбце 2 текущей строки;
  • R3C. Ссылка на ячейку в строке 3 текущего столбца;
  • RC[4]. Ссылка на ячейку, расположенную в 4 столбцах справа от текущей ячейки;
  • R[-5]C. Ссылка на ячейку, расположенную на 5 строк выше текущей ячейки;
  • R6C[7]. Ссылка на ячейку, расположенную в 6 строках и 7 столбцах справа от текущей ячейки;
  • И т.д.
Читайте так же:
Как подсчитать дни между двумя датами, включая дату начала в Excel?

В целом, использование цифр после символов строки или столбца (например, после букв R или C) для стиля R1C1 кажется аналогичным закреплению строки или столбца (символов $).

Применение квадратных скобок позволяет сделать относительное смещение относительно ячейки, в которой введена формула (к примеру, R[-2]C делает смещение на 2 строки вверх, RC[2] — смещение на 2 столбца вправо и т.д.). Таким образом, смещение вниз или вправо обозначается положительными числами, влево или вверх — отрицательными.

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

Преимущества стиля ссылок R1C1

R1C1 предпочтительнее A1 в двух основных ситуациях: при проверке формул (поиск ошибок) и при написании макросов.

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

Расчет формул. Вариант 1.

Вы сделали ошибку?

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

Теперь посмотрим на ту же таблицу с ячейкой в стиле R1C1:

Расчет формул. Вариант 2.

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

Аналогичный прием можно применить и в случае с работой с ячейками при создании макросов.
В случае, если нам нужно прописать для диапазона ячеек формулы произведения двух столбцов (как в примере выше), то для стиля R1C1 все формулы будут записываться абсолютно одинаково (=RC[-2]*RC[-1]), поэтому в коде макроса можно сразу прописать формулу для всех ячеек диапазона. Для стиля A1, к сожалению, такой способ не подойдет, так как записи всех формул отличаются друг от друга и каждую нужно будет прописывать отдельно, что несколько усложняет задачу.

R1C1 в функциях Excel

У вас не возникнет проблем с изменением стиля с A1 на R1C1, поскольку все ссылки, используемые в качестве аргументов в функциях, автоматически будут отображаться в новом формате.

Однако в Excel есть функции, которые могут использовать оба стиля адресации независимо от режима, установленного в настройках. В частности, функции ИНДИРЕКТ (английская версия) и АДРЕС (английская версия) могут использоваться в обоих режимах.

Пример функций ДВССЫЛ и АДРЕС

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

Читайте так же:
Как подсчитать появление определенного символа в ячейке в Excel?

Спасибо за внимание!
Если у вас есть мысли или вопросы по использованию и преимуществам разных стилей ссылок — пишите в комментариях.

Создание ссылки на ячейку или столбец в формуле

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

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

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

Краткая информация о типах ссылок в формулах

Ниже представлен контрольный список для создания собственных формул.

Элемент, на который нужно сослатьсяТребуемый форматПример
Отдельная ячейкаНазвание столбца, номер строки=Бюджет1
Название столбца содержит пробел или заканчивается на числоЗаключите имя столбца в квадратные скобки.=[Столбец A]1
=DAY([Q1]1)
Абсолютная ссылка (всегда ссылается на указанную ячейку, строку или столбец)Введите символ «$» перед названием столбца, номером строки или обоими.=$[Столбец A]$1
=[Столбец B]$1
=$[Столбец C]1
Несколько несвязанных ячеекСсылки на ячейки должны быть разделены запятыми.=SUM(Бюджет1, Затраты4, [Прогноз прибыли]20)
Диапазон ячеек в одном столбцеВведите первую и последнюю ячейки диапазона через двоеточие «:».=SUM(Бюджет1:Бюджет12)
Столбец целиком (вместе со всеми добавляемыми ячейками)Название столбца через двоеточие «:».=SUM(Бюджет:Бюджет)
Диапазон ячеек в нескольких столбцахВведите ссылку на правую верхнюю и левую нижнюю ячейки через двоеточие «:».=SUM(Январь1:Март5)
Отдельная ячейка, диапазон ячеек или столбцы целиком из другой таблицыВведите название созданной ссылки в другой таблице в фигурных скобках.=COUNT()

Более подробное описание типов ссылок

Ссылка на отдельную ячейку

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

Формула в столбце Carrying Value следующей таблицы Inventory Management умножит цену строки 1 на инвентарные запасы строки 1:

Формула ссылки на отдельную ячейку

Формула 2 вернет значение 2 994,00 р., т.е. общую стоимость:

Результат ссылки на отдельную ячейку

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

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

= [годовой бюджет] 1 + [годовой бюджет] 2

Создание абсолютной ссылки

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

В формуле перед именем столбца или номером строки поставьте знак доллара ($). Следующая формула останется неизменной при перемещении или копировании:

=$[Столбец A]$1 * $[Столбец B]$1

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

=[Столбец A]$1 * [Столбец B]$1

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

=$[Столбец A]1 * $[Столбец B]1

Ссылка на диапазон ячеек в одном столбце

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

Например, формула в нижней части столбца «Балансовая стоимость» в таблице «Управление запасами», приведенной ниже, добавит значения в строках с 1 по 6 столбца:

Сумма значений диапазона

Эта формула вернет значение «40763,75 р», которое является общей стоимостью книги:

Результат суммирования значений диапазона

Ссылка на столбец целиком

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

=SUM([Бюджет Аннуэл] : [Бюджет Аннуэл])

ПРИМЕЧАНИЕ: Если формула введена в ячейку столбца, к которому она относится, ячейка, в которой она содержится, будет проигнорирована. Например, если вы введете формулу, приведенную в примере выше, в таблицу под названием «Годовой бюджет», формула SUM сложит значения всех ячеек, кроме ячейки, в которой введена формула.

Ссылка на диапазон ячеек из нескольких столбцов

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

Например, формула в столбце «Всего запасов» таблицы контроля запасов ниже суммирует значения в строке 1 из столбцов «Запасы A», «Запасы B» и «Запасы C»:

Поиск значения в диапазоне таблицы Excel по столбцам и строкам

Предположим, ваш отчет содержит таблицу с большим количеством данных во многих столбцах. Очень трудно провести визуальный анализ таких таблиц. И одной из задач работы над отчетом является анализ данных с точки зрения заголовков строк и столбцов за определенный месяц. Эта задача кажется очень простой, но она не может быть решена стандартной функцией. Да, вы можете использовать инструмент Главная — Правка — Найти CTRL+F, чтобы вызвать окно поиска в электронной таблице Excel. Или создайте правило условного форматирования для таблицы. Но тогда вы не сможете выполнить дальнейшие вычисления по результатам. Поэтому вам необходимо создать и правильно применить соответствующую формулу.

Поиск значения в массиве Excel

Схема решения проблемы выглядит следующим образом:

  • В ячейку B1 мы вводим интересующие нас данные;
  • В ячейке B2 указывается имя столбца, содержащего значение ячейки B1
  • В ячейке B3 указывается имя строки, содержащей значение ячейки B1.

Фактически, вам нужно найти координаты в Excel. В чем смысл? Часто требуется получить координаты таблицы по значению. Это немного похоже на анализ обратной матрицы. Конкретный пример в нескольких словах выглядит следующим образом. Цель, заданная в цифрах, является начальным значением, нам нужно определить, кто и когда ближе всего к этой цели. Для примера мы используем простую матрицу данных с отчетом о количестве проданных товаров за три квартала, как показано на рисунке ниже. Важно, чтобы все цифры совпадали. Если вы не хотите создавать и заполнять таблицу Excel вручную с нуля, вы можете скачать готовый пример в конце статьи.

Массив данных.

Здесь мы рассмотрим вариант решения различной сложности, а в конце статьи увидим конечный результат.

Поиск значения в столбце Excel

Сначала узнайте, как получить заголовки столбцов таблицы по значению. Для этого выполните следующие действия:

  1. В ячейку B1 введите значение взятое из таблицы 5277 и выделите ее фон синим цветом для читабельности поля ввода (далее будем вводить в ячейку B1 другие числа, чтобы экспериментировать с новыми значениями).
  2. В ячейку C2 вводим формулу для получения заголовка столбца таблицы который содержит это значение:
  3. После ввода формулы для подтверждения нажимаем комбинацию горячих клавиш CTRL+SHIFT+Enter, так как формула должна быть выполнена в массиве. Если все сделано правильно в строке формул по краям появятся фигурные скобки < >.

В ячейке C2 формула возвращает букву D, которая является буквой, соответствующей заголовку столбца листа. Как видите, значение 5277 содержится в ячейке в столбце D. Мы рекомендуем посмотреть формулу, чтобы получить полный адрес фактической ячейки.

Поиск значения в строке Excel

Теперь найдите номер строки для того же значения (5277). Для этого введите следующую формулу в ячейку C3:

Чтобы получить результат, мы вводим формулу, а затем снова нажимаем CTRL+SHIFT+Enter.

Получить номер строки.

Формула вернула число 9 — она нашла заголовок строки рабочего листа по соответствующему значению таблицы. В результате мы имеем полный адрес значения D9.

Как получить заголовок столбца и название строки таблицы

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

  • Для столбца таблицы — Март;
  • Для строки — Хорошо4.

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

  1. Для заголовка столбца. В ячейку D2 введите формулу: На этот раз после ввода формулы для подтверждения жмем как по традиции просто Enter: Для заголовка столбца.
  2. Для строки вводим похожую, но все же немного другую формулу:

Результат — внутренние координаты в таблице по значению — март; товар 4:

Внутренние координаты таблицы.

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

Поиск одинаковых значений в диапазоне Excel

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

Кроме того, для диапазона табличной части создайте правило условного форматирования:

  1. Выделите диапазон B6:J12 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Правила выделения ячеек»-«Равно». Правила выделения ячеек.
  2. В левом поле введите значение $B$1, а из правого выпадающего списка выберите опцию «Светло-красная заливка и темно-красный цвет» и нажмите ОК. Условное форматирование.
  3. В ячейку B1 введите значение 3478 и полюбуйтесь на результат.

Если есть дубликаты названий, как вы видите, формула для названий по горизонтали (слева направо) берет название из первого дубликата. Чтобы вычислить название строки (номер), умножьте номер первого дубликата по вертикали (сверху вниз). Эту проблему можно решить двумя способами:

  1. Получить координаты первого дубликата по горизонтали (с лева на право). Для этого только в ячейке С3 следует изменить формулу на: В результате получаем правильные координаты как для листа, так и для таблицы: Первый по горизонтали.
  2. Получить координаты первого дубликата по вертикали (сверху вниз). Для этого только в ячейке С2 следует изменить формулу на:

Вы можете менять формулы либо одну, либо другую, но не обе сразу. В ячейке C3 мы должны оставить старую формулу:

Первое по вертикали.

Здесь правильно отображаются координаты первого дубликата по вертикали (сверху вниз) — I7 для Лист и Август; Товар2 для Таблица. Оставим этот вариант для последнего примера.

Поиск ближайшего значения в диапазоне Excel

Тем не менее, эта таблица не идеальна. Для ее анализа необходимо точно знать все ее значения. Формула возвращает ошибку, если не может найти в таблице число в ячейке B1 — #NOTE! В идеале формула должна быть такой, чтобы при отсутствии исходного числа в таблице сама подбирала ближайшее значение, которое содержит таблица. Чтобы создать такую программу для анализа таблиц, введите в ячейку F1 новую формулу:

Затем измените ссылку во всех остальных формулах на F1 вместо B1! Также измените ссылку в условном форматировании. Выберите: HOME — Стили — Условное форматирование — Управление правилами — Изменить правило. В этом случае укажите в параметрах F1 вместо B1. Чтобы проверить, работает ли программа, введите в ячейку B1 число, которого нет в таблице, например, 8000: 8000. Это позволит получить окончательный результат:

Поиск ближайшего значения Excel.

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

Пример.

Наша программа Excel нашла самое близкое значение 4965 для исходного значения 5000. Такая программа может быть полезна для автоматического решения различных аналитических задач по бизнес-планированию, постановке целей, поиску рациональных решений и т.д. А полученные строки и столбцы позволяют еще больше расширить вычислительные возможности таких отчетов с помощью новых формул Excel.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector