Функция в excel ячейка

Функция ЯЧЕЙКА в Excel и примеры ее использования

Очень часто при работе в Excel необходимо использовать данные об адресации ячеек в электронной таблице. Для этого была предусмотрена функция ЯЧЕЙКА. Рассмотрим ее использование на конкретных примерах.

Функция значения и свойства ячейки в Excel

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

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

Как работает функция ЯЧЕЙКА в Excel?

Функция ЯЧЕЙКА в своей работе использует синтаксис, который состоит из двух аргументов:

1. Тип_сведений – текстовое значение, задающее требуемый тип сведений о ячейке. При вводе функции вручную высвечивается выпадающий список где указаны все возможные значения аргумента «тип сведений»:
2. Ссылка – необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе тип_сведений, возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА() возвращает сведения только для левой верхней ячейки диапазона.



Примеры использования функции ЯЧЕЙКА в Excel

Пример 1. Дана таблица учета работы сотрудников организации вида:

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

Для этого введем формулу следующего вида:

• – «строка» и «столбец» – параметр вывода;
• – С8 – адрес данных с зарплатой.

В результате вычислений получим: строка №8 и столбец №3 (С).

Как узнать ширину таблицы Excel?

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

Примечание. Высота строк и ячеек в Excel по умолчанию измеряется в единицах измерения базового шрифта – в пунктах pt. Чем больше шрифт, тем выше строка для полного отображения символов по высоте.

Введем в ячейку С14 формулу для вычисления суммы ширины каждого столбца таблицы:

• – «ширина» – параметр функции;
• – А1 – ширина определенного столбца.

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

Пример 3. В условии примера 1 нужно вывести содержимое только из первой (верхней левой) ячейки из диапазона A5:C8.

Введем формулу для вычисления:

Описание формулы аналогичное предыдущим двум примерам.

Функция ЯЧЕЙКА() в MS EXCEL

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

Синтаксис функции ЯЧЕЙКА()

ЯЧЕЙКА(тип_сведений, [ссылка])

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

ссылка — Необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе тип_сведений, возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА() возвращает сведения только для левой верхней ячейки диапазона.

Использование функции

В файле примера приведены основные примеры использования функции:

Большинство сведений об ячейке касаются ее формата. Альтернативным источником информации такого рода может случить только VBA.

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

Обратите внимание, что если в одном экземпляре MS EXCEL (см. примечание ниже) открыто несколько книг, то функция ЯЧЕЙКА() с аргументами адрес и имяфайла, будет отображать имя того файла, с который Вы изменяли последним. Например, открыто 2 книги в одном окне MS EXCEL: Базаданных.xlsx и Отчет.xlsx. В книге Базаданных.xlsx имеется формула =ЯЧЕЙКА(«имяфайла») для отображения в ячейке имени текущего файла, т.е. Базаданных.xlsx (с полным путем и с указанием листа, на котором расположена эта формула). Если перейти в окно книги Отчет.xlsx и поменять, например, содержимое ячейки, то вернувшись в окно книги Базаданных.xlsx (CTRL+TAB) увидим, что в ячейке с формулой =ЯЧЕЙКА(«имяфайла») содержится имя Отчет.xlsx. Это может быть источником ошибки. Хорошая новость в том, что при открытии книги функция пересчитывает свое значение (также пересчитать книгу можно нажав клавишу F9). При открытии файлов в разных экземплярах MS EXCEL — подобного эффекта не возникает — формула =ЯЧЕЙКА(«имяфайла») будет возвращать имя файла, в ячейку которого эта формула введена.

Примечание: Открыть несколько книг EXCEL можно в одном окне MS EXCEL (в одном экземпляре MS EXCEL) или в нескольких. Обычно книги открываются в одном экземпляре MS EXCEL (когда Вы просто открываете их подряд из Проводника Windows или через Кнопку Офис в окне MS EXCEL). Второй экземпляр MS EXCEL можно открыть запустив файл EXCEL.EXE, например через меню Пуск. Чтобы убедиться, что файлы открыты в одном экземпляре MS EXCEL нажимайте последовательно сочетание клавиш CTRL+TAB — будут отображаться все окна Книг, которые открыты в данном окне MS EXCEL. Для книг, открытых в разных окнах MS EXCEL (экземплярах MS EXCEL) это сочетание клавиш не работает. Удобно открывать в разных экземплярах Книги, вычисления в которых занимают продолжительное время. При изменении формул MS EXCEL пересчитывает только книги открытые в текущем экземпляре.

Другие возможности функции ЯЧЕЙКА() : определение типа значения, номера столбца или строки, мало востребованы, т.к. дублируются стандартными функциями ЕТЕКСТ() , ЕЧИСЛО() , СТОЛБЕЦ() и др.

Извлечение информации о ячейке функцией ЯЧЕЙКА (CELL)

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

Синтаксис у функции следующий:

=ЯЧЕЙКА( Параметр ; Адрес )

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

Давайте рассмотрим пару трюков с применением этой функции на практике.

Например, можно получить имя текущего листа формулой, используя функцию ЯЧЕЙКА с параметром «имяфайла» и извлекающей все символы правее закрывающей квадратной скобки:

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

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

Функция Получить.Ячейку

Задача: вы хотите выделить все ячейки на листе, которые не содержат формул.

Примечание Багузина. Именно эту задачу можно решить довольно просто, если вы пользуетесь версией Excel 2013 или более поздней. Примените функцию ЕФОРМУЛА(ссылка). Функция проверяет содержимое ячейки, и возвращает значение ИСТИНА или ЛОЖЬ. Однако подход Билла Джелена любопытен сам по себе, поскольку открывает окно в мир макрофункций (скорее всего, неизвестный большинству пользователей).

Решение: до введения VBA, макросы писали на языке xlm (Excel Macro). Язык использовал макрофункции, т.е., функции листа макросов Excel 4.0. Этот язык до сих пор поддерживается Microsoft для совместимости с предыдущими версиями Excel (подробнее см. Что такое макрофункции?). Система макросов xlm является «пережитком», доставшимся нам от предыдущих версий Excel (4.0 и более ранних). Более поздние версии Excel все еще выполняют макросы xlm, но, начиная с Excel 97, пользователи не имеют возможности записывать макросы на языке xlm.

Язык xlm среди прочих содержит функцию Получить.Ячейку (GET.CELL), которая предоставляет гораздо больше информации, чем современная функция ЯЧЕЙКА(). На самом деле, Получить.Ячейку может рассказать о 66 различных атрибутах ячейки, в то время, как функция ЯЧЕЙКА возвращает лишь 12 параметров. Функция Получить.Ячейку весьма полезна, за исключением одного «но»… Вы не можете ввести ее непосредственно в ячейку (рис. 1).

Рис. 1. Функция Получить.Ячейку недоступна для ввода на листе Excel

Скачать заметку в формате Word или pdf, примеры в формате Excel (с макросами)

Однако есть обходной путь. Вы можете определить имя, основанное на функции, а затем ссылаться на это имя в любой ячейке. Например, чтобы выяснить, содержит ли ячейка A1 формулу, можно записать =Получить.Ячейку(48,А1). Здесь 48 – аргумент, отвечающий за анализ, является ли содержимое ячейки формулой. Для более универсального случая, когда вы хотите применить условное форматирование, воспользуйтесь формулой =Получить.Ячейку(48,ДВССЫЛ( » RC » ,ЛОЖЬ)). Если вы не знакомы с функцией ДВССЫЛ, советую почитать Примеры использования функции ДВССЫЛ (INDIRECT). Нам эта функция нужна для того, чтобы обозначить ссылку на ячейку, в которой мы сейчас находимся. Мы не можем указать никакую конкретную ячейку, поэтому используем ссылку в стиле R1C1, где RC означает относительную ссылку на текущую ячейку. В стиле ссылок А1 для ссылки на текущую ячейку нам бы потребовалось этот фрагмент формулы записать в виде =ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ();4)). Подробнее см. Зачем нужен стиль ссылок R1C1.

