Как вернуть значение ячейки в Excel

Обновлено: 21.11.2024

Функция CELL — это информационная функция Excel. Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые крайне важно знать аналитику Excel, который будет извлекать информацию о местоположении, содержимом или форматировании ячейки. Функция CELL принимает два аргумента, один из которых определяет тип извлекаемой информации, а другой — ячейку, которую она будет проверять.

Как финансовый аналитик Финансовый аналитик Описание работы Приведенное ниже описание работы финансового аналитика дает типичный пример всех навыков, образования и опыта, необходимых для найма на работу аналитика в банке, учреждении или корпорации. Выполняйте финансовое прогнозирование, отчетность и отслеживание операционных показателей, анализируйте финансовые данные, создавайте финансовые модели. Функция ЯЧЕЙКА полезна, поскольку она может помочь проверить, содержит ли ячейка числовое значение вместо текста, прежде чем мы выполним для нее расчет. Если мы импортируем данные из внешнего источника, мы можем убедиться, что все ячейки с числами используются для расчетов.

Формула

=CELL(info_type, [ссылка])

Функция CELL использует следующие аргументы:

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

Info_type	Описание
Адрес	Он вернет адрес первой ячейки в ссылке в виде текста.
Col	Он вернет номер столбца первой ячейки в ссылке .
Цвет	Он вернет значение 1, если первая ячейка в ссылке отформатирована с использованием цвета для отрицательных значений, или ноль, если нет.< /td>
Contents	Возвращает значение верхней левой ячейки в ссылке. Формулы не возвращаются. Вместо этого возвращается результат формулы.
Имя файла	Возвращается имя файла и полный путь в виде текста. Если рабочий лист, содержащий ссылку, еще не сохранен, возвращается пустая строка.
Format	Возвращается код, соответствующий числу формат ячейки.
Скобки	Вернется 1, если первая ячейка в ссылке отформатирована со скобками, и 0, если нет.
Префикс	Возвращает текстовое значение, соответствующее «префиксу метки» ячейки.
Protect	Возвратит 1, если ячейка заблокирована, иначе 0.
Строка	Вернет номер строки ячейки.
Тип	Возвращает текстовое значение, соответствующее типу данных в ячейке. Это может быть либо «b» для пустого (или пустого); "l" для метки (т.е. текстовой константы) или "v" для значения (для любого другого типа данных).
Ширина	Вернется ширина столбца.

Для формата info_type коды числового формата показаны ниже:

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

Как использовать функцию ЯЧЕЙКА в Excel?

Чтобы понять, как использовать функцию CELL, рассмотрим несколько примеров:

Пример. Поиск определенного значения

Предположим, у нас есть следующие данные о максимумах и минимумах цен на акции. Чтобы получить адрес результата поиска, полученного с помощью функции ИНДЕКС, мы можем использовать функцию ЯЧЕЙКА.

Используемая формула будет следующей:

Функция ИНДЕКС отображает значение ячейки с заданным индексом, но функция под ней фактически возвращает ссылку. Таким образом, заключив ИНДЕКС в функцию АДРЕС, мы можем увидеть адрес ячейки, возвращаемый поиском.

Мы получаем следующий результат:

Что нужно помнить

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сравнить сертификаты по финансам Лучшие сертификаты по финансам Список лучших сертификатов по финансам. Ознакомьтесь с обзором лучших финансовых сертификатов для профессионалов со всего мира, работающих в финансовой сфере.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

При использовании формул поиска в Excel (таких как ВПР, ВПР или ИНДЕКС/ПОИСКПОЗ) цель состоит в том, чтобы найти совпадающее значение и получить это значение (или соответствующее значение в той же строке/столбце) в качестве результата.

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

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

В Excel есть несколько функций, предназначенных именно для этого.

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

Это руководство охватывает:

Поиск и возврат адреса ячейки с помощью функции ADDRESS

Функция АДРЕС в Excel предназначена именно для этого.

