backtotop
Функция СМЕЩ в Excel. Как автоматически расширить диапазон?
Для чего в Excel формула СУММПРОИЗВ?

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

Кто знает VBA, удивится: функционал легко заменяется программно. Но мы здесь занимаемся оптимизацией без программирования, для пользователей.

Функция ЯЧЕЙКА в Excel. Описание

Если в двух словах, то функция =ЯЧЕЙКА() работает со свойствами ячейки и имеет два аргумента

  • тип_сведений — обязательный реквизит, текст, заполняется аргументами на выбор в таблице ниже
  • ссылка — необязательный аргумент, адрес ссылки на ячейку, для некоторых типов сведений, например таких как «имяфайла» его не нужно указывать. Если аргумент это диапазон, т.к. стандартно выводится результат верхней левой ячейки.

Таблица аргумента Тип_сведений представлена на рисунке в начале страницы, таблице ниже, а так же в файле примере (список представлен в последовательности формулы)

Тип_сведений Описание Значение ячейки Возвращается
«адрес» Адрес ячейки в формате А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

Расшифровка типа сведений «префикс»

  1. одинарная кавычка или апостраф(‘) текст выровнен влево,
  2. («) — текст, выровнен вправо,
  3. (^) — текст, выровнен по центру,
  4.  (/) — текст, распределен по всей ширине ячейки,
  5. Пусто («») — любому другому содержимому ячейки.

Функция ЯЧЕЙКА в Excel. Как применять?

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

Разберем более интересные примеры:

1. Необходимо отметить в столбце все ячейки начиная с 5 и записать в них 1

Функция ЯЧЕЙКА в Excel

Формула

=ЕСЛИ(ЯЧЕЙКА("строка";A6)>5;1;"")

2. Если вам нужно найти столбец ширина которого равна 100, не проверять же каждый столбец руками
Функция ЯЧЕЙКА в Excel
Формула

=ЕСЛИ(ЯЧЕЙКА("ширина";C1)=10;1;0)

Примеры доступны так же в файле

 

Функция СМЕЩ в Excel. Как автоматически расширить диапазон?
Для чего в Excel формула СУММПРОИЗВ?

Комментарии:

( 2 Comments )

Константин:

Добрый день!
Отличная статья, для тех кто первый раз использует эту функцию в Экселе просто замечательно, это как раз мой случай :)
Хочу попросить помощи в решении моей задачи: Есть Лист1 с названием «Старые процедуры», на нем 4 столбца: А — № процедуры; В — Имя процедуры; С — Класс процедуры; D — функции БД.
Необходимо на Лист2 «Новые процедуры» сопоставить по первым 3 столбцам новый номер, добавив к существующему 10 000, а так же название и класс процедуры, путём добавления постфикса «NEW» к уже существующим значениям.
Задача усложняется тем, что для одной процедуры может быть несколько записей на Листе1, а вот на Листе2 должны содержаться только уникальные записи без повторений.
Хотел попытаться поступить следующим образом:
1) Получить Адрес ячейки для уникальной, выбранной руками, записи на Листе1 (Ф-ция: =ЯЧЕЙКА)
2) Используя ф-цию =СМЕЩ и =СЦЕПИТЬ добавить необходимую информацию.
Но результат функции =ЯЧЕЙКА(«адрес»; А2) воспринимается как текст, а не как ссылка на ячейку :( Что делать???

Добрый день!
Константин, спасибо за Ваш комментарий, я, к сожалению уезжал, поэтому задержался с ответом. Пришлите пожалуйста пример на почту nizotov@excelworks.ru, так мне будет проще понять задачу :)
Но как мне видится, попроще будет возратить нужные значения функциями суммеслимн или впр а затем уже сделать сцепить. Но как я сказал лучше на примере глянуть! Если, что описание функций есть на сайте, просто введите в поиске ;)

Добавить комментарий