Возвращает информацию о форматировании, размещении или содержимом ячейки.

Синтаксис:

ПОЛУЧИТЬ.ЯЧЕЙКУ(ном_типа ; ссылка )

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

Ном_типа Возвращает

1 Абсолютную ссылку верхней левой ячейки в аргументе ссылка в виде текста в текущем стиле рабочего пространства.
2 Номер строки верхней ячейки в аргументе ссылка.
3 Номер столбца самой верхней ячейки в аргументе ссылка.
4 То же, что и ТИП(ссылка).
5 Содержимое аргумента ссылка.
6 Формула в аргументе ссылка в виде текста, стиль которого А1 или R1C1 -- в зависимости от параметров рабочего пространства.
7 Номер формата ячейки (например, «М/Д/ГГ» или «Основной»).
8 Число, показывающее горизонтальное выравнивание ячейки:

1 = Нормальное
2 = Левое
3 = По центру
4 = Правое
5 = Заполнить
6 = По обоим краям
7 = Центрировать через ячейки

9 Число, показывающее стиль левой границы, назначаемый ячейке:

0 = Без границы
1 = Тонкая линия
2 = Средняя линия
3 = Штриховая линия
4 = Пунктирная линия
5 = Толстая линия
6 = Двойная линия
7 = Самая тонкая линия

10 Число, показывающее стиль правой границы, назначаемый ячейке. Возвращаемые числа см. в описании аргумента ном_типа 9.
11 Число, показывающее стиль верхней границы, назначаемый ячейке. Возвращаемые числа см. в описании аргумента ном_типа 9.
12 Число, показывающее стиль нижней границы, назначаемый ячейке. Возвращаемые числа см. в описании аргумента ном_типа 9.
13 Число от 0 до 18, показывающее узор выделенной ячейки как выводимый на экран на панели «Узоры» диалогового окна Формат ячеек, которое появляется, если в меню Формат выбрать команду Ячейки. Если узор не выбран, возвращается значение 0.
14 Если ячейка заблокирована, возвращается значение ИСТИНА, иначе возвращается значение ЛОЖЬ.
15 Если ячейка скрыта, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ.
16 Горизонтальный массив из двух элементов, содержащий ширину активной ячейки и логическое значение, показывающее, установлена ли ширина ячейки в стандартное значение (ИСТИНА) или в пользовательское (ЛОЖЬ).
17 Высота ячейки в точках.
18 Имя шрифта в виде текста.
19 Размер шрифта в точках.
20 Если все символы ячейки или только первый символ выделены полужирным шрифтом, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ.
21 Если все символы ячейки или только первый символ выделены курсивом, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ.
22 Если все символы ячейки или только первый символ выделены подчеркиванием, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ.
23 Если все символы ячейки или только первый символ выделены перечеркиванием, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ.
24 Число от 1 до 56, обозначающее цвет шрифта. Если цвет шрифта выбран автоматически, возвращается значение 0.
25 Если все символы ячейки или только первый символ обведены контуром, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ. Этот тип не поддерживается Microsoft Excel для Windows.
26 Если все символы ячейки или только первый символ затанены, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ. Этот тип не поддерживается Microsoft Excel для Windows.
27 Число, показывающее, проходит ли разбиение на страницы рядом с ячейкой:

0 = Не разбивается
1 = По строкам
2 = По столбцам
3 = И по строкам и по столбцам