Он берет строку и номер столбца и дает вам адрес этой конкретной ячейки.

Ниже приведен синтаксис функции АДРЕС:

номер_строки: номер строки ячейки, для которой требуется адрес ячейки.
column_num: номер столбца ячейки, для которой вы хотите получить адрес
[abs_num]: необязательный аргумент, в котором можно указать, будет ли ссылка на ячейку абсолютной, относительной или смешанной.
[a1]: необязательный аргумент, в котором можно указать, хотите ли вы использовать ссылку в стиле R1C1 или стиле A1.
[sheet_text]: необязательный аргумент, в котором вы можете указать, хотите ли вы добавить имя листа вместе с адресом ячейки или нет.

Теперь давайте рассмотрим пример и посмотрим, как это работает.

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

Ниже приведена формула, которая это сделает:

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

А поскольку отдел находится в столбце C, я использовал 3 в качестве второго аргумента.

Эта формула прекрасно работает, но у нее есть один недостаток: она не будет работать, если вы добавите строку над набором данных или столбец слева от набора данных.

Это связано с тем, что когда я указываю второму аргументу (номер столбца) значение 3, оно жестко закодировано и не изменится.

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

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

Но если вам нужно, чтобы это было более надежным, используйте вариант, описанный в следующем разделе.

Поиск и возврат адреса ячейки с помощью функции CELL

Хотя функция АДРЕС была создана специально для предоставления ссылки на ячейку с указанным номером строки и столбца, существует и другая функция, которая делает то же самое.

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

Ниже приведен синтаксис функции ЯЧЕЙКА:

info_type: информация о нужной ячейке. Это может быть адрес, номер столбца, имя файла и т. д.
[ссылка]: необязательный аргумент, в котором вы можете указать ссылку на ячейку, для которой вам нужна информация о ячейке.

Теперь давайте рассмотрим пример, в котором вы можете использовать эту функцию для поиска и получения ссылки на ячейку.

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

Ниже приведена формула, которая это сделает:

Приведенная выше формула довольно проста.

Я использовал формулу ИНДЕКС в качестве второго аргумента, чтобы получить отдел для идентификатора сотрудника KR256.

А затем просто завернул его в функцию CELL и попросил вернуть адрес ячейки этого значения, которое я получаю из формулы INDEX.

Теперь вот секрет того, почему это работает: формула ИНДЕКС возвращает искомое значение, когда вы даете ей все необходимые аргументы. Но в то же время он также вернет ссылку на результирующую ячейку.

В нашем примере формула ИНДЕКС возвращает "Продажи" в качестве результирующего значения, но в то же время вы также можете использовать ее для получения ссылки на ячейку этого значения вместо самого значения.

Обычно, когда вы вводите формулу ИНДЕКС в ячейку, она возвращает значение, потому что это то, что от нее ожидается. Но в случаях, когда требуется ссылка на ячейку, формула ИНДЕКС даст вам ссылку на ячейку.

В этом примере это именно то, что он делает.

Лучшее в использовании этой формулы то, что она не привязана к первой ячейке листа. Это означает, что вы можете выбрать любой набор данных (который может находиться в любом месте рабочего листа), использовать формулу ИНДЕКС для обычного поиска, и он все равно даст вам правильный адрес.

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

Итак, это две простые формулы, которые можно использовать для поиска и возврата адреса ячейки вместо значения в Excel.

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

Вернуть адрес ячейки из результата поиска

Обычно вы можете использовать функцию ИНДЕКС/ПОИСКПОЗ для поиска значения в заданном диапазоне ячеек и возврата значения ячейки. Если вы хотите вернуть адрес ячейки вместо значения ячейки в своей формуле, как это сделать. В этом случае вы можете использовать другой вызов функции ЯЧЕЙКА в сочетании с ИНДЕКС/ПОИСКПОЗ, чтобы создать новую формулу для поиска значения и возврата адреса ячейки.

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

Введите эту формулу в пустую ячейку и нажмите клавишу Enter на клавиатуре.

Связанные функции

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

Предположим, что у вас есть задача преобразовать полное название штата в аббревиатуры в MS Excel, и для выполнения этой задачи вы можете сделать это вручную, что является приемлемым способом, только если у вас нет .

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

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

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

Функция Excel XLOOKUP была добавлена в Excel в качестве бета-функции в августе 2019 г. и теперь доступна исключительно в Microsoft 365 (по состоянию на июль 2021 г.). Однако если вы попадаете в эту категорию и часто имеете дело с большими наборами файлов .

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

В этом посте рассказывается, как извлекать уникальные элементы из заданного списка в Microsoft Excel. Как создать новую формулу для получения уникальных значений из ячеек диапазона в Excel. Уникальный список элементов — это .

Благодаря мощным функциям Excel ЕСЛИ, ИНДЕКС и ПОИСКПОЗ мы можем найти именно то, что вы ищете, всего несколькими щелчками мыши. Это пошаговое руководство покажет, как легко извлекать данные с помощью этих и других инструментов! .

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

5 способов проверить, равна ли одна ячейка другой, и вернуть их в другую ячейку

Скачать учебное пособие

1. Использование функции ЕСЛИ

ЕСЛИ — это одна из самых простых функций, которая используется для логического сравнения двух значений. В этом методе мы увидим, как использовать функцию ЕСЛИ для сравнения одной ячейки с другой и возврата другого значения ячейки. Прежде чем перейти к примеру, давайте узнаем больше об этой функции. Синтаксис функции такой:

В первой части параметра нам нужно передать наше условие, на основе которого мы собираемся сравнивать. Затем вторая и третья часть определяют, что будет, если значения после сравнения получатся True или False.

Предположим, что у нас есть набор данных фруктов с двумя столбцами. Каждая строка имеет определенное значение. Теперь мы найдем строки, в которых совпадают Fruit 1 и Fruit 2, и отобразим их значения в столбце Matched Values.

Шаг 1. Введите приведенную ниже формулу в ячейку D4.

Разбивка формулы

Во-первых, используя условие B4=C4, мы сравниваем название фруктов каждого столбца Fruits 1 и Fruits 2 . Если условие принимает значение True, оно печатает значения из столбца «Значение» в столбец «Совпавшие значения».

Шаг 2. Скопируйте формулу до D8.

2. Использование функции ЕСЛИ и формулы

В этом методе мы будем использовать ту же функцию ЕСЛИ, но в зависимости от условия будем использовать формулу и показывать их в другой ячейке. Давайте подумаем о том же наборе данных, который использовался в предыдущем методе, но здесь я обновлю новую цену, если значение флага не равно «X», и наша новая цена будет в 2 раза выше текущей цены.

Шаг 1. Введите следующую формулу в ячейку E4 и нажмите клавишу ВВОД.

Разбивка формулы

В этой формуле с помощью D4<>"X" мы проверяем, не равно ли значение флага "X" или нет. Если условие верно, то цена удвоится, в противном случае она останется прежней.

Шаг 2. Скопируйте формулу до E8.

3. Использование функции ПРОСМОТР

Что касается поиска чего-либо в Excel, функция ПРОСМОТР будет правильным выбором для этого. Эта функция позволяет нам искать что-то по вертикали или горизонтали в рамках условия в определенном диапазоне. Для этих конкретных целей в Excel есть функции ВПР и ГПР. Давайте посмотрим на основы функции ВПР. Синтаксис функции такой:

Во-первых, значение -> содержит значение, которое нужно искать в первом столбце таблицы.

table -> Здесь будет имя таблицы.

col_index -> Это значение индекса столбца таблицы, откуда мы будем собирать значение.

[range_lookup] -> Этот последний раздел предназначен для обозначения необязательного диапазона.

Например, рассмотрим набор данных о некоторых фруктах, как раньше. Но здесь у нас будет 3 столбца: Fruits, ID, Price. Теперь будем искать цену Фруктов из этой таблицы с помощью ВПР.