Чтобы использовать формулу =Получить.Ячейку() для выделения ячеек с помощью условного форматирования, выполните следующие действия (для Excel 2007 или более поздней версии):

1. Чтобы определить новое имя, пройдите по меню ФОРМУЛЫ –>Присвоить имя. В открывшемся окне (рис. 2) выберите подходящее имя, например, ЕслиФормула. В поле формула введите =Получить.Ячейку(48,ДВССЫЛ( » RC » ,ЛОЖЬ)). Нажмите Оk. Нажмите Закрыть.
2. Выделите ячейки, к которым хотите применить условное форматирование (рис. 3); в нашем примере – это В3:В15.
3. Пройдите по меню ГЛАВНАЯ –>Условное форматирование –>Создать правило. В открывшемся окне выберите пункт Использовать формулу для определения форматируемых ячеек. В нижней половине диалогового типа введите =ЕслиФормула, как показано на рис. 3. Excel может автоматически добавить кавычки =»ЕслиФормула». Уберите их. Нажмите кнопку Формат, в открывшемся окне Формат ячеек перейдите на вкладку Заливка и выберите цвет заливки. Нажмите Оk.

Рис. 2. Окно Создание имени

Рис. 3. Создание нового правила условного форматирования

Чтобы выделить ячейки, которые не содержат формулу, используйте настройку формата =НЕ(ЕслиФормула).

Будьте осторожны. Иногда при копировании ячеек, содержащих формулу, на другой лист, есть риск «обрушить» Excel (у меня такого не случилось ни разу).

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

1. Выберите все ячейки; для этого встаньте на одну из ячеек диапазона и нажмите Ctrl+А (А – английское).
2. Нажмите Ctrl+G, чтобы открыть окно Переход.
3. В левом нижнем углу этого окна нажмите кнопку Выделить.
4. В открывшемся диалоговом окне Выделить группу ячеек выберите формулы, нажмите Ok.
5. На закладке ГЛАВНАЯ выберите цвет заливки, например, красный.

Синтаксис функции: ПОЛУЧИТЬ.ЯЧЕЙКУ(номер_типа; ссылка). Полный список первого аргумента функции Получить.Ячейку см., например, здесь. Обратите внимание, что в некоторых случаях функциональность современных версий Excel существенно изменилась, и функция не вернет допустимое значение. Для некоторых аргументов номер_типа удобнее использовать функцию ЯЧЕЙКА.

Несколько примеров функции ПОЛУЧИТЬ.ЯЧЕЙКУ.

Номер_типа = 1. Абсолютная ссылка левой верхней ячейки аргумента ссылка в виде текста в текущем стиле: $А$1 или R1C1 (рис. 4). Проще использовать формулу =ЯЧЕЙКА( » адрес » ;ссылка)

Рис. 4. Определение адреса левой верхней ячейки диапазона

Номер_типа = 63. Возвращает номер цвета заливки ячейки (рис. 5).

Рис. 5. Определение номера цвета заливки ячейки

30 функций Excel за 30 дней: ЯЧЕЙКА (CELL)

В 4-ый день марафона 30 функций Excel за 30 дней мы получали детальные сведения о рабочей среде, используя функцию INFO (ИНФОРМ), – такие как версия Excel и режим пересчёта.

Одиннадцатый день марафона мы посвятим изучению функции CELL (ЯЧЕЙКА), которая сообщит информацию о форматировании ячейки, её содержимом и расположении. Она работает аналогично функции INFO (ИНФОРМ), т.е. имеет список значений, которые можно ввести в функцию, но содержит не один, а два аргумента.

Итак, давайте рассмотрим информацию и примеры по функции CELL (ЯЧЕЙКА). Если у Вас есть что добавить к нашим примерам и сведениям, пожалуйста, делитесь этим в комментариях.

Функция 11: CELL (ЯЧЕЙКА)

