- ЯЧЕЙКА
(CELL) - Определяет информацию о формате, местоположении или содержимом ячейки.
ЯЧЕЙКА(тип_инфо; ссылка) Если в качестве первТип_инфо - текстовое значение, определяющее тип информации о ячейке. Доступны следующие типы:адрес - текстовое представление адреса ячейки, записанной в аргументе("address") ссылка . Адрес отображается в соответствии с текущим стилем ссылок.столбец - Номер столбца ячейки в ссылке.("col") цвет - вернет 1, если форматом ячейки(правая кнопка мыши -Формат ячейки) установлено изменение цвета отрицательных значений в зависимости от значения в ячейке. Например, если это один из предустановленных финансовых форматов, при котором отрицательные значения в ячейке отображаются красным. При этом совершенно не важно какое на данный момент значение в ячейке и какого оно цвета.("color")
Если изменение цвета не предусмотрено форматом ячейки, функция вернет 0.содержимое - по сути текст или число, записанное в ячейке или возвращаемое формулой, записанной в этой ячейке("contents") имяфайла - Имя файла в текстовом виде (включая полный путь), содержащего ссылку. Возвращает пустой текст (""), если книга с листом, содержащим ссылку, еще не сохранена. Имеет свои нюансы, подробнее о которых можно почитать в статье Как получить имя листа формулой("filename") формат - Текстовое значение, соответствующее числовому формату ячейки. Возвращает "-" в конце текстового значения, если ячейка форматирована для вывода отрицательных значений другим цветом. Возвращает "()" в конце текстового значения, если ячейка форматирована для вывода положительных чисел в скобках. Доступные возвращаемые значения для различных форматов:("format")
Формат Возвращаемое значение Основной G 0 "F0" 0,00 "F2" # ##0 ",0" # ##0,00 ",2" # ##0\ _р_.;-# ##0\ _р_. ",0" # ##0\ _р_.;[Красный]-# ##0\ _р_. ",0-" # ##0,00\ _р_.;-# ##0,00\ _р_. ",2" # ##0,00\ _р_.;[Красный]-# ##0,00\ _р_. ",2-" 0% "P0" 0,00% "P2" 0,00E+00 "S2" ##0,0E+0 "S1" #" "?/? или #" "??/?? "G" Д.ММ.ГГ или Д.МММ.ГГ или Д.ММ.ГГ ч:мм "D1" Д.МММ "D2" МММ.ГГ "D3" ч:мм AM/PM "D7" ч:мм:сс AM/PM "D6" ч:мм "D9" ч:мм:сс "D8" мм:сс,0 "G" [ч]:мм:сс "G" 0;[Зеленый]0 "F0-" скобки - 1, если ячейка форматирована для вывода чисел (в том числе только положительных) в скобках. В ином случае возвращает 0.("parentheses") префикс - Текстовое значение, соответствующее "метке-префиксу" ячейки. Вернет символ апострофа(("prefix") ), если текст в ячейке выровнен влево; символ двойная кавычка(' ), если текст в ячейке выровнен вправо; символ(" ), если текст в ячейке выровнен по центру; символ обратной косой черты(^ ), если текст в ячейке выровнен по обоим краям. Функция вернет пустую строку(\ ), если ячейка содержит что-либо другое, отличное от текста"" защита - 0, если ячейка не заблокирована, и 1, если ячейка заблокирована. Подробнее про блокирование ячеек можно узнать из этой статьи - Как разрешить изменять только выбранные ячейки?. Функция не определяет, защищен ли в данный момент лист.("protect") строка - Номер строки ячейки в ссылке.("row") тип - Текстовое значение, означающее тип данных в ячейке. Возвращает("type") "b" , если ячейка пустая;"l" - если ячейка содержит текстовую константу; "v" - если ячейка содержит значение иного типа.ширина - Ширина столбца ячейки, округленная до ближайшего целого. Ширина определяется на основании ширины символа текущего шрифта для ячейки.("width")
- ссылка на ячейку, данные о которой требуется получить. Если указано более одной ячейки, то информация будет возвращено только для верхней левой ячейки указанного диапазонаСсылка
Если функция была применена к ячейке и после этого у ячейки, заданной аргументом
ссылка был изменен формат, ширина столбца или защита, то для получения корректной информации о текущем состоянии ячейки(в зависимости от типа информации) необходимо заново вычислить функцию или нажать Shift+F9 для вычисления всех формул листа.
Некоторые примеры применения:
=ЯЧЕЙКА("строка"; A20 )вернет 20
=ЯЧЕЙКА("формат";A1 )вернет "D2" при условии, что к ячейке A1 применен формат "Д-МММ"
=ЯЧЕЙКА("содержимое";A2 )вернет текст "ВСЕГО", если в ячейке A2 записан текст "ВСЕГО"
У функции ЯЧЕЙКА есть нюанс: т.к. в качестве аргумента принимается текстовая константа, то написанная в русской версии Excel эта константа не будет работать в других локализациях. Например, если записать на листе:
=ЯЧЕЙКА("строка"; A20 )
сохранить и открыть файл в английскоязычном Excel, то функция вернет значение ошибки, а в ячейке функция будет записана так:=CELL("строка", . Т.е. название функции будет преобразовано в её английское написание, но текстовая константа - нет. Для обеспечения совместимости необходимо использовать английские названия констант:A20 )
=ЯЧЕЙКА("row";A20)
Такое написание констант будет корректно работать в любой локализации, т.к. является для Excel "интернациональным"(тот же трюк работает и с функцией ИНФОРМ(INFO) ) - ТИП.ОШИБКИ
(ERROR.TYPE) - Определяет номер, соответствующий одному из типов ошибок Microsoft Excel. Чаще всего применяется в составе логических функций типаЕСЛИ для проверки результата вычисления функций.(IF) ТИП.ОШИБКИ(значение_ошибки)
Значение_ошибки - значение ошибки, номер которой необходимо определить. Как правило ссылка на ячейку с формулой, чтобы определить, не возвращает ли формула ошибку. При необходимости определяется конкретный тип ошибки. Всего доступно 7 вариантов возвращаемых значений:
1 - #ПУСТО! (#EMPTY!)
2 - #ДЕЛ/0!(#DIV/0)
3 - #ЗНАЧ!(#VALUE!)
4 - #ССЫЛКА!(#REF!)
5 - #ИМЯ?(#NAME?)
6 - #ЧИСЛО!(#NUM!)
7 - #Н/Д(#N/A)
Если в ячейке содержится любое другое значение(не ошибочное), функция вернет значение ошибки #Н/Д(#N/A) .
Пример применения:
=ЕСЛИ(ТИП.ОШИБКИ( A10 )=4; "Неверная ссылка";A10 )
=IF(ERROR.TYPE(A10)=4, "Wrong reference", A10) - ИНФОРМ
(INFO) - Выдает информацию о текущей операционной среде и некоторых свойствах приложения Excel. Однако данная функция давно не дорабатывалась и некоторые сведения могут быть неверными или выдавать ошибочный результат в версиях Excel 2007 и выше.
ИНФОРМ(тип_инфо)
Тип_инфо - текстовая константа, означающая тип возвращаемой информации.
=ИНФОРМ("КАТАЛОГ")
=ИНФОРМ("ВЕРСИЯОС")
=ИНФОРМ("ПЕРЕВЫЧИСЛИТЬ")
=INFO("directory")
=INFO("osversion")
=INFO("recalc")
Доступны следующие варианты:КАТАЛОГ - Путь текущего каталога("directory") ЧИСЛОФАЙЛОВ - Количество рабочих листов во всех открытых книгах Excel. При этом подсчитываются абсолютно все листы всех книг, даже если листы скрыты и если это листы надстроек и скрытых книг. Поэтому если хотите подсчитать только отображенные листы данная функция не поможет, т.к. у пользователя могут быть активны надстройки вроде Пакета анализа и Поиска решения, листы которых так же будут подсчитаны.("numfile") ИСТОЧНИК - Возвращается абсолютная ссылка на левую верхнюю видимую в текущем окне ячейку с учетом состояния прокрутки листа. Ссылка возвращается в виде текста с префиксом("origin") "$A:" . Применяется для совместимости с Lotus 1-2-3 версий 3.x. Возвращаемое значение зависит от текущих настроек стиля ссылок. Например, если верхняя левая ячейка в окне - ячейка H5, то функцияИНФОРМ вернет следующий текст:- Стиль ссылок A1:
"$A:$H$5" - Стиль ссылок R1C1:
"$A:R5C8"
- Стиль ссылок A1:
ВЕРСИЯОС - Возвращает сведения о версии операционной системы, в которой запущен Excel. Однако, если используется 64-разрядная версия ОС, а Excel установлен из пакета 32-бита, то и версия будет идентифицирована как 32-битная. Связано с тем, что функция определяет основные исполняемые файлы приложения и в зависимости именно от их битности выдаст результат. Это может ввести в заблуждение, т.к. тот же офис 365 определяется именно как 32-битный, даже если устанавливался на 64-битную ОС из 64-битного пакета...("osversion") ПЕРЕВЫЧИСЛИТЬ - Текущий режим вычисления формул. Функция возвращает "Автоматически" или "Вручную", в зависимости от того, какой режим выставлен в настройках. Более подробно про режим вычислений можно узнать в статье: Формулы перестали считать - как исправить?("recalc") ВЕРСИЯ - Версия Microsoft Excel в виде текста. Для Excel 2003 вернет "11.0", 2007 - "12.0", 2010 - "14.0", 2013 - "15.0", 2016(а так же 365) - "16.0"("release") СИСТЕМА - Возвращает название операционной среды, в которой запущен Excel. Macintosh(MAC OS) = "mac", Windows = "pcdos"("system") - Приведенные ниже типы применимы только в версиях Excel 2003 и более ранних. Начиная с Excel 2007 данные типы не используются и функция вернет значение ошибки
#ЗНАЧ! :(#VALUE!) ПАМЯТЬВСЕГО - Общее количество доступной памяти в байтах, включая уже используемую("totmem") ДОСТУПНАЯПАМЯТЬ - Количество свободной памяти в байтах("memavail") ИСПОЛЬЗУЕМАЯПАМЯТЬ - Количество памяти, используемой для данных("memused")
С функцией ИНФОРМ есть один нюанс: т.к. в качестве аргумента принимается текстовая константа, то написанная в русской версии Excel эта константа не будет работать в других локализациях. Например, если записать на листе:
=ИНФОРМ("ВЕРСИЯОС")
сохранить и открыть файл в английскоязычном Excel, то функция вернет значение ошибки, а в ячейке функция будет записана так:=INFO("ВЕРСИЯОС") . Т.е. название функции будет преобразовано в её английское написание, но текстовая константа - нет. Как же обеспечить совместимость? Все просто. Надо использовать английские названия констант:
=ИНФОРМ("osversion")
Такое написание констант будет корректно работать в любой локализации, т.к. является для Excel "интернациональным"(тот же трюк работает и с другой функцией, работающей с набором текстовых констант - ЯЧЕЙКА(CELL) ) - ЕПУСТО
(ISBLANK) - Выдает логическое значение ИСТИНА, если аргумент является ссылкой на пустую ячейку.
=ЕПУСТО(значение)
значение - ссылка на ячейку или именованный диапазон, значение которого необходимо проверить на наличие пустого значения. Если ссылка содержит более одной ячейки, то функция проверит значение только одной - левой верхней ячейки.
=ЕПУСТО( A1 )
Что важно помнить, при работе с этой функцией: если в проверяемой ячейке записана формула, то такая ячейка всегда будет считаться не пустой и функцияЕПУСТО вернет значениеЛОЖЬ , даже если при этом формула в проверяемой ячейке возвращает пустую строку((FALSE) ="" ). - ЕОШ
(ISERR) - Выдает логическое значениеИСТИНА , если аргумент ссылается на любое значение ошибки, кроме(TRUE) #Н/Д .(#N/A)
=ЕОШ(значение)
значение - непосредственно формула или значение, а так же ссылка на ячейку или именованный диапазон, которые необходимо проверить на наличие ошибки. Если ссылка содержит более одной ячейки, то функция проверит значение только одной - левой верхней ячейки.
Чаще всего используется вместе с вложенными функциями, которые могут вернуть значение ошибки(например, функция ПОИСК или НАЙТИ), кроме ошибки#Н/Д :(#N/A)
=ЕОШ(( A1 )
=ЕСЛИ(ЕОШ((НАЙТИ("дом";A1 ));"";ПСТР(A1 ;1;НАЙТИ("дом";A1 )-1))
где в A1 текст для поиска значения "дом". Если значение в ячейке есть - функция возвращает текст до этого значения
Чтобы обработать исключительно ошибку#Н/Д используется функция(#N/A) ЕНД
Пример применения функции можно посмотреть в этой статье - Как в ячейке с формулой вместо ошибки показать 0 - ЕОШИБКА
(ISERROR) - Выдает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки
=ЕОШИБКА(значение)
значение - непосредственно формула или значение, а так же ссылка на ячейку или именованный диапазон, которые необходимо проверить на наличие ошибки. Если ссылка содержит более одной ячейки, то функция проверит значение только одной - левой верхней ячейки.
Применяется для обработки функций, которые могут вернуть значение ошибки или проверки ячеек, содержащих такие функции:
=ЕОШИБКА( A1 )
=ЕСЛИ(ЕОШИБКА(ВПР(A1 ;B2:F10;2;0));"";ВПР(A1 ;B2:F10;2;0))
Если ВПР не найдет значение(вернет ошибку #Н/Д) или возвращаемое значение будет содержать любую ошибку, то формула вернет пустую строку. В противном случае - будет вычислена ВПР
Пример применения функции можно посмотреть в этой статье - Как в ячейке с формулой вместо ошибки показать 0
Подробнее про ВПР можно ознакомиться здесь: Как найти значение в другой таблице или сила ВПР - ЕНД
(ISNA) - Выдает логическое значение ИСТИНА, если аргумент ссылается на значение ошибки#Н/Д (значение недоступно).(#N/A)
=ЕНД(значение)
значение - непосредственно формула или значение, а так же ссылка на ячейку или именованный диапазон, которые необходимо проверить на наличие ошибки. Если ссылка содержит более одной ячейки, то функция проверит значение только одной - левой верхней ячейки.
Очень похожа наЕОШ иЕОШИБКА , но работает исключительно с типом ошибок#Н/Д . Поэтому применяется в основном вместе с функциями вроде(#N/A) ВПР и(VLOOKUP) ПОИСКПОЗ . Подробнее про ВПР можно ознакомиться здесь: Как найти значение в другой таблице или сила ВПР(MATCH) - ЕЧЁТН
(ISEVEN) - Выдает логическое значениеИСТИНА , если аргумент - четное число.(TRUE)
=ЕЧЁТН(число)
число - непосредственно значение или ссылка на ячейку с числом. Число может быть положительным или отрицательным. 0 будет считаться четным. Если значение является не целым числом, то дробная часть числа отбрасывается(например, 2,5 будет преобразовано в 2). Если значение не является числом, то функция возвращает значение ошибки#ЗНАЧ! .(#VALUE!) В версиях 2007 и ниже функция может быть недоступна. В этом случае необходимо установить надстройку Пакет Анализа, а затем подключить его с помощью команды Надстройки из меню.
=ЕЧЁТН(-1) вернет ЛОЖЬ
=ЕЧЁТН(2,5)вернет ИСТИНА
=ЕЧЁТН(5)вернет ЛОЖЬ - ЕНЕЧЁТ
(ISODD) - Выдает логическое значение ИСТИНА, если аргумент - нечетное число.
=ЕНЕЧЁТ(число)
число - непосредственно значение или ссылка на ячейку с числом. Число может быть положительным или отрицательным. 0 будет считаться четным, т.е. функция вернетЛОЖЬ . Если значение является не целым числом, то дробная часть числа отбрасывается(например, 2,5 будет преобразовано в 2). Если значение не является числом, то функция возвращает значение ошибки(FALSE) #ЗНАЧ! .(#VALUE!) В версиях 2007 и ниже функция может быть недоступна. В этом случае необходимо установить надстройку Пакет Анализа, а затем подключить его с помощью команды Надстройки из меню.
=ЕНЕЧЁТ(-1) вернет ИСТИНА
=ЕНЕЧЁТ(2,5)вернет ЛОЖЬ
=ЕНЕЧЁТ(5)вернет ИСТИНА - ЕЛОГИЧ
(ISLOGICAL) - Выдает логическое значение ИСТИНА, если аргумент ссылается на логическое значение.
=ЕЛОГИЧ(значение)
значение - непосредственно значение или ссылка на ячейку, а также формула или функция. - ЕНЕТЕКСТ
(ISNONTEXT) - Выдает логическое значение ИСТИНА, если аргумент ссылается на значение, которое не является текстом.
=ЕНЕТЕКСТ(значение)
Самое важное, что стоит помнить в этой функции - она вернет ИСТИНА, если аргументзначение является ссылкой на пустую ячейку. - ЕЧИСЛО
(ISNUMBER) - Выдает логическое значение ИСТИНА, если аргумент является числом.
=ЕЧИСЛО(значение)
Отличие этой функции от функцииЕНЕТЕКСТ в том, что если аргументом(ISNONTEXT) значение будет логическое ИСТИНА или ЛОЖЬ или значение ошибки, то функция ЕНЕТЕКСТ вернет ИСТИНА, в то время как ЕЧИСЛО вернет ЛОЖЬ. - ЕССЫЛКА
(ISREF) - Выдает логическое значение ИСТИНА, если аргумент является рабочей ссылкой на существующую ячейку.
=ЕССЫЛКА(значение)
значение - ссылка на одну ячейку, диапазон ячеек или именованный диапазон. Если аргумент является текстом или несуществующей ячейкой, функция вернет ЛОЖЬ. Как правило ЕССЫЛКА используется в случаях, когда другие функции используют данные с других листов, которые могут быть удалены.
=ЕСЛИ(ЕССЫЛКА(Лист4!A1:C10);ВПР( A1 ;Лист4!A1:C10;3;0);ВПР(A1 ;Лист2!A1:C10;3;0))
=IF(ISREF(Лист4!A1:C10),VLOOKUP(A1,Лист4!A1:C10,3,0),VLOOKUP(A1,Лист2!A1:C10,3,0))
В этом случае функцияЕССЫЛКА будет возвращатьИСТИНА , если(TRUE) Лист4 присутствует в книге и тогда ВПР будет брать данные с этого листа. Если жеЛист4 будет удален - ВПР будет брать данные с листаЛист2 . Это более корректно, чем применение ЕОШИБКА или подобные им, т.к. ВПР может возвращать значение ошибки с самого листаЛист2 , например, если искомые данные отсутствуют на листе.
Подробнее про ВПР можно ознакомиться здесь: Как найти значение в другой таблице или сила ВПР - ЕТЕКСТ
(ISTEXT) - Выдает логическое значение ИСТИНА, если аргумент является текстом.
=ЕТЕКСТ(значение)
значение - непосредственно значение или ссылка на ячейку, значение которой необходимо проверить. Если аргументомзначение будет логическое ИСТИНА или ЛОЖЬ или значение ошибки, то функция ЕТЕКСТ вернет ЛОЖЬ.
Главное, о чем стоит помнить, применяя функцию это то, что если число в ячейке записано как текст("2"), функция вернет ИСТИНА. Т.е. функция проверяет именно тип данных, а не возможность работы со значением как с числом. - Ч
(N) - Преобразует заданное значение в число.
=Ч(значение)
значение - произвольное значение или ссылка на ячейку со значением. Если значение является значением любой из ошибок Excel(#ДЕЛ/0!(#DIV/0) , #ЗНАЧ!(#VALUE!) , #ССЫЛКА!(#REF!) , #ИМЯ?(#NAME?) , #ЧИСЛО!(#NUM!) , #Н/Д(#N/A) ), то функция вернет значение этой ошибки. Преобразование не будет выполнено.
Остальные типы данных преобразуются по следующим правилам:Число или число, записанное как текст - будет преобразовано в это же число, которое является реальным числомДата в одном из встроенных форматов Microsoft Excel - будет преобразована в дату в числовом формате(чуть более подробно о представлении дат в Excel можно узнать из статьи: Как Excel воспринимает данные?)ИСТИНА - будет преобразовано в 1любой другой тип данных - будет преобразован в 0
Наиболее часто функция применяется в формулах массива, когда необходимо игнорировать текстовые значения, а числа, записанные как текст преобразовать в реальные числа.
- НД
(NA) - Выдает значение ошибки #Н/Д. Не имеет аргументов и записывается в ячейку обязательно со скобками:=НД() - ТИП
(TYPE) - Выдает тип значения.
=ТИП(значение)
значение - произвольное значение или ссылка на ячейку со значением.
Применяется в основном в случаях, когда результаты вычисления другой функции зависят от типа значения в конкретной ячейке. Наиболее актуальна была до появления VBA, когда для автоматизации применялись функции листов макросов. На данный момент используется гораздо реже.
Ниже приведены возвращаемые функцией значения и типы данных, им соответствующие:
1 - Число
2 - Текст
4 - Логическое значение
16 - Значение ошибки
64 - Массив
При помощи данной функции нельзя определить, содержится ли в ячейке формула. Эта функция всего лишь определяет тип результирующего (отображаемого) значения. Если аргумент "значение" является ссылкой на ячейку, содержащую формулу, функция ТИП возвращает тип результата вычисления формулы.
Некоторые примеры:
при условии, что в A1 записано значение "excel-vba.ru", т.е. текст
ТИП("url = "&A1)функция вернет тип 2
ТИП(2+A1)сложение текста с числом вызовет ошибку(#ЗНАЧ!) и функция вернет тип 16
ТИП({1;2:3;4})функция вернет тип 64 - ЕФОРМУЛА(ISFORMULA) - проверяет содержится ли в ячейке формула или нет и возвращает ИСТИНА, если ячейка содержит формулу и ЛОЖЬ, если значение в ячейке является константой.
=ЕФОРМУЛА(ссылка)
ссылка - ссылка на ячейку, которую необходимо проверить на наличие формулы. Если ссылка указывает на диапазон ячеек, то проверена будет лишь одна верхняя левая ячейка.
Примечание: функция вернет ИСТИНА даже в том случае, если в ячейке нет никакой функции, а просто записана ссылка на другую ячейку или диапазон ячеек:=B4