Шаг 1. Введите формулу в ячейку G4.

Разбивка формулы

Здесь в функции я сначала передал значение, представляющее собой ячейку G3, затем таблицу, из которой мы хотим извлечь данные, которая указана диапазоном B3: D8 всей таблицы. После этого в третьей части мы получим значения из столбца «Цена», который является столбцом № 3, поэтому нам нужно передать 3. Наконец, 0 используется для указания того, что мы хотим точного совпадения.

Шаг 2. Вы можете узнать цену любого другого фрукта, введя название в ячейку G3.

Теперь мы увидим использование функций HLOOKUP, если наши данные спроектированы горизонтально. Синтаксис функций ГПР:

Это почти как функция ВПР. Единственное отличие состоит в том, что вместо индекса столбца здесь используется индекс строки в 3-й части параметра.

Шаг 1. Введите формулу в ячейку B8.

Разбивка формулы

Как мы уже говорили ранее, это почти как функция ВПР. Здесь я передал значение по строкам, а не по столбцам. Вот почему сначала ввели индекс строки нашего желаемого значения, которое равно B7. Кроме того, диапазон таблицы также меняется, так как наша таблица смещается по горизонтали.

4. Использование функций ИНДЕКС и ПОИСКПОЗ

В этом разделе мы будем делать то же самое, что и функция ПРОСМОТР, но с той лишь разницей, что здесь мы не будем использовать функцию ПРОСМОТР. Функции ИНДЕКС и ПОИСКПОЗ делают то же самое, что и ПРОСМОТР. Кроме того, набор данных будет таким же. Прежде чем перейти к примеру, давайте подробно рассмотрим эти две функции.

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

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

1 -> При объявлении 1 будет найдено наибольшее значение, меньшее или равное искомому значению.

0 -> Если мы поместим 0 в качестве типа соответствия, оно будет соответствовать значению, которое точно соответствует значению поиска.

-1 -> Это будет соответствовать наименьшему значению, большему или равному значению поиска.

Шаг 1. Введите формулу в ячейку G4.

Разбивка формулы

В этой части мы попытаемся сопоставить значение, которое находится в ячейке G3, в диапазоне от B3 до B8 в нашей таблице поиска. А так как мы рассматривали точное совпадение, то в последнем аргументе присваивается 0.

Внешней функцией является функция ИНДЕКС. В первой части я назначил диапазон ячеек. Затем совпадающее значение будет рассчитано с помощью функции ПОИСКПОЗ. Наконец, используется 3, так как мы хотим получить данные из третьего столбца нашей таблицы поиска.

5. Вернуть элементы из другого рабочего листа, если есть совпадение

Давайте создадим два рабочих листа: один — еженедельные приемы пищи, а другой — ингредиенты. Теперь я покажу, как сравнивать блюда и показывать ингредиенты на первом листе. Рабочий лист «Планирование питания на неделю» будет выглядеть следующим образом:

И рабочий лист ингредиентов блюд будет таким:

Теперь я покажу, как найти пищевые ингредиенты из листа ингредиентов в листе еды, введя название продукта в ячейку A12.

Шаг 1. Введите формулу в ячейку B12.

Разбивка формулы

Сначала мы передали номер ячейки значения поиска, который равен $A12, затем был отправлен диапазон таблицы других рабочих листов (рабочий лист ингредиентов) $A3:D14. После того, как функция COLUMN передана, чтобы получить значение столбца этой строки. Наконец, FALSE используется для поиска точного совпадения, что означает, что он чувствителен к регистру, и при объявлении false он будет искать точное соответствие значения.

Шаг 2. Скопируйте формулу справа. Затем отобразятся все ингредиенты выбранной еды.

Шаг 3. Вы можете проверить это, введя название любого продукта в ячейку A12 и нажав Enter.

Точно так же, если просто ввести любой продукт питания в поле «Название продукта», будут показаны все ингредиенты этого выбранного продукта из другого листа.

Заключение

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

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