28 Уровень строки (контур).
29 Уровень столбца (контур).
30 Если содержимое строки активной ячейки является итоговой строкой, возвращается ИСТИНА, иначе возвращается ЛОЖЬ.
31 Если содержимое строки активной ячейки является итоговым столбцом, возвращается ИСТИНА, иначе возвращается ЛОЖЬ.
32 Наименование рабочей книги и листа, содержащих ячейку. Если окно содержит только один лист с тем же именем, что и рабочая книга без расширения, возвращается только имя книги в форме BOOK1.XLS. Иначе возвращается имя листа в форме «[Книга1]Лист1».
33 Если ячейка форматирована с переносом по словам, возвращается ИСТИНА, иначе возвращается ЛОЖЬ.
34 Число от 1 до 56, обозначающее цвет левой границы. Если цвет выбирается автоматически, возвращается 0.
35 Число от 1 до 56, обозначающее цвет правой границы. Если цвет выбирается автоматически, возвращается 0.
36 Число от 1 до 56, обозначающее цвет верхней границы. Если цвет выбирается автоматически, возвращается 0.
37 Число от 1 до 56, обозначающее цвет нижней границы. Если цвет выбирается автоматически, возвращается 0.
38 Число от 1 до 56, обозначающее цвет тени переднего плана. Если цвет выбирается автоматически, возвращается 0.
39 Число от 1 до 56, обозначающее цвет тени фона. Если цвет выбирается автоматически, возвращается 0.
40 Стиль ячейки в виде текста.
41 Возвращает формулу в активной ячейке (полезно для международных форматов листов макросов).
42 Горизонтальное расстояние, измеряемое в точках от левого края активного окна до левого края ячейки. Может быть отрицательным числом, если окно прокручивается вне ячейки.
43 Вертикальное расстояние, измеряемое в точках от верхнего края активного окна до верхнего края ячейки. Может быть отрицательным числом, если окно прокручивается вне ячейки.
44 Горизонтальное расстояние, измеряемое в точках от левого края активного окна до правого края ячейки. Может быть отрицательным числом, если окно прокручивается вне ячейки.
45 Вертикальное расстояние, измеряемое в точках от верхнего края активного окна до нижнего края ячейки. Может быть отрицательным числом, если окно прокручивается вне ячейки.
46 Если ячейка содержит текстовую заметку, возвращается ИСТИНА, иначе возвращается ЛОЖЬ.
47 Если ячейка содержит звуковую заметку, возвращается ИСТИНА, иначе возвращается ЛОЖЬ.
48 Если ячейка содержит формулу, возвращается ИСТИНА; если содержит константу -- возвращается ЛОЖЬ.
49 Если ячейка является частью массива, возвращается ИСТИНА, иначе возвращается ЛОЖЬ
50 Число, показывающее вертикальное выравнивание ячейки:

1 = Вверх
2 = По центру
3 = Вниз
4 = По обоим краям

51 Число, показывающее вертикальную ориентацию ячейки:

0 = Горизоонтальная
1 = Вертикальная
2 = Направленная вверх
3 = Направленная вниз

52 Символ префикса ячейки (или выравнивание текста) или пустой текст (««), если ячейка не содержит текста.
53 Содержимое ячейки, если она в данных момент выведена на экран в виде текста, включающего любые дополнительные цифры или символы, являющиеся результатом форматирования ячейки.
54 Возвращает имя сводной таблицы, содержащей активную ячейку.
55 Возвращает положение ячейки внутри сводной таблицы.
56 Возвращает имя поля, содержащего ссылку на активную ячейку, если оно находится внутри сводной таблицы.
57 Если все символы ячейки или только первый символ форматированы с надстрочным шрифтом, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ.
58 Возвращает стиль шрифта в виде текста всех символов ячейки или только первого символа, как показано в диалоговом окне Формат ячеек на вкладке «Шрифт». Например, «полужирный курсив».

59 Возвращает цифру для стиля «подчеркивание»:

1 = Нет
2 = Одиночное
3 = Двойное
4 = Одиночное денежное
5 = Двойное денежное

60 Если все символы ячейки или только первый символ форматированы с подстрочным шрифтом, возвращается значение ИСТИНА, иначе возвращается ЛОЖЬ.
61 Возвращается имя элемента сводной таблицы для активной ячейки в виде текста.
62 Возвращает имя рабочей книги и текущего листа в форме «[Книга1]лист1».
63 Заполняет цветом ячейку (фон).
64 Возвращает узор фона ячейки.
65 Возвращает значение ИСТИНА, если включен параметр выравнивания доб_отступ (только для Microsoft Excel версии Far East); иначе возвращает ЛОЖЬ.
66 Возвращает имя рабочей книги, содержащей ячейку в форме BOOK1.XLS.

Примеры:

Следующая макроформула возвращает значение ИСТИНА, если ячейка B4 на листе Лист1 выделена полужирным шрифтом:

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))

При работе с таблицами первоочередное значение имеют выводимые в ней значения. Но немаловажной составляющей является также и её оформление. Некоторые пользователи считают это второстепенным фактором и не обращают на него особого внимания. А зря, ведь красиво оформленная таблица является важным условием для лучшего её восприятия и понимания пользователями. Особенно большую роль в этом играет визуализация данных. Например, с помощью инструментов визуализации можно окрасить ячейки таблицы в зависимости от их содержимого. Давайте узнаем, как это можно сделать в программе Excel.

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

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

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

Способ 1: условное форматирование

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

Посмотрим, как этот способ работает на конкретном примере. Имеем таблицу доходов предприятия, в которой данные разбиты помесячно. Нам нужно выделить разными цветами те элементы, в которых величина доходов менее 400000 рублей, от 400000 до 500000 рублей и превышает 500000 рублей.

1. Выделяем столбец, в котором находится информация по доходам предприятия. Затем перемещаемся во вкладку «Главная» . Щелкаем по кнопке «Условное форматирование» , которая располагается на ленте в блоке инструментов «Стили» . В открывшемся списке выбираем пункт «Управления правилами…» .



2. Запускается окошко управления правилами условного форматирования. В поле «Показать правила форматирования для» должно быть установлено значение «Текущий фрагмент» . По умолчанию именно оно и должно быть там указано, но на всякий случай проверьте и в случае несоответствия измените настройки согласно вышеуказанным рекомендациям. После этого следует нажать на кнопку «Создать правило…» .



3. Открывается окно создания правила форматирования. В списке типов правил выбираем позицию . В блоке описания правила в первом поле переключатель должен стоять в позиции «Значения» . Во втором поле устанавливаем переключатель в позицию «Меньше» . В третьем поле указываем значение, элементы листа, содержащие величину меньше которого, будут окрашены определенным цветом. В нашем случае это значение будет 400000 . После этого жмем на кнопку «Формат…» .



4. Открывается окно формата ячеек. Перемещаемся во вкладку «Заливка» . Выбираем тот цвет заливки, которым желаем, чтобы выделялись ячейки, содержащие величину менее 400000 . После этого жмем на кнопку «OK» в нижней части окна.



5. Возвращаемся в окно создания правила форматирования и там тоже жмем на кнопку «OK» .



6. После этого действия мы снова будем перенаправлены в Диспетчер правил условного форматирования . Как видим, одно правило уже добавлено, но нам предстоит добавить ещё два. Поэтому снова жмем на кнопку «Создать правило…» .



7. И опять мы попадаем в окно создания правила. Перемещаемся в раздел «Форматировать только ячейки, которые содержат» . В первом поле данного раздела оставляем параметр «Значение ячейки» , а во втором выставляем переключатель в позицию «Между» . В третьем поле нужно указать начальное значение диапазона, в котором будут форматироваться элементы листа. В нашем случае это число 400000 . В четвертом указываем конечное значение данного диапазона. Оно составит 500000 . После этого щелкаем по кнопке «Формат…» .



8. В окне форматирования снова перемещаемся во вкладку «Заливка» , но на этот раз уже выбираем другой цвет, после чего жмем на кнопку «OK» .



9. После возврата в окно создания правила тоже жмем на кнопку «OK» .



10. Как видим, в Диспетчере правил у нас создано уже два правила. Таким образом, осталось создать третье. Щелкаем по кнопке «Создать правило» .



11. В окне создания правила опять перемещаемся в раздел «Форматировать только ячейки, которые содержат» . В первом поле оставляем вариант «Значение ячейки» . Во втором поле устанавливаем переключатель в полицию «Больше» . В третьем поле вбиваем число 500000 . Затем, как и в предыдущих случаях, жмем на кнопку «Формат…» .



12. В окне «Формат ячеек» опять перемещаемся во вкладку «Заливка» . На этот раз выбираем цвет, который отличается от двух предыдущих случаев. Выполняем щелчок по кнопке «OK» .



13. В окне создания правил повторяем нажатие на кнопку «OK» .



14. Открывается Диспетчер правил . Как видим, все три правила созданы, поэтому жмем на кнопку «OK» .



15. Теперь элементы таблицы окрашены согласно заданным условиям и границам в настройках условного форматирования.



16. Если мы изменим содержимое в одной из ячеек, выходя при этом за границы одного из заданных правил, то при этом данный элемент листа автоматически сменит цвет.

Кроме того, можно использовать условное форматирование несколько по-другому для окраски элементов листа цветом.

Способ 2: использование инструмента «Найти и выделить»

Если в таблице находятся статические данные, которые не планируется со временем изменять, то можно воспользоваться инструментом для изменения цвета ячеек по их содержимому под названием «Найти и выделить» . Указанный инструмент позволит отыскать заданные значения и изменить цвет в этих ячейках на нужный пользователю. Но следует учесть, что при изменении содержимого в элементах листа, цвет автоматически изменяться не будет, а останется прежним. Для того, чтобы сменить цвет на актуальный, придется повторять процедуру заново. Поэтому данный способ не является оптимальным для таблиц с динамическим содержимым.

