Функция Excel CELL с примерами формул
В учебнике показано, как использовать функцию ЯЧЕЙКА в Excel для получения различной информации о ячейке, такой как адрес ячейки, содержимое, форматирование, местоположение и многое другое.
Как вы обычно получаете конкретную информацию о ячейке в Excel? Кто-то проверит визуально своими глазами, кто-то воспользуется ленточными вариантами. Но более быстрый и надежный способ — использовать функцию Excel CELL. Помимо прочего, он может сообщить вам, защищена ли ячейка или нет, указать числовой формат и ширину столбца, показать полный путь к рабочей книге, содержащей ячейку, и многое другое.
Функция Excel CELL — синтаксис и основные способы использования
Функция ЯЧЕЙКА в Excel возвращает различную информацию о ячейке, такую как содержимое ячейки, форматирование, местоположение и т. д.
Синтаксис функции ЯЧЕЙКА следующий:
ЯЧЕЙКА(информация_тип, [reference])
Где:
- info_type (обязательно) — тип возвращаемой информации о ячейке.
- ссылка (необязательно) — ячейка, для которой нужно получить информацию. Обычно этот аргумент представляет собой одну ячейку. Если указано в виде диапазона ячеек, формула возвращает информацию о верхней левой ячейке диапазона. Если этот параметр опущен, возвращается информация о последней измененной ячейке на листе.
Значения типа информации
В следующей таблице показаны все возможные значения для info_type аргумент, принимаемый функцией ЯЧЕЙКА Excel.
Info_type Описание “address” Адрес ячейки, возвращаемый в виде текста. “col” Номер столбца ячейки. “цвет” Число 1, если ячейка отформатирована по цвету для отрицательных значений; в противном случае 0 (ноль). “contents” Значение ячейки. Если ячейка содержит формулу, возвращается ее вычисленное значение. “имя файла” Имя файла и полный путь к книге, содержащей ячейку, возвращенные в виде текста. Если рабочая книга, содержащая ячейку, еще не сохранена, возвращается пустая строка (“”). «формат» Специальный код, соответствующий числовому формату ячейки. Для получения дополнительной информации см. Коды форматов. “круглые скобки” Число 1, если ячейка отформатирована со скобками для положительных или всех значений; в противном случае 0. “префикс” Одно из следующих значений в зависимости от того, как текст выравнивается в ячейке:
- одинарная кавычка (‘) для текста, выровненного по левому краю
- двойная кавычка (“”) для выровненного по правому краю текста
- каретка (^) для выравнивания текста по центру
- обратная косая черта (\) для текста, выровненного по заливке
- пустая строка (“”) для всего остального
Для числовых значений возвращается пустая строка (пустая ячейка) независимо от выравнивания.
“защитить” Цифра 1, если ячейка заблокирована; 0, если ячейка не заблокирована.
Обратите внимание, что «заблокировано» — это не то же самое, что «защищено». Заблокировано атрибут предварительно выбран для всех ячеек в Excel по умолчанию. Чтобы защитить ячейку от редактирования или удаления, необходимо защитить рабочий лист.
“row” Номер строки ячейки. “type” Одно из следующих текстовых значений, соответствующих типу данных в ячейке:
- “b” (пробел) для пустой ячейки
- “l” (метка) для текстовой константы
- “v” (значение) для всего остального
“width” Ширина столбца ячейки, округленная до ближайшего целого числа. Дополнительные сведения о единицах ширины см. в разделе Ширина столбца Excel.
Заметки:
- Все info_types извлекают информацию о первой (верхней левой) ячейке в ссылка аргумент.
- Значения «имя файла», «формат», «круглые скобки», «префикс», «защита» и «ширина» не поддерживаются в Excel Online, Excel Mobile и Excel Starter.
В качестве примера воспользуемся функцией Excel CELL для возврата различных свойств ячейки A2, содержащей текстовое значение в общем формате:
A B C D 1 Формула данных Описание результата 2 Apple =CELL(“address”, $A$2) $A$2 Адрес ячейки как абсолютная ссылка 3 =CELL(“col”, $A$2) 1 Column 1 4 =CELL(“color” , $A$2) 0 Ячейка не отформатирована цветом 5 =ЯЧЕЙКА(“содержимое”, $A$2) Значение ячейки Apple 6 =ЯЧЕЙКА(“формат”,$A$2) G Общий формат 7 =ЯЧЕЙКА(“круглые скобки”, $A$2) 0 Ячейка не отформатирована со скобками 8 =ЯЧЕЙКА(“префикс”, $A$2) ^ Текст по центру 9 =ЯЧЕЙКА(“защита”, $A$2) 1 Ячейка заблокирована (состояние по умолчанию) 10 =CELL(“row”, $A$2) 2 Row 2 11 =CELL(“type”, $A$2) l Текстовая константа 12 =CELL(“width”, $A$2) 3 Ширина столбца, округленная до целого числа
На снимке экрана показаны результаты другой формулы Excel CELL, которая возвращает различную информацию о ячейке A2 на основе info_type значение в столбце B. Для этого мы вводим следующую формулу в C2, а затем перетаскиваем ее вниз, чтобы скопировать формулу в другие ячейки:
=ЯЧЕЙКА(B2, $A$2)
С информацией, которую вы уже знаете, у вас не должно возникнуть трудностей с интерпретацией результатов формулы, может быть, кроме типа формата. И это прекрасно подводит нас к следующему разделу нашего руководства.
Коды форматов
В таблице ниже перечислены наиболее типичные значения, которые могут быть возвращены формулой CELL с info_type аргумент установлен в «формат».
Формат Возвращаемое значение Общий G 0 F0 0.00 F2 #,##0 ,0 #,##0.00 ,2 Валюта без десятичных знаков
$#,##0 или $#,##0_);($#,##0)
C0 Валюта с двумя десятичными знаками
$#,##0.00 или $#,##0.00_);($#,##0.00)
C2 Процент без десятичных знаков
0%
P0 Процент с 2 знаками после запятой
0,00%
P2 Научное обозначение
0.00E+00
S2 Фракция
# ?/? или же # ??/?? G м/д/гг или м/д/гг ч:мм или мм/дд/гг D4 д-ммм-гг или дд-ммм-гг D1 д-ммм или дд-ммм D2 ммм-гг D3 мм/дд D5 ч:мм AM/PM D7 ч:мм:сс AM/PM D6 ч:мм D9 ч:мм:сс D8
Для пользовательских числовых форматов Excel функция ЯЧЕЙКА может возвращать другие значения, и следующие советы помогут вам их интерпретировать:
- Буква обычно является первой буквой в названии формата, например, «G» означает «Общий», «C» означает «Валюта», «P» означает «Процент», «S» означает «Научный» и «D» для «Свидания».
- В числах, валютах и процентах цифра указывает количество отображаемых десятичных разрядов. Например, если в пользовательском числовом формате отображаются 3 десятичных знака, например 0.###, функция ЯЧЕЙКА возвращает «F3».
- Запятая (,) добавляется к началу возвращаемого значения, если числовой формат содержит разделитель тысяч. Например, для формата #,###.#### формула ЯЧЕЙКИ возвращает «,4», указывая, что ячейка отформатирована как число с 4 десятичными знаками и разделителем тысяч.
- Знак минус (-) добавляется в конец возвращаемого значения, если ячейка отформатирована в цвете для отрицательных значений.
- Скобки () добавляются в конец возвращаемого значения, если ячейка отформатирована со скобками для положительных или всех значений.
Чтобы лучше понять коды форматов, взгляните на результаты следующей формулы, которая скопирована в столбец D:
=ЯЧЕЙКА(“формат”,B3)
Примечание. Если позже вы примените другой формат к ячейке, на которую указывает ссылка, вам придется пересчитать рабочий лист, чтобы обновить результат формулы CELL. Чтобы пересчитать активный лист, нажмите Shift + F9 или используйте любой другой метод, описанный в разделе Как пересчитать листы Excel.
Как использовать функцию ЯЧЕЙКА в Excel – примеры формул
Со встроенными info_types функция CELL может возвращать всего 12 различных параметров о ячейке. В сочетании с другими функциями Excel он способен на гораздо большее. В следующих примерах демонстрируются некоторые дополнительные возможности.
Получить адрес результата поиска
Чтобы найти определенное значение в одном столбце и вернуть соответствующее значение из другого столбца, вы обычно используете функцию ВПР или более мощную комбинацию ПОИСКПОЗ ИНДЕКС. Если вы также хотите узнать адрес возвращаемого значения, поместите формулу Index/Match в поле ссылка аргумент CELL, как показано ниже:
ЯЧЕЙКА(“адрес”, ИНДЕКС (return_columnСООТВЕТСТВИЕ (искомое_значение, lookup_column0)))
С искомым значением в E2, диапазоном поиска A2:A7 и диапазоном возврата B2:B7 реальная формула выглядит следующим образом:
=ЯЧЕЙКА(“адрес”, ИНДЕКС(B2:B7, ПОИСКПОЗ(E1,A2:A7,0)))
И возвращает абсолютную ссылку на ячейку результата поиска:
Обратите внимание, что встроенная функция ВПР не будет работать, поскольку она возвращает значение ячейки, а не ссылку. Функция ИНДЕКС также обычно отображает значение ячейки, но возвращает ссылку на ячейку внизу, которую функция ЯЧЕЙКА может понять и обработать.
Сделать гиперссылку на результат поиска (первое совпадение)
Если вы хотите не только получить адрес первого совпадения, но и перейти к нему, создайте гиперссылку на результат поиска, используя эту общую формулу:
ГИПЕРССЫЛКА(“#”&ЯЧЕЙКА(“адрес”, ИНДЕКС (return_columnСООТВЕТСТВИЕ (искомое_значение, lookup_column0))), ссылка_имя)
В этой формуле мы снова используем классическую комбинацию Индекс/Соответствие для получения первого совпадающего значения и функцию ЯЧЕЙКА для извлечения его адреса. Затем мы объединяем адрес с символом «#», чтобы сообщить ГИПЕРССЫЛКЕ, что целевая ячейка находится на текущем листе.
Для нашего примера набора данных мы используем ту же формулу Index/Match, что и в предыдущем примере, и нам нужно только добавить желаемое имя ссылки, например, это:
=ГИПЕРССЫЛКА(“#”&ЯЧЕЙКА(“адрес”, ИНДЕКС(B2:B7, ПОИСКПОЗ(E1,A2:A7,0))), “Перейти к результату поиска”)
Вместо создания гиперссылки в отдельной ячейке вы можете превратить адрес в интерактивную ссылку. Для этого вставьте ту же формулу CELL(“address”, INDEX(…,MATCH()) в последний аргумент HYPERLINK:
=ГИПЕРССЫЛКА(“#”&ЯЧЕЙКА(“адрес”, ИНДЕКС(B2:B7, ПОИСКПОЗ(E1,A2:A7,0))), ЯЧЕЙКА(“адрес”, ИНДЕКС(B2:B7, ПОИСКПОЗ(E1,A2:A7) ,0))))
И убедитесь, что эта длинная формула дает лаконичный и явный результат:
Получить разные части пути к файлу
Чтобы вернуть полный путь к книге, содержащей ячейку, на которую указывает ссылка, используйте простую формулу Excel CELL с «имя файла» в аргументе info_type:
=ЯЧЕЙКА(“имя файла”)
Это вернет путь к файлу в следующем формате: Диск:\путь\[workbook.xlsx]лист
Чтобы вернуть только определенную часть пути, используйте функцию ПОИСК, чтобы определить начальную позицию, и одну из текстовых функций, таких как ВЛЕВО, ВПРАВО и СРЕДНЯЯ, чтобы извлечь требуемую часть.
Примечание. Все приведенные ниже формулы возвращают адрес текущей рабочей книги и рабочего листа, т. е. листа, на котором находится формула.
Название книги
Чтобы вывести только имя файла, используйте следующую формулу:
=СРЕДН(ЯЧЕЙКА(“имя файла”), ПОИСК(“[“, CELL(“filename”))+1, SEARCH(“]”, ЯЧЕЙКА(“имя файла”)) – ПОИСК(“[“, CELL(“filename”))-1)
How the formula works:
The file name returned by the Excel CELL function is enclosed in square brackets, and you use the MID function to extract it.
The starting point is the position of the opening square bracket plus 1: SEARCH (“[“,CELL(“filename”))+1.
The number of characters to extract corresponds to the number of characters between the opening and closing brackets, which is calculated with this formula: SEARCH(“]”, ЯЧЕЙКА(“имя файла”)) – ПОИСК(“[“, CELL(“filename”))-1
Worksheet name
To return the sheet name, use one of the following formulas:
=RIGHT(CELL(“filename”), LEN(CELL(“filename”)) – SEARCH(“]”, ЯЧЕЙКА(“имя файла”)))
или же
=СРЕДН(ЯЧЕЙКА(“имя файла”), ПОИСК(“]”, ЯЧЕЙКА(“имя файла”))+1, 31)
Как работают формулы:
Формула 1. Работая изнутри наружу, мы вычисляем количество символов в имени рабочего листа, вычитая позицию закрывающей скобки, возвращаемую ПОИСКОМ, из общей длины пути, вычисленной с помощью ДЛСТР. Затем мы передаем это число функции RIGHT, предписывая ей извлечь такое количество символов из конца текстовой строки, возвращаемой CELL.
Формула 2: мы используем функцию MID, чтобы извлечь только имя листа, начинающееся с первого символа после закрывающей скобки. Количество символов для извлечения указано как 31, что является максимальным количеством символов в именах рабочих листов, разрешенным пользовательским интерфейсом Excel (хотя формат файла Excel xlsx допускает до 255 символов в именах листов).
Путь к файлу
Эта формула покажет вам путь к файлу без имен книг и листов:
=ЛЕВА(ЯЧЕЙКА(“имя файла”), ПОИСК(“[“, CELL(“filename”))-1)
How the formula works:
First, you locate the position of the opening square bracket “[” with the SEARCH function and subtract 1. This gives you the number of characters to extract. And then, you use the LEFT function to pull that many characters from the beginning of the text string returned by CELL.
Path and file name
With this formula, you can get a full path to the file including the workbook name, but without the sheet name:
=SUBSTITUTE(LEFT(CELL(“filename”), SEARCH(“]”, CELL(“имя файла”))-1), “[“””)[“””)
Как работает формула:
Функция ПОИСК вычисляет положение закрывающей квадратной скобки, из которой вы вычитаете 1, а затем получаете функцию ВЛЕВО для извлечения такого количества символов из начала текстовой строки, возвращаемой ЯЧЕЙКОЙ. Это эффективно обрезает имя листа, но открывающая квадратная скобка остается. Чтобы избавиться от него, вы заменяете “[“пустойстрокой(“”)[“withanemptystring(“”)
Вот как вы используете функцию CELL в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, я приглашаю вас загрузить наш Пример рабочей книги Excel CELL Function.
Спасибо за внимание и надеемся увидеть вас в нашем блоге на следующей неделе!
