Функция ячейка в excel и примеры ее использования. Закрасить ячейку по условию или формуле Excel функция значение ячейки
20-й день нашего марафона мы посвятим изучению функции ADDRESS (АДРЕС). Она возвращает адрес ячейки в текстовом формате, используя номер строки и столбца. Нужен ли нам этот адрес? Можно ли сделать то же самое с помощью других функций?
Давайте обратимся к сведениям по функции ADDRESS (АДРЕС) и изучим примеры работы с ней. Если у Вас есть дополнительная информация или примеры, пожалуйста, делитесь ими в комментариях.
Функция 20: ADDRESS (АДРЕС)
Функция ADDRESS (АДРЕС) возвращает ссылку на ячейку в виде текста, основываясь на номере строки и столбца. Она может возвращать абсолютный или относительный адрес в стиле ссылок A1 или R1C1 . К тому же в результат может быть включено имя листа.
Как можно использовать функцию ADDRESS (АДРЕС)?
Функция ADDRESS (АДРЕС) может возвратить адрес ячейки или работать в сочетании с другими функциями, чтобы:
- Получить адрес ячейки, зная номер строки и столбца.
- Найти значение ячейки, зная номер строки и столбца.
- Возвратить адрес ячейки с самым большим значением.
Синтаксис ADDRESS (АДРЕС)
Функция ADDRESS (АДРЕС) имеет вот такой синтаксис:
ADDRESS(row_num,column_num,,,)
АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])
- abs_num (тип_ссылки) – если равно 1 или вообще не указано, то функция возвратит абсолютный адрес ($A$1). Чтобы получить относительный адрес (A1), используйте значение 4 . Остальные варианты: 2 =A$1, 3 =$A1.
- a1 – если TRUE (ИСТИНА) или вообще не указано, функция возвращает ссылку в стиле A1 , если FALSE (ЛОЖЬ), то в стиле R1C1 .
- sheet _text (имя_листа) – имя листа может быть указано, если Вы желаете видеть его в возвращаемом функцией результате.
Ловушки ADDRESS (АДРЕС)
Функция ADDRESS (АДРЕС) возвращает лишь адрес ячейки в виде текстовой строки. Если Вам нужно значение ячейки, используйте её в качестве аргумента функции INDIRECT (ДВССЫЛ) или примените одну из альтернативных формул, показанных в .
Пример 1: Получаем адрес ячейки по номеру строки и столбца
При помощи функции ADDRESS (АДРЕС) Вы можете получить адрес ячейки в виде текста, используя номер строки и столбца. Если Вы введёте только эти два аргумента, результатом будет абсолютный адрес, записанный в стиле ссылок A1 .
ADDRESS($C$2,$C$3)
=АДРЕС($C$2;$C$3)
Абсолютная или относительная
Если не указывать значение аргумента abs_num (тип_ссылки) в формуле, то результатом будет абсолютная ссылка.
Чтобы увидеть адрес в виде относительной ссылки, можно подставить в качестве аргумента abs_num (тип_ссылки) значение 4 .
ADDRESS($C$2,$C$3,4)
=АДРЕС($C$2;$C$3;4)
A1 или R1C1
Чтобы задать стиль ссылок R1C1 , вместо принятого по умолчанию стиля A1 , Вы должны указать значение FALSE (ЛОЖЬ) для аргумента а1 .
ADDRESS($C$2,$C$3,1,FALSE)
=АДРЕС($C$2;$C$3;1;ЛОЖЬ)
Название листа
Последний аргумент – это имя листа. Если Вам необходимо это имя в полученном результате, укажите его в качестве аргумента sheet_text (имя_листа).
ADDRESS($C$2,$C$3,1,TRUE,"Ex02")
=АДРЕС($C$2;$C$3;1;ИСТИНА;"Ex02")
Пример 2: Находим значение ячейки, используя номер строки и столбца
Функция ADDRESS (АДРЕС) возвращает адрес ячейки в виде текста, а не как действующую ссылку. Если Вам нужно получить значение ячейки, можно использовать результат, возвращаемый функцией ADDRESS (АДРЕС), как аргумент для INDIRECT (ДВССЫЛ). Мы изучим функцию INDIRECT (ДВССЫЛ) позже в рамках марафона 30 функций Excel за 30 дней .
INDIRECT(ADDRESS(C2,C3))
=ДВССЫЛ(АДРЕС(C2;C3))
Функция INDIRECT (ДВССЫЛ) может работать и без функции ADDRESS (АДРЕС). Вот как можно, используя оператор конкатенации “& “, слепить нужный адрес в стиле R1C1 и в результате получить значение ячейки:
INDIRECT("R"&C2&"C"&C3,FALSE)
=ДВССЫЛ("R"&C2&"C"&C3;ЛОЖЬ)
Функция INDEX (ИНДЕКС) также может вернуть значение ячейки, если указан номер строки и столбца:
INDEX(1:5000,C2,C3)
=ИНДЕКС(1:5000;C2;C3)
1:5000 – это первые 5000 строк листа Excel.
Пример 3: Возвращаем адрес ячейки с максимальным значением
В этом примере мы найдём ячейку с максимальным значением и используем функцию ADDRESS (АДРЕС), чтобы получить её адрес.
Функция MAX (МАКС) находит максимальное число в столбце C.
MAX(C3:C8)
=МАКС(C3:C8)
ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))
=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))
Функция ЯЧЕЙКА возвращает сведения о форматировании, расположении или содержимом ячейки.
Описание функции ЯЧЕЙКА
Функция возвращает сведения о форматировании, расположении или содержимом ячейки.
Например, если перед выполнением вычислений с ячейкой необходимо удостовериться в том, что она содержит числовое значение, а не текст, можно использовать следующую формулу:
ЕСЛИ(ЯЧЕЙКА("тип";A1)="v";A1*2;0) Эта формула вычисляет произведение A1*2, только если в ячейке A1 содержится числовое значение, и возвращает значение 0, если в ячейке A1 содержится текст или она пустая.
Синтаксис
=ЯЧЕЙКА(тип_сведений; [ссылка])Аргументы
Обязательный аргумент. Текстовое значение, задающее тип сведений о ячейке при возвращении. В приведенном ниже списке указаны возможные значения аргумента «тип_сведений» и соответствующие результаты.
| Тип_сведений | Возвращаемое значение |
|---|---|
| «адрес» | Ссылка на первую ячейку в аргументе «ссылка» в виде текстовой строки. |
| «столбец» | Номер столбца ячейки в аргументе «ссылка». |
| «цвет» | 1, если форматированием ячейки предусмотрено изменение цвета для отрицательных значений; во всех остальных случаях - 0 (ноль). |
| «содержимое» | Значение левой верхней ячейки в ссылке; не формула. |
| «имяфайла» | Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка («»). |
| «формат» | Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется «-«. Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется «()». |
| «скобки» | 1, если форматированием ячейки предусмотрено отображение положительных или всех чисел в круглых скобках; во всех остальных случаях - 0. |
| «префикс» | Текстовое значение, соответствующее префиксу метки ячейки. Одиночная кавычка (‘) соответствует тексту, выровненному влево, двойная кавычка («) - тексту, выровненному вправо, знак крышки (^) - тексту, выровненному по центру, обратная косая черта () - тексту, распределенному по всей ширине ячейки, а пустой текст («») - любому другому содержимому ячейки. |
| «защита» | 0, если ячейка разблокирована, и 1, если ячейка заблокирована. |
| «строка» | Номер строки ячейки в аргументе «ссылка». |
| «тип» | Текстовое значение, соответствующее типу данных в ячейке. Значение «b» соответствует пустой ячейке, «l» - текстовой константе в ячейке, «v» - любому другому содержимому. |
| «ширина» | Ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера. |
Внимание!
В Excel Web App нет поддержки аргумента «формат».
Необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе «тип_сведений», возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА возвращает сведения только для левой верхней ячейки диапазона.
Коды форматов функции ЯЧЕЙКА
Внимание! Если аргумент «тип_сведений» функции ЯЧЕЙКА имеет значение «формат», а формат ячейки был изменен, для обновления значения функции ЯЧЕЙКА необходимо пересчитать лист.
Поймал себя на мысли, что не до конца освоил функционал функции =ЯЧЕЙКА() (CELL в английской версии). Исправляюсь. Далее расскажу подробнее про функционал функции и как можно его применить на практике, например, как найти номер строки или столбца, адрес ячейки, или даже адрес и имя файла. Функция ЯЧЕЙКА в Excel, разбираем подробнее.
Кто знает VBA, удивиться, функционал легко заменяется программно. Но мы здесь занимаемся оптимизацией без программирования, для пользователей.
Если в двух словах, то функция =ЯЧЕЙКА() работает со свойствами ячейки и имеет два аргумента
- тип_сведений — обязательный реквизит, текст, заполняется аргументами на выбор в таблице ниже
- ссылка — необязательный аргумент, адрес ссылки на ячейку, для некоторых типов сведений, например таких как «имяфайла» его не нужно указывать. Если аргумент это диапазон, т.к. стандартно выводится результат верхней левой ячейки.
Таблица аргумента Тип_сведений представлена на рисунке в начале страницы, таблице ниже, а так же в примере (список представлен в последовательности формулы)
| Тип_сведений | Описание | Значение ячейки | Возвращается |
| «адрес» | Адрес ячейки в формате А1 (если диапазон то верхней левой ячейки) | $D$3 | |
| «защита» | 0, если ячейка разблокирована в Формате ячейки, и 1, если ячейка заблокирована. | 1 | |
| «имяфайла» | Полный путь к файлу, включая имя и название листа. Для несохраненгого листа возвращается пусто «» C:\Users\….\Пример.xlsx |
C:\Users\User\Desktop\[Функция Ячейка.xlsx]1 | |
| «префикс» | Текст. Код обозначающий выравнивание ячейки, расшифровка ниже | текст | ^ |
| «скобки» | 1, если формат отрицательных чисел в скобках; иначе — 0. | 0 | |
| «содержимое» | Не формула, значение ячейки | 5 | 5 |
| «столбец» | Номер столбца аргумента «ссылка». | 4 | |
| «строка» | Номер строки аргумента «ссылка». | 10 | |
| «тип» | Текст. Зависит от типа данных, значение «b» — пустая ячейка, «l» - константа, «v» - любому другому содержимому. | 1 | v |
| «формат» | Текст. Буква соответствует определенному формату (). Значения для различных форматов показаны ниже в таблице. Если меняется цвет при отрицательных значениях то добавляется «-«. | C2 | |
| «цвет» | 1, если ячейка изменяет цвет для отрицательных значений; иначе = 0 (ноль) | 0 | |
| «ширина» | Целое число. Ширина столбца указанной ссылки | 8 |
Расшифровка типа сведений «префикс »
- одинарная кавычка или апостраф(‘) текст выровнен влево,
- («) - текст, выровнен вправо,
- (^) - текст, выровнен по центру,
- (/) - текст, распределен по всей ширине ячейки,
- Пусто («») - любому другому содержимому ячейки.
Функция ЯЧЕЙКА в Excel. Как применять?
Как найти номер строки/столбца и адрес файла думаю вы поняли из таблицы.
Разберем более интересные примеры:
1. Необходимо отметить в столбце все ячейки начиная с 5 и записать в них 1
Очень часто при работе в Excel необходимо использовать данные об адресации ячеек в электронной таблице. Для этого была предусмотрена функция ЯЧЕЙКА. Рассмотрим ее использование на конкретных примерах.
Функция значения и свойства ячейки в Excel
Стоит отметить, что в Excel используются несколько функций по адресации ячеек:
- – СТРОКА;
- – СТОЛБЕЦ и другие.
Функция ЯЧЕЙКА(), английская версия CELL(), возвращает сведения о форматировании, адресе или содержимом ячейки. Функция может вернуть подробную информацию о формате ячейки, исключив тем самым в некоторых случаях необходимость использования VBA. Функция особенно полезна, если необходимо вывести в ячейки полный путь файла.
Как работает функция ЯЧЕЙКА в Excel?
Функция ЯЧЕЙКА в своей работе использует синтаксис, который состоит из двух аргументов:
Примеры использования функции ЯЧЕЙКА в Excel
Пример 1. Дана таблица учета работы сотрудников организации вида:
Необходимо с помощью функции ЯЧЕЙКА вычислить в какой строке и столбце находится зарплата размером 235000 руб.
Для этого введем формулу следующего вида:
- – «строка» и «столбец» – параметр вывода;
- – С8 – адрес данных с зарплатой.
В результате вычислений получим: строка №8 и столбец №3 (С).
Как узнать ширину таблицы Excel?
Пример 2. Нужно вычислить ширину таблицы в символах. Сразу стоит отметить что в Excel по умолчанию ширина столбцов и ячеек измеряется в количестве символов, которые они умещают в своем значении доступны для отображения в ячейке без переноса строки.
Примечание. Высота строк и ячеек в Excel по умолчанию измеряется в единицах измерения базового шрифта – в пунктах pt. Чем больше шрифт, тем выше строка для полного отображения символов по высоте.
Введем в ячейку С14 формулу для вычисления суммы ширины каждого столбца таблицы:
- – «ширина» – параметр функции;
- – А1 – ширина определенного столбца.
Как получить значение первой ячейки в диапазоне
Пример 3. В условии примера 1 нужно вывести содержимое только из первой (верхней левой) ячейки из диапазона A5:C8.
Введем формулу для вычисления:
Описание формулы аналогичное предыдущим двум примерам.
Для выполнения этой задачи будем использовать возможности условного форматирования.
Возьмем таблицу, содержащую список заказов, сроки их исполнения, текущий статус и стоимость. Попробуем сделать так, чтобы ее ячейки раскрашивались сами, в зависимости от их содержимого.
Инструкция для Excel 2010
ВКЛЮЧИТЕ СУБТИТРЫ!
Как это сделать в Excel 2007
ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».
Выберем четвертый пункт, позволяющий сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Формат», зададим цвет ячеек.
Подтверждаем наш выбор, и ячейки с ценой выше средней окрасились в голубой цвет, привлекая наше внимание к дорогим заказам.
Выделим ячейки со статусами заказов и создадим новое правило. На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели.
Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату.
«ОК», и мы получили весело разукрашенную таблицу, позволяющую наглядно отслеживать ход выполнения заказов.
Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции .
Как это сделать в Excel 2003
ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.
Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!