Посмотрим, как это работает на конкретном примере, для которого возьмем все ту же таблицу дохода предприятия.

1. Выделяем столбец с данными, которые следует отформатировать цветом. Затем переходим во вкладку «Главная» и жмем на кнопку «Найти и выделить» , которая размещена на ленте в блоке инструментов «Редактирование» . В открывшемся списке кликаем по пункту «Найти» .



2. Запускается окно «Найти и заменить» во вкладке «Найти» . Прежде всего, найдем значения до 400000 рублей. Так как у нас нет ни одной ячейки, где содержалось бы значение менее 300000 рублей, то, по сути, нам нужно выделить все элементы, в которых содержатся числа в диапазоне от 300000 до 400000 . К сожалению, прямо указать данный диапазон, как в случае применения условного форматирования, в данном способе нельзя.

   Но существует возможность поступить несколько по-другому, что нам даст тот же результат. Можно в строке поиска задать следующий шаблон «3?????» . Знак вопроса означает любой символ. Таким образом, программа будет искать все шестизначные числа, которые начинаются с цифры «3» . То есть, в выдачу поиска попадут значения в диапазоне 300000 – 400000 , что нам и требуется. Если бы в таблице были числа меньше 300000 или меньше 200000 , то для каждого диапазона в сотню тысяч поиск пришлось бы производить отдельно.

   Вводим выражение «3?????» в поле «Найти» и жмем на кнопку «Найти все ».



3. После этого в нижней части окошка открываются результаты поисковой выдачи. Кликаем левой кнопкой мыши по любому из них. Затем набираем комбинацию клавиш Ctrl+A . После этого выделяются все результаты поисковой выдачи и одновременно выделяются элементы в столбце, на которые данные результаты ссылаются.



4. После того, как элементы в столбце выделены, не спешим закрывать окно «Найти и заменить» . Находясь во вкладке «Главная» в которую мы переместились ранее, переходим на ленту к блоку инструментов «Шрифт» . Кликаем по треугольнику справа от кнопки «Цвет заливки» . Открывается выбор различных цветов заливки. Выбираем тот цвет, который мы желаем применить к элементам листа, содержащим величины менее 400000 рублей.



5. Как видим, все ячейки столбца, в которых находятся значения менее 400000 рублей, выделены выбранным цветом.



6. Теперь нам нужно окрасить элементы, в которых располагаются величины в диапазоне от 400000 до 500000 рублей. В этот диапазон входят числа, которые соответствуют шаблону «4??????» . Вбиваем его в поле поиска и щелкаем по кнопке «Найти все» , предварительно выделив нужный нам столбец.



7. Аналогично с предыдущим разом в поисковой выдаче производим выделение всего полученного результата нажатием комбинации горячих клавиш CTRL+A . После этого перемещаемся к значку выбора цвета заливки. Кликаем по нему и жмем на пиктограмму нужного нам оттенка, который будет окрашивать элементы листа, где находятся величины в диапазоне от 400000 до 500000 .



8. Как видим, после этого действия все элементы таблицы с данными в интервале с 400000 по 500000 выделены выбранным цветом.



9. Теперь нам осталось выделить последний интервал величин – более 500000 . Тут нам тоже повезло, так как все числа более 500000 находятся в интервале от 500000 до 600000 . Поэтому в поле поиска вводим выражение «5?????» и жмем на кнопку «Найти все» . Если бы были величины, превышающие 600000 , то нам бы пришлось дополнительно производить поиск для выражения «6?????» и т.д.



10. Опять выделяем результаты поиска при помощи комбинации Ctrl+A . Далее, воспользовавшись кнопкой на ленте, выбираем новый цвет для заливки интервала, превышающего 500000 по той же аналогии, как мы это делали ранее.



11. Как видим, после этого действия все элементы столбца будут закрашены, согласно тому числовому значению, которое в них размещено. Теперь можно закрывать окно поиска, нажав стандартную кнопку закрытия в верхнем правом углу окна, так как нашу задачу можно считать решенной.



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

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

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

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

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

• – СТРОКА;
• – СТОЛБЕЦ и другие.

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заметка написана с использованием книги Билла Джелена .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Любопытно. Несмотря на то что это макрофункция, язык приложения важен. В русском Excel функция GET.CELL не работает. И еще. Если вам нужна информация о сводной таблице, то аналог ПОЛУЧИТЬ.ЯЧЕЙКУ — обычная функция (доступная для ввода на листе Excel) .