Функция CELL (ЯЧЕЙКА) показывает информацию о форматировании, содержании и расположении ячейки по заданной ссылке.

Как можно использовать функцию CELL (ЯЧЕЙКА)?

Функция CELL (ЯЧЕЙКА) может сообщить о ячейке информацию следующего содержания:

• Числовой формат ячейки.
• Название листа.
• Выравнивание или ширину столбца.

Синтаксис CELL (ЯЧЕЙКА)

Функция CELL (ЯЧЕЙКА) имеет вот такой синтаксис:

info_type (тип_сведений) – это один из вариантов аргумента:

• address (адрес) – ссылка на первую ячейку в аргументе reference (ссылка) в текстовом формате.
• col (столбец) – номер столбца ячейки в аргументе reference (cсылка).
• сolor (цвет) – возвращается 1, если форматированием ячейки предусмотрено изменение цвета для отрицательных значений; во всех остальных случаях возвращается 0 (ноль).
• contents (содержимое) – значение левой верхней ячейки в ссылке.
• filename (имяфайла) – имя файла и полный путь.
• format (формат) – числовой формат ячейки.
• parentheses (скобки) – возвращает 1, если форматированием ячейки предусмотрено отображение положительных или всех чисел в круглых скобках; во всех остальных случаях возвращает 0 (ноль).
• prefix (префикс) – текстовое значение, соответствующее префиксу метки ячейки (показывает тип выравнивания).
• protect (защита) – 0 = ячейка не заблокирована, 1 = заблокирована.
• row (строка) – номер строки ячейки.
• type (тип) – тип данных в ячейке (пустая, текст, другое).
• width (ширина) – ширина столбца ячейки.

Ловушки функции CELL (ЯЧЕЙКА)

Есть несколько моментов, за которыми нужно внимательно следить, используя функцию CELL (ЯЧЕЙКА):

• Если аргумент reference (ссылка) опущен, то результат возвращается для последней изменённой ячейки. Чтобы быть уверенным, что результат получился именно тот, что Вам нужен, ссылку желательно всегда указывать. Вы можете сослаться даже ячейку, в которой содержится сама функция CELL (ЯЧЕЙКА).
• При работе с функцией CELL (ЯЧЕЙКА), иногда необходимо делать пересчёт листа, чтобы обновить результат, который возвращает функция.
• Если в качестве аргумента info_type (тип_сведений) выбрано значение filename (имяфайла), а книга Excel ещё не сохранена, то результатом будет пустая строка.

Пример 1: Формат числа в ячейке

Со значением format (формат) Вы можете использовать функцию CELL (ЯЧЕЙКА), чтобы показать числовой формат ячейки. Например, если ячейка B7 имеет формат General (Общий), то результатом формулы будет G:

Пример 2: Название листа

Со значением filename (имяфайла) функция CELL (ЯЧЕЙКА) покажет путь к файлу, имя файла и название листа.

Из полученного результата при помощи других функций Вы можете извлечь название листа. В формуле ниже, используя функции MID (ПСТР) и FIND (НАЙТИ), находим квадратные скобки и возвращаем 32 символа, которые следуют за ними (длина названия листа ограничена 31 символом).

Пример 3: Подстановка аргумента info_type (тип_сведений) из выпадающего списка

Вместо того, чтобы вводить значение аргумента info_type (тип_сведений) в функцию CELL (ЯЧЕЙКА) в виде текстовой строки, Вы можете указать ссылку на ячейку, содержащую корректные значения. В этом примере в ячейке B4 находится выпадающий список, а вместо аргумента info_type (тип_сведений) указана ссылка на эту ячейку. Аргумент reference (ссылка) ссылается на ячейку B2.

Когда выбрано значение protect (защита): результат равен 1, если ячейка заблокирована, или 0 (ноль), если не заблокирована.

Когда выбрано значение width (ширина), результат показывает ширину столбца в формате целого числа. Единицей измерения в этом случае является ширина одного символа при стандартном размере шрифта.