Lost your password?

Главная » Функции Excel »

Проверка свойств и значений и Информационные функции

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

    • Тип_инфо - текстовое значение, определяющее тип информации о ячейке. Доступны следующие типы:
      • адрес("address") - текстовое представление адреса ячейки, записанной в аргументе ссылка. Адрес отображается в соответствии с текущим стилем ссылок.
      • столбец("col") - Номер столбца ячейки в ссылке.
      • цвет("color") - вернет 1, если форматом ячейки(правая кнопка мыши -Формат ячейки) установлено изменение цвета отрицательных значений в зависимости от значения в ячейке. Например, если это один из предустановленных финансовых форматов, при котором отрицательные значения в ячейке отображаются красным. При этом совершенно не важно какое на данный момент значение в ячейке и какого оно цвета.
        Если изменение цвета не предусмотрено форматом ячейки, функция вернет 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-"
      • скобки("parentheses") - 1, если ячейка форматирована для вывода чисел (в том числе только положительных) в скобках. В ином случае возвращает 0.
      • префикс("prefix") - Текстовое значение, соответствующее "метке-префиксу" ячейки. Вернет символ апострофа('), если текст в ячейке выровнен влево; символ двойная кавычка("), если текст в ячейке выровнен вправо; символ(^), если текст в ячейке выровнен по центру; символ обратной косой черты(\), если текст в ячейке выровнен по обоим краям. Функция вернет пустую строку(""), если ячейка содержит что-либо другое, отличное от текста
      • защита("protect") - 0, если ячейка не заблокирована, и 1, если ячейка заблокирована. Подробнее про блокирование ячеек можно узнать из этой статьи - Как разрешить изменять только выбранные ячейки?. Функция не определяет, защищен ли в данный момент лист.
      • строка("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") - Путь текущего каталога
    • ЧИСЛОФАЙЛОВ("numfile") - Количество рабочих листов во всех открытых книгах Excel. При этом подсчитываются абсолютно все листы всех книг, даже если листы скрыты и если это листы надстроек и скрытых книг. Поэтому если хотите подсчитать только отображенные листы данная функция не поможет, т.к. у пользователя могут быть активны надстройки вроде Пакета анализа и Поиска решения, листы которых так же будут подсчитаны.
    • ИСТОЧНИК("origin") - Возвращается абсолютная ссылка на левую верхнюю видимую в текущем окне ячейку с учетом состояния прокрутки листа. Ссылка возвращается в виде текста с префиксом "$A:". Применяется для совместимости с Lotus 1-2-3 версий 3.x. Возвращаемое значение зависит от текущих настроек стиля ссылок. Например, если верхняя левая ячейка в окне - ячейка H5, то функция ИНФОРМ вернет следующий текст:
      • Стиль ссылок A1: "$A:$H$5"
      • Стиль ссылок R1C1: "$A:R5C8"
    • ВЕРСИЯОС("osversion") - Возвращает сведения о версии операционной системы, в которой запущен Excel. Однако, если используется 64-разрядная версия ОС, а Excel установлен из пакета 32-бита, то и версия будет идентифицирована как 32-битная. Связано с тем, что функция определяет основные исполняемые файлы приложения и в зависимости именно от их битности выдаст результат. Это может ввести в заблуждение, т.к. тот же офис 365 определяется именно как 32-битный, даже если устанавливался на 64-битную ОС из 64-битного пакета...
    • ПЕРЕВЫЧИСЛИТЬ("recalc") - Текущий режим вычисления формул. Функция возвращает "Автоматически" или "Вручную", в зависимости от того, какой режим выставлен в настройках. Более подробно про режим вычислений можно узнать в статье: Формулы перестали считать - как исправить?
    • ВЕРСИЯ("release") - Версия Microsoft Excel в виде текста. Для Excel 2003 вернет "11.0", 2007 - "12.0", 2010 - "14.0", 2013 - "15.0", 2016(а так же 365) - "16.0"
    • СИСТЕМА("system") - Возвращает название операционной среды, в которой запущен Excel. Macintosh(MAC OS) = "mac", Windows = "pcdos"
    • Приведенные ниже типы применимы только в версиях 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 будет считаться четным, т.е. функция вернет ЛОЖЬ(FALSE). Если значение является не целым числом, то дробная часть числа отбрасывается(например, 2,5 будет преобразовано в 2). Если значение не является числом, то функция возвращает значение ошибки #ЗНАЧ!(#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

Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти