Как получить содержимое ячейки в формуле excel

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

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

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

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

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

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

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

ЯЧЕЙКА(тип_сведений, [ссылка])

1. Тип_сведений – текстовое значение, задающее требуемый тип сведений о ячейке. При вводе функции вручную высвечивается выпадающий список где указаны все возможные значения аргумента «тип сведений»:



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



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

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

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

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

тут:

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

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

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

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

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

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

тут:

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

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

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

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

Скачать примеры функции ЯЧЕЙКА в Excel

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

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

Синтаксис у функции следующий:

=ЯЧЕЙКА(Параметр; Адрес)

где Адрес — это, понятное дело, ссылка на нужную ячейку, а вот Параметр — это кодовое слово, определяющее какую именно информацию о ячейке мы хотим получить. У этого аргумента несколько возможных значений:

Давайте рассмотрим пару трюков с применением этой функции на практике.

Например, можно получить имя текущего листа формулой, используя функцию ЯЧЕЙКА с параметром «имяфайла» и извлекающей все символы правее закрывающей квадратной скобки:

Также можно проверить тип данных в ячейке (параметр «тип») и выводить сообщение об ошибке вместо вычислений, если введен текст или ячейка пуста:

Или подсветить условным форматированием все незащищенные ячейки в заданном диапазоне:

Ссылки по теме

• Включение / выключение подсветки незащищенных ячеек макросом
• Условное форматирование в Excel

Извлечение информации о ячейке функцией ЯЧЕЙКА (CELL)

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

Синтаксис у функции следующий:

=ЯЧЕЙКА( Параметр ; Адрес )

где Адрес — это, понятное дело, ссылка на нужную ячейку, а вот Параметр — это кодовое слово, определяющее какую именно информацию о ячейке мы хотим получить. У этого аргумента несколько возможных значений:

Давайте рассмотрим пару трюков с применением этой функции на практике.

Например, можно получить имя текущего листа формулой, используя функцию ЯЧЕЙКА с параметром «имяфайла» и извлекающей все символы правее закрывающей квадратной скобки:

Также можно проверить тип данных в ячейке (параметр «тип») и выводить сообщение об ошибке вместо вычислений, если введен текст или ячейка пуста:

Или подсветить условным форматированием все незащищенные ячейки в заданном диапазоне:

Функция ЯЧЕЙКА() в MS EXCEL

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

Синтаксис функции ЯЧЕЙКА()

ЯЧЕЙКА(тип_сведений, [ссылка])

тип_сведений— Текстовое значение, задающее требуемый тип сведений о ячейке. В приведенном ниже списке указаны возможные значения аргумента тип_сведений и соответствующие результаты.

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

Использование функции

В файле примера приведены основные примеры использования функции:

Большинство сведений об ячейке касаются ее формата. Альтернативным источником информации такого рода может случить только VBA.

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

Обратите внимание, что если в одном экземпляре MS EXCEL (см. примечание ниже) открыто несколько книг, то функция ЯЧЕЙКА() с аргументами адрес и имяфайла, будет отображать имя того файла, с который Вы изменяли последним. Например, открыто 2 книги в одном окне MS EXCEL: Базаданных.xlsx и Отчет.xlsx. В книге Базаданных.xlsx имеется формула =ЯЧЕЙКА(«имяфайла») для отображения в ячейке имени текущего файла, т.е. Базаданных.xlsx (с полным путем и с указанием листа, на котором расположена эта формула). Если перейти в окно книги Отчет.xlsx и поменять, например, содержимое ячейки, то вернувшись в окно книги Базаданных.xlsx (CTRL+TAB) увидим, что в ячейке с формулой =ЯЧЕЙКА(«имяфайла») содержится имя Отчет.xlsx. Это может быть источником ошибки. Хорошая новость в том, что при открытии книги функция пересчитывает свое значение (также пересчитать книгу можно нажав клавишу F9). При открытии файлов в разных экземплярах MS EXCEL — подобного эффекта не возникает — формула =ЯЧЕЙКА(«имяфайла») будет возвращать имя файла, в ячейку которого эта формула введена.

Примечание: Открыть несколько книг EXCEL можно в одном окне MS EXCEL (в одном экземпляре MS EXCEL) или в нескольких. Обычно книги открываются в одном экземпляре MS EXCEL (когда Вы просто открываете их подряд из Проводника Windows или через Кнопку Офис в окне MS EXCEL). Второй экземпляр MS EXCEL можно открыть запустив файл EXCEL.EXE, например через меню Пуск. Чтобы убедиться, что файлы открыты в одном экземпляре MS EXCEL нажимайте последовательно сочетание клавиш CTRL+TAB — будут отображаться все окна Книг, которые открыты в данном окне MS EXCEL. Для книг, открытых в разных окнах MS EXCEL (экземплярах MS EXCEL) это сочетание клавиш не работает. Удобно открывать в разных экземплярах Книги, вычисления в которых занимают продолжительное время. При изменении формул MS EXCEL пересчитывает только книги открытые в текущем экземпляре.

Другие возможности функции ЯЧЕЙКА() : определение типа значения, номера столбца или строки, мало востребованы, т.к. дублируются стандартными функциями ЕТЕКСТ() , ЕЧИСЛО() , СТОЛБЕЦ() и др.

Excel. Получить значение ячейки по адресу без макроса 2

Итак, решая очередную головоломку с закупкой материалов, я встал перед следующей ситуацией:

Даны две таблицы Excel на разных листах:

В таблице №1 строка состоит из пустых полей: уникальный номер товара, наименование, количество и т.п.

В таблице №2 строки непосредственно с данными: номер товара, название товара, цена, комплектность и т.п.

Я хочу связать эти таблички таким образом, чтобы при заполнении уникального номера в колонке таблицы №1 цена и название подтягивались из таблицы №2

Не хочу использовать для этого ни макросы, ни какое либо программирование, а выполнить эту задачу только средствами самого Excel

для этого мне нужно решить две задачи:

1) Найти в таблице строку, в которой находится информация о товаре

2) Используя знания об этой строке, вставить значение из нужного поля(столбца).

Решаем первую задачу:

= ПОИСКПОЗ(G11;Цены!A:A;0)

G11 — это та ячейка, куда мы вбиваем уникальный номер товара,

Цены!A:A — это колонка с уникальными номерами товаров

— формат вывода в абсолютных значениях, оставляем так, либо изучаем вопрос самостоятельно =)

Данная формула находит номер строки, в которой находится наш товар и информация о нём после того как в ячейку G11 мы вбили его уникальный номер.

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

Например за цену у нас ответственный второй столбец во второй таблице

= АДРЕС( ПОИСКПОЗ(G11;Цены!A:A;0); 2; 1;1; ‘Цены’ )

где тип ссылки абсолютный, то есть 1

и формат адреса ячеек не буквенно-числовые вида A11 B876 и т.д. , а R11C1, R876C2 и т.п

то-есть номера строк и столбцов сразу очевидны за этот формат у нас отвечает последняя 1

Сам адрес мы получаем для вкладки «Цены» , потому не забываем и её указать, иначе поиск значений будет идти на странице с таблицей 1

Мы уже у финиша

Следующая головоломка которую я не решил, зато мне подсказал мой приятель — гений Экселя.

Как зная адрес ячейки без использования макросов вытянуть из неё значения.

Всё просто, это функция

таким образом полная формула получится вида:

=ДВССЫЛ(АДРЕС(ПОИСКПОЗ(G11;Цены!A:A;0); 2 ;1;1;’Цены’)),

где меняя двойку на номер столбца с нужным типом данных мы получим любое необходимое нам значение по фиксированной товарной позиции: Наименование, тип итд

Теперь не нужно ломать голову по поводу проверки вводимых данных, они берутся полностью из имеющейся таблицы, которую до меня уже проверили ГЫГЫГЫ =)

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

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

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

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

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

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

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

1. Тип_сведений – текстовое значение, задающее требуемый тип сведений о ячейке. При вводе функции вручную высвечивается выпадающий список где указаны все возможные значения аргумента «тип сведений»:
2. Ссылка – необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе тип_сведений, возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА() возвращает сведения только для левой верхней ячейки диапазона.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Видимое значение ячейки в реальное

Часто из всем известной 1С отчеты выгружаются в Excel. Что не удивительно, ведь многие используют 1С как базу ведения всевозможных данных, а анализ предпочитают производить в Excel. И это удобно, это работает. Но часто при получении файла из 1С форматы ячеек изменены так, что отображается в ячейках одно значение, а на деле там значение совершенно иное:

Чтобы не возникло недопонимания, что это такое на картинках выше. Например, если в ячейку записать число 1077 , то оно и отобразится так же. Однако его визуальное отображение в ячейках можно изменить: выделяем ячейку -правая кнопка мыши —Формат ячеек (Format Cells) -вкладка Число (Number) . Далее в списке слева выбрать Дополнительный (Special) и установить Почтовый индекс. Тогда в ячейке визуально будет отображаться 001077 , в то время как реально в ячейке будет оставаться все то же число 1077 . Тоже и с датами. Реально в ячейке число, а визуально дата в одном из форматов из категории Дата. Подробнее про то, почему так происходит можно прочесть в статье: Как Excel воспринимает данные?
И как это всегда бывает — порой просто необходимо работать не с тем значением, которое реально в ячейке, а именно с теми, которые отображаются в ячейках. Яркий пример такой необходимости — это сцепление данных двух ячеек, в одной из которых записана дата. Например, в A1 записана дата » 06.02.2016 «, а в B1 текст вроде » Отчет по магазину за » и необходимо сцепить текст из B1 с датой из A1 . Если применить просто функцию СЦЕПИТЬ (CONCATENATE) или по простому = B1 & A1 , то результатом будет такой текст: Отчет по магазину за 42406 .
Если формат лишь один — можно стандартно попробовать побороть при помощи функции ТЕКСТ(TEXT). Например, в ячейках столбца А записаны даты в формате 31 января 2016г. Тогда формулу можно записать так:
=ТЕКСТ( A2 ;»[$-F800]ДДДД, ММММ ДД, ГГГГ»)
=TEXT( A2 ,»[$-F800]dddd, MMMM yy, yyyy»)
На примере той же СЦЕПИТЬ (CONCATENATE) :
=СЦЕПИТЬ( B1 ;ТЕКСТ( A1 ;»[$-F800]ДДДД, ММММ ДД, ГГГГ»))
=CONCATENATE( B1 ,TEXT( A1 ,»[$-F800]dddd, MMMM yy, yyyy»))

Сами вид формата для использования в функции ТЕКСТ можно подсмотреть непосредственно в форматах ячеек: правая кнопка мыши на ячейке —Формат ячеек (Format Cells) -вкладка Число (Number) -(все форматы). Там в поле Тип будет как раз приведен применяемый код формата. Можно его просто скопировать оттуда и вставить в функцию ТЕКСТ .
Но если форматы в ячейках различаются и записаны в разнобой. Стандартно этого никак не сделать, кроме как каждую ячейку руками перебивать. Но если прибегнуть к помощи Visual Basic for Applications(VBA), то можно написать простую функцию пользователя(Что такое функция пользователя(UDF)) и применить её:

Function VisualVal_Text(rc As Range) VisualVal_Text = rc.Text End Function

Для применения надо внимательно прочитать про создание функций пользователя. После этого в ячейку останется записать:
=VisualVal_Text( A1 )
и раскопировать ячейку на весь столбец. После этого можно заменить результат функции значениями(Как удалить в ячейке формулу, оставив значения) и все готово. Но и в этой функции есть недостаток. Если в ячейке отображается значение, которое не помещается в границы ячейки, то оно может быть обрезано или вместо значения будут решетки. Например, если дата в указанном формате не помещается в ячейку — вместо значений будут решетки #######:

и функция VisualVal_Text вернет так же решетки. Это тоже решаемо. Можно либо перед применением расширить столбцы с исходными данными так, чтобы значение отображалось правильно и полностью, либо применить чуть другую функцию пользователя:

Function VisualVal(rc As Range) VisualVal = Application.Text(rc.Value, rc.NumberFormat) End Function

Используется и записывается в ячейку так же, как и предыдущая:
=VisualVal( A1 )
Эта функция без всяких танцев с бубном вернет отображаемое форматом ячейки значение.

И вариант применения функции вместе с функцией СЦЕПИТЬ:
=СЦЕПИТЬ( B1 ;VisualVal_Text( A1 ))
=CONCATENATE( B1 ,VisualVal_Text( A1 ))
=СЦЕПИТЬ( B1 ;VisualVal( A1 ))
=CONCATENATE( B1 ,VisualVal( A1 ))
Как видно не надо задумываться о том какой применить формат — будет записано так же, как оно отображается в ячейке.

Все варианты решений можно посмотреть в примере :

Tips_Macro_CellValToVisual.xls (48,5 KiB, 679 скачиваний)

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

Поиск по меткам

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

Функция

ЯЧЕЙКА(

)

, английская версия CELL()

,

возвращает сведения о форматировании, адресе или содержимом ячейки. Функция может вернуть подробную информацию о формате ячейки, исключив тем самым в некоторых случаях необходимость использования VBA. Функция особенно полезна, если необходимо вывести в ячейки полный путь файла.

Синтаксис функции

ЯЧЕЙКА()

ЯЧЕЙКА(тип_сведений, [ссылка])

тип_сведений

— Текстовое значение, задающее требуемый тип сведений о ячейке. В приведенном ниже списке указаны возможные значения аргумента

тип_сведений

и соответствующие результаты.

ссылка —

Необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе

тип_сведений

, возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция

ЯЧЕЙКА()

возвращает сведения только для левой верхней ячейки диапазона.

Использование функции

В

файле примера

приведены основные примеры использования функции:

Большинство сведений об ячейке касаются ее формата. Альтернативным источником информации такого рода может случить только VBA.

Самые интересные аргументы это —

адрес

и

имяфайла

, которые позволяют быстро вывести в ячейке имени файла и путь к нему. Об этом читайте в статье

Нахождение имени текущей книги

.

Обратите внимание, что если в одном экземпляре MS EXCEL (см. примечание ниже) открыто несколько книг, то функция

ЯЧЕЙКА()

с аргументами

адрес

и

имяфайла

, будет отображать имя того файла, с который Вы изменяли последним. Например, открыто 2 книги в одном окне MS EXCEL:

Базаданных.xlsx

и

Отчет.xlsx.

В книге

Базаданных.xlsx

имеется формула

=ЯЧЕЙКА(«имяфайла»)

для отображения в ячейке имени текущего файла, т.е.

Базаданных.xlsx

(с полным путем и с указанием листа, на котором расположена эта формула). Если перейти в окно книги

Отчет.xlsx

и поменять, например, содержимое ячейки, то вернувшись в окно книги

Базаданных.xlsx

(

CTRL+TAB

) увидим, что в ячейке с формулой

=ЯЧЕЙКА(«имяфайла»)

содержится имя

Отчет.xlsx.

Это может быть источником ошибки. Хорошая новость в том, что при открытии книги функция пересчитывает свое значение (также пересчитать книгу можно нажав клавишу

F9

). При открытии файлов в разных экземплярах MS EXCEL — подобного эффекта не возникает — формула

=ЯЧЕЙКА(«имяфайла»)

будет возвращать имя файла, в ячейку которого эта формула введена.

Примечание

: Открыть несколько книг EXCEL можно в одном окне MS EXCEL (в одном экземпляре MS EXCEL) или в нескольких. Обычно книги открываются в одном экземпляре MS EXCEL (когда Вы просто открываете их подряд из Проводника Windows или через Кнопку Офис в окне MS EXCEL). Второй экземпляр MS EXCEL можно открыть запустив файл EXCEL.EXE, например через меню Пуск. Чтобы убедиться, что файлы открыты в одном экземпляре MS EXCEL нажимайте последовательно сочетание клавиш

CTRL+TAB

— будут отображаться все окна Книг, которые открыты в данном окне MS EXCEL. Для книг, открытых в разных окнах MS EXCEL (экземплярах MS EXCEL) это сочетание клавиш не работает. Удобно открывать в разных экземплярах Книги, вычисления в которых занимают продолжительное время. При изменении формул MS EXCEL пересчитывает только книги открытые в текущем экземпляре.

Другие возможности функции

ЯЧЕЙКА()

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

ЕТЕКСТ()

,

ЕЧИСЛО()

,

СТОЛБЕЦ()

и др.

Функция ЯЧЕЙКА() в MS EXCEL

​Смотрите также​Поставим пробел и выберем​ складывают или вычитают.​ например, для пользователя​ критерием лишь частично.​ аргумент ссылки указывает​=INDEX(1:5000,C2,C3)​A1​или​20​ его первому столбцу​ можно указать, какой​ а затем — клавишу​Аргументы функции ИНДЕКС описаны​

​Предположим, что нужно узнать,​

​ быть источником ошибки.​

​ в круглых скобках;​​Функция ЯЧЕЙКА(), английская версия​ любую ячейку из​ Например, в ячейках​ с ником sam​ Например, таблица Excel​ на диапазон ячеек,​​=ИНДЕКС(1:5000;C2;C3)​​.​

​R1C1​​Грецкие орехи​ и т. д.​ из них требуется​ ВВОД. При необходимости​ ниже.​​ все ли участники​​ Хорошая новость в​ во всех остальных​ CELL(), возвращает сведения​ данного диапазона:​ A1:A10 хранится последовательность​ это значение равно​ содержит столбец с​ функция ЯЧЕЙКА() возвращает​

​1:5000​​=ADDRESS($C$2,$C$3)​

Использование функции

​(тип_ссылки) в формуле,​ADDRESS​=ИНДЕКС(A2:C6;2;3)​

​ аргумента «номер_строки» или​    — обязательный аргумент. Ссылка​Яблоки​ одну строку или​ данными, причем данные​

​F9​ выровненному влево, кавычки​ тем самым в​ получим ошибку #ПУСТО!.​ 8 необходимо выполнить​ есть, числу пользователей​ указывает на любое​ учета работы сотрудников​ максимальным значением и​ то результатом будет​

​(АДРЕС) может возвратить​Пересечение второй строки и​ «номер_столбца» значение 0​ на один или​Лимоны​ один столбец, соответствующий​ могут не быть​). При открытии файлов​ («) — тексту, выровненному​ некоторых случаях необходимость​Данная ошибка означает, что​ следующие действия:​ на сайте. Полученное​ количество любых символов​ организации вида:​ используем функцию​ абсолютная ссылка.​ адрес ячейки или​ третьего столбца в​ (ноль), функция ИНДЕКС​ несколько диапазонов ячеек.​Бананы​ аргумент «номер_строки» или​ численными: например, некоторые​ в разных экземплярах​ вправо, знак крышки​​ использования VBA. Функция​​ ячейка не принадлежит​записать первую функцию СЧЁТЕСЛИ​ значение:​ до и после​Необходимо с помощью функции​ADDRESS​Чтобы увидеть адрес в​ работать в сочетании​ диапазоне A2:C6, т. е.​ возвратит ссылку на​​Если в качестве аргумента​​Груши​ «номер_столбца» не является​ участники могли ввести​ MS EXCEL -​ (^) — тексту, выровненному​ особенно полезна, если​ выделенной области.​ с критерием «>3»;​

​Для определения количества просмотренных​​ подстроки «Иванов».​ ЯЧЕЙКА вычислить в​(АДРЕС), чтобы получить​ виде относительной ссылки,​ с другими функциями,​ содержимое ячейки C3.​ целый столбец или​ «ссылка» используется несмежный​Формула​ обязательным.​ значения-заполнители, такие как​ подобного эффекта не​ по центру, обратная​ необходимо вывести в​Если выделить несколько ячеек​записать вторую функцию с​ страниц пользователями default​Пример 1. В таблице​ какой строке и​ её адрес.​ можно подставить в​ чтобы:​38​​ целую строку соответственно.​​ диапазон, его необходимо​Описание​Если массив содержит больше​ «TBD». Для выполнения​ возникает — формула =ЯЧЕЙКА(«имяфайла») будет​ косая черта () —​ ячейки полный путь​ внутри диапазона, функция​ критерием «>=8»;​ и user_1 запишем​ Excel содержатся данные​ столбце находится зарплата​Функция​ качестве аргумента​Получить адрес ячейки, зная​=ИНДЕКС((A1:C6;A8:C11);2;2;2)​Аргументы «номер_строки», «номер_столбца» и​

​ заключить в скобки​Результат​ одной строки и​ такой задачи можно​ возвращать имя файла,​ тексту с заполнением,​ файла.​

excel2.ru

Использование функции СЧЁТЗ для подсчета количества непустых ячеек

​ ОБЛАСТИ вернет количество​​определить разницу между возвращаемыми​ формулу:​ о продажах товаров​ размером 235000 руб.​MAX​abs_num​ номер строки и​Пересечение второй строки и​ «номер_области» должны указывать​Если каждая область в​=ИНДЕКС(A2:B3;2;2)​ одного столбца, а​ использовать функцию СЧЁТЗ.​ в ячейку которого​ пустой текст («») —​Синтаксис функции ЯЧЕЙКА()​ выделенных ячеек:​ значениями =СЧЁТЕСЛИ(A1:10;»>3″)-СЧЁТЕСЛИ(A1:A10;»>=8″). То​В результате расчета получим:​ в магазине бытовой​Для этого введем формулу​

​(МАКС) находит максимальное​(тип_ссылки) значение​ столбца.​ второго столбца во​ на ячейку внутри​ ссылке содержит только​Значение ячейки на пересечении​ из аргументов «номер_строки»​Пример.​ эта формула введена.​ любому другому содержимому​ЯЧЕЙКА(тип_сведений, [ссылка])​Описанные особенности работы данной​ есть, вычесть из​Функция имеет следующую синтаксическую​

​ техники за день.​

​ следующего вида:​ число в столбце​4​Найти значение ячейки, зная​ второй области (A8:C11),​ аргумента «ссылка»; в​ одну строку или​ второй строки и​ и «номер_столбца» задан​Эта функция подсчитывает только​Примечание​ ячейки.​тип_сведений​

1. ​ функции могут быть​ множества (3;+∞) подмножество​ запись:​ Определить, какую часть​тут:​

2. ​ C.​.​ номер строки и​ т. е. содержимое ячейки​

3. ​ противном случае функция​ один столбец, аргумент​ второго столбца в​ только один, функция​

   ​ ячейки с данными,​

​: Открыть несколько книг​»защита»​- Текстовое значение, задающее​ полезны при работе​ [8;+∞).​=СЧЕТЕСЛИ(диапазон; критерий)​ от проданной продукции​– «строка» и «строка»​

​=MAX(C3:C8)​=ADDRESS($C$2,$C$3,4)​ столбца.​ B9.​

​ ИНДЕКС возвращает значение​ «номер_строки» или «номер_столбца»​ диапазоне A2:B3.​ ИНДЕКС возвращает массив,​ но имейте в​

Другие способы подсчета количества ячеек с данными

• ​ EXCEL можно в​

• ​0, если ячейка разблокирована,​

• ​ требуемый тип сведений​ с большим количеством​

support.office.com

ИНДЕКС (функция ИНДЕКС)

​Функция ОБЛАСТИ в Excel​Описание аргументов:​ составляет техника фирмы​ – параметр вывода;​

​=МАКС(C3:C8)​=АДРЕС($C$2;$C$3;4)​

• ​Возвратить адрес ячейки с​1,25​ ошибки #ССЫЛКА!. Если​ соответственно является необязательным.​Груши​

• ​ состоящий из целой​ виду, что «данные»​ одном окне MS​ и 1, если​

Форма массива

Описание

​ о ячейке. В​ таблиц данных.​ используется для подсчета​диапазон – обязательный аргумент,​

​ Samsung.​– С8 – адрес​Далее в игру вступает​Чтобы задать стиль ссылок​

Синтаксис

​ самым большим значением.​

​=СУММ(ИНДЕКС(A1:C11;0;3;1))​ аргументы «номер_строки» и​

• ​ Например, для ссылки​​=ИНДЕКС(A2:B3;2;1)​ строки или целого​ могут включать пробелы,​

  • ​ EXCEL (в одном​ ячейка заблокирована.​ приведенном ниже списке​Функция находиться в категории​ числа областей, содержащихся​ принимающий ссылку на​

  • ​Вид исходной таблицы данных:​ данных с зарплатой.​ функция​R1C1​Функция​Сумма третьего столбца в​ «номер_столбца» опущены, функция​ на единственную строку​Значение ячейки на пересечении​ столбца аргумента «массив».​

• ​ которые не видны.​​ экземпляре MS EXCEL)​»строка»​ указаны возможные значения​ формул «Ссылки и​ в переданной ссылке,​ одну либо несколько​Для расчета используем формулу:​

• ​В результате вычислений получим:​​ADDRESS​, вместо принятого по​ADDRESS​ первой области диапазона​ ИНДЕКС возвращает область​ нужно использовать формулу​ второй строки и​

Замечания

• ​Номер_строки​ Да, в этом​ или в нескольких.​Номер строки ячейки в​ аргумента​ Массивы». Она имеет​ и возвращает соответствующее​

• ​ ячеек, в которых​=СЧЁТЕСЛИ(C3:C17;»Samsung»)/A17​ строка №8 и​(АДРЕС) в сочетании​ умолчанию стиля​(АДРЕС) имеет вот​ (A1:C11) является суммой​ в аргументе «ссылка»,​ ИНДЕКС(ссылка,,номер_столбца).​ первого столбца в​     — обязательный аргумент.​ примере пробелы можно​ Обычно книги открываются​ аргументе «ссылка».​тип_сведений​ следующую форму синтаксической​ значение. В Excel​ требуется определить число​

  ​Описание аргументов:​​ столбец №3 (С).​ с​A1​

• ​ такой синтаксис:​ диапазона C1:C11.​ заданную аргументом «номер_области».​Номер_строки​ диапазоне A2:B3.​ Выбирает строку в​ подсчитать самостоятельно, но​

Примеры

Пример 1

​ в одном экземпляре​»тип»​и соответствующие результаты.​ записи:​ областью является одна​ совпадений с указанным​

​C3:C17 – диапазон ячеек,​Пример 2. Нужно вычислить​MATCH​, Вы должны указать​ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])​216​Результатом вычисления функции ИНДЕКС​    — обязательный аргумент. Номер​Бананы​ массиве, из которой​ представьте, что книга​ MS EXCEL (когда​Текстовое значение, соответствующее типу​

Пример 2

​ проданной техники;​ символах. Сразу стоит​ номер строки, и​ для аргумента​abs_num​Сумма значений из диапазона,​ интерпретируется в качестве​

​ заданном аргументом «ссылка»,​ ИНДЕКС используется в​ Если аргумент «номер_строки»​ нужно выполнить указанные​ их подряд из​ Значение «b» соответствует​ о которой требуется​ссылка – обязательный для​Пример 1. Вернуть число,​ которому выполняется расчет​»Samsung» – критерий поиска​ отметить что в​

​ В зависимости от​​ возвратить ссылку.​ поиска значений двух​ является обязательным.​Определить диапазон ячеек, которые​ через Кнопку Офис​ — текстовой константе​ аргумент опущен, сведения,​ принимает ссылку на​ в диапазонах A1:B7,​ рассматриваемом диапазоне. Условием​A17 – ячейка, хранящая​ ширина столбцов и​ номер столбца.​=ADDRESS($C$2,$C$3,1,FALSE)​

​1​

Ссылочная форма

Описание

​ пересечением пятой строки​ формулы значение, возвращаемое​Номер_столбца​ заданных ячеек в​Номер_столбца​ нужно подсчитать. В​ в окне MS​ в ячейке, «v» —​ указанные в аргументе​

Синтаксис

​ одну или несколько​

​ C14:E19, D9, Пример2!A4:C6.​ могут являться логическое​

• ​ номер последней продажи,​​ ячеек измеряется в​=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))​=АДРЕС($C$2;$C$3;1;ЛОЖЬ)​

  • ​или вообще не​ и второго столбца​ функцией ИНДЕКС, может​    — необязательный аргумент. Номер​

  • ​ массиве с диапазоном​     — необязательный аргумент.​ приведенном примере это​ EXCEL). Второй экземпляр​ любому другому значению.​тип_сведений​ ячеек из указанного​Исходные данные на листе​ выражение, числовое значение,​ соответствующий общему числу​

• ​ количестве символов, которые​​=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))​Последний аргумент – это​ указано, то функция​ диапазона A2:A6, т. е.​ использоваться как ссылка​

• ​ столбца в диапазоне,​​ 2 x 2.​ Выбирает столбец в​ ячейки с B2​ MS EXCEL можно​»ширина»​

• ​, возвращаются для последней​​ диапазона.​ «Пример1»:​ текстовая строка, значение​ продаж.​ они умещают в​Урок подготовлен для Вас​ имя листа. Если​ возвратит абсолютный адрес​ сумма значений из​ или как значение.​ заданном аргументом «ссылка»,​В этом случае нужно​ массиве, из которого​ по D6.​ открыть запустив файл EXCEL.EXE,​Ширина столбца ячейки, округленная​ измененной ячейки. Если​Примечания:​Для подсчета количества областей​ типа Дата, ссылка​Результат расчета:​ своем значении доступны​ командой сайта office-guru.ru​ Вам необходимо это​ ($A$1). Чтобы получить​ диапазона B2:B6.​ Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2))​ из которого требуется​ выбрать любые две​ требуется возвратить значение.​Выделить ячейку, в которой​ например через меню Пуск.​

​ до целого числа.​ аргумент ссылки указывает​Аргументом рассматриваемой функции может​ используем формулу:​ на ячейку.​Доля проданной продукции техники​ для отображения в​Источник: http://blog.contextures.com/archives/2011/01/21/30-excel-functions-in-30-days-20-address/​ имя в полученном​

Замечания

• ​ относительный адрес (A1),​2,42​ эквивалентна формуле ЯЧЕЙКА(«ширина»;B1).​ возвратить ссылку.​ ячейки по вертикали,​ Если аргумент «номер_столбца»​ нужно отобразить результат.​ Чтобы убедиться, что​ Единица измерения равна​ на диапазон ячеек,​ являться только ссылка​Результат вычисления функции является​Примечания:​ фирмы Samsung в​ ячейке без переноса​Перевел: Антон Андронов​

• ​ результате, укажите его​ используйте значение​К началу страницы​ Функция ЯЧЕЙКА использует​Номер_области​ например A1:A2, вставить​ опущен, аргумент «номер_строки»​

• ​ Назовем ее ячейкой​ файлы открыты в​ ширине одного знака​ функция ЯЧЕЙКА() возвращает​ на диапазон ячеек.​ ошибка #ЗНАЧ!, поскольку​При подсчете числа вхождений​ процентах составляет –​ строки.​Автор: Антон Андронов​ в качестве аргумента​4​

• ​Вчера в марафоне​ значение, возвращаемое функцией​    — необязательный аргумент. Диапазон​ в первую ячейку​ является обязательным.​ результата.​ одном экземпляре MS​ для шрифта стандартного​ сведения только для​ Если было передано​ диапазон «Пример2!A4:C6» находится​ в диапазон в​ 40%.​Примечание. Высота строк и​Очень часто при работе​sheet_text​. Остальные варианты:​30 функций Excel за​ ИНДЕКС, как ссылку.​ в аргументе «ссылка»,​

​ формулу =ИНДЕКС({1;2:3;4};0;2) и​​Если используются оба аргумента​Ввести формулу в ячейке​ EXCEL нажимайте последовательно​

Примеры

​ размера.​ левой верхней ячейки​ текстовое или числовое​ на другом листе.​ соответствии с двумя​​ ячеек в Excel​ в Excel необходимо​(имя_листа).​2​ 30 дней​ С другой стороны,​ из которого требуется​

​ этого была предусмотрена​

support.office.com

30 функций Excel за 30 дней: АДРЕС (ADDRESS)

​ADDRESS​​a1​MATCH​​ в число в​ аргументами «номер_строки» и​ ее во вторую​​ пересечении указанных строки​​Можно также подсчитать ячейки​ открыты в данном​ касаются ее формата.​Ссылка на первую ячейку​ ошибка».​​ полученных значений в​​ непересекающихся подмножеств. Например,​​ о количестве студентов,​​ Чем больше шрифт,​

​ функция ЯЧЕЙКА. Рассмотрим​(АДРЕС) возвращает адрес​– если TRUE​​(ПОИСКПОЗ) и обнаружили,​​ ячейке B1.​ «номер_столбца». Первая введенная​ ячейку.​ и столбца.​ из нескольких диапазонов.​ окне MS EXCEL.​ Альтернативным источником информации​ в аргументе «ссылка»​В качестве аргумента ссылка​ результате выполнения функций​

​ в таблице «Мебель»​ сдавших предмет на​​ тем выше строка​​ ее использование на​ ячейки в виде​ (ИСТИНА) или вообще​ что она отлично​Примечание:​ или выделенная область​Формула​

Функция 20: ADDRESS (АДРЕС)

​Если указать в качестве​​ В приведенном ниже​​ Для книг, открытых​ такого рода может​ в виде текстовой​ могут быть переданы​ ОБЛАСТИ для подсчета​ необходимо найти количество​ 5, 4, 3​ для полного отображения​​ конкретных примерах.​​ текста, а не​​ не указано, функция​​ работает в команде​  Функция ЯЧЕЙКА недоступна​ имеет номер 1, вторая —​Описание​

Как можно использовать функцию ADDRESS (АДРЕС)?

​ аргумента «номер_строки» или​​ примере подсчитываются ячейки​​ в разных окнах​ случить только VBA.​ строки.​ несколько диапазонов ячеек.​ количества областей в​

• ​ столов и стульев.​ балла соответственно, а​ символов по высоте.​
• ​Стоит отметить, что в​ как действующую ссылку.​ возвращает ссылку в​
• ​ с другими функциями,​ в Excel Web​

Синтаксис ADDRESS (АДРЕС)

​ 2 и т. д.​​Результат​​ «номер_столбца» значение 0​ в диапазонах с​

​ MS EXCEL (экземплярах​
​Самые интересные аргументы это​

• ​»столбец»​​ Для этого необходимо​ диапазонах A1:B7;C14:E19;D9 и​​ Для вычислений используем​​ также тех, кто​Введем в ячейку С14​ Excel используются несколько​ Если Вам нужно​ стиле​ такими как​​ App.​​ Если аргумент «номер_области»​​=ИНДЕКС({1;2​​ (ноль), функция ИНДЕКС​​ B2 по D6​​ MS EXCEL) это​
• ​ — адрес и​​Номер столбца ячейки в​ использовать еще по​ Пример2!A4:C6 соответственно. Результат:​ выражение =СЧЁТЕСЛИ(B3:B200;»*стол*»)+СЧЁТЕСЛИ(B3:B200;»*стул*»).​ не сдал предмет.​​ формулу для вычисления​​ функций по адресации​ получить значение ячейки,​​A1​​VLOOKUP​
• ​Скопируйте образец данных из​​ опущен, в функции​​:​ возвратит массив значений​ и с B9​ сочетание клавиш не​ имяфайла, которые позволяют​ аргументе «ссылка».​

Ловушки ADDRESS (АДРЕС)

​ одной открывающей и​​С помощью такой не​​Если в качестве критерия​Вид исходной таблицы:​ суммы ширины каждого​ ячеек:​ можно использовать результат,​, если FALSE (ЛОЖЬ),​(ВПР) и​​ следующей таблицы и​​ ИНДЕКС используется область 1. ​3;4};0;2)​ для целого столбца​ по D13.​

Пример 1: Получаем адрес ячейки по номеру строки и столбца

​ работает. Удобно открывать​​ быстро вывести в​​»цвет»​ закрывающей скобки (в​ хитрой формулы мы​ указана текстовая строка,​Предварительно выделим ячейки E2:E5,​ столбца таблицы:​– СТРОКА;​ возвращаемый функцией​ то в стиле​INDEX​​ вставьте их в​​ Все указанные здесь​

​Значение ячейки на пересечении​
​ или целой строки​

Абсолютная или относительная

​Excel выделит диапазоны ячеек,​ в разных экземплярах​​ ячейке имени файла​​1, если ячейка изменяет​ этом случае Excel​ получили правильный результат.​

​ следует учитывать, что​ введем приведенную ниже​тут:​– СТОЛБЕЦ и другие.​​ADDRESS​​R1C1​​(ИНДЕКС).​​ ячейку A1 нового​

​ области должны находиться​
​ первой строки и​

A1 или R1C1

​ соответственно. Чтобы использовать​​ а при нажатии​​ Книги, вычисления в​ и путь к​​ цвет при выводе​​ не будет распознавать​​ регистр символов не​​ формулу:​​– «ширина» – параметр​

​Функция ЯЧЕЙКА(), английская версия​
​(АДРЕС), как аргумент​

Название листа

​.​20-й день нашего марафона​ листа Excel. Чтобы​ на одном листе. ​ второго столбца в​ значения, возвращенные как​​ клавиши ВВОД появится​​ которых занимают продолжительное​

​ нему. Об этом​
​ отрицательных значений; во​

Пример 2: Находим значение ячейки, используя номер строки и столбца

​ символ «;» как​​Пример 2. Определить количество​​ имеет значения. Например,​=СЧЁТЕСЛИ(B3:B19;D2:D5)​ функции;​ CELL(), возвращает сведения​ для​sheet​ мы посвятим изучению​ отобразить результаты формул,​​ Если указать области,​​ массиве. Массив содержит​ массив, введите функцию​​ результат:​​ время. При изменении​ читайте в статье​​ всех остальных случаях —​​ разделитель аргументов в​ столбцов в таблице​​ функция СЧЁТЕСЛИ(A1:A2;»Петров») вернет​Описание аргументов:​​– А1 – ширина​

​ о форматировании, адресе​
​INDIRECT​

​_text​​ функции​​ выделите их и​ находящиеся на разных​​ значения 1 и​​ ИНДЕКС как формула​Если известно, что нужно​ формул MS EXCEL​​ Нахождение имени текущей​​ 0 (ноль).​ функции. Например, результатом​​ и записать это​​ значение 2, если​B3:B19 – диапазон ячеек​

​ определенного столбца.​
​ или содержимом ячейки.​

​(ДВССЫЛ). Мы изучим​​(имя_листа) – имя​​ADDRESS​ нажмите клавишу F2,​ листах, произойдет ошибка​ 2 в первой​

​ массива в горизонтальный​
​ учесть только числа​

​ пересчитывает только книги открытые в​​ книги.​»содержимое»​ выполнения функции с​

Пример 3: Возвращаем адрес ячейки с максимальным значением

​ значение в ячейку​ в ячейках A1​ с оценками за​Пример 3. В условии​​ Функция может вернуть​​ функцию​ листа может быть​

​(АДРЕС). Она возвращает​​ а затем — клавишу​​ #ЗНАЧ!  Если необходимо​ строке и значения​ диапазон ячеек для​

​ и даты, но​
​ текущем экземпляре.​

​Обратите внимание, что если​Значение левой верхней ячейки​​ указанными аргументами: ((A1:C5;E1:H12))​​ A16.​ и A2 записаны​​ экзамен;​​ примера 1 нужно​ подробную информацию о​​INDIRECT​​ указано, если Вы​ адрес ячейки в​

​ ВВОД. При необходимости​
​ использовать диапазоны, находящиеся​

​ 3 и 4​ строки и в​
​ не текстовые данные,​
​Другие возможности функции ЯЧЕЙКА():​

​ в одном экземпляре​

office-guru.ru

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

​ в ссылке; не​ будет значение 2,​Таблица:​ строки «петров» и​D2:D5 – диапазон ячеек,​ вывести содержимое только​ формате ячейки, исключив​(ДВССЫЛ) позже в​ желаете видеть его​

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

​ текстовом формате, используя​ измените ширину столбцов,​ на разных листах,​ во второй строке.​

• ​ вертикальный — для столбца.​
• ​ используйте функцию СЧЕТ.​

​ определение типа значения,​ MS EXCEL (см.​ формула.​ поскольку в качестве​Используем формулу ОБЛАСТИ, поочередно​ «Петров» соответственно.​ содержащих критерии для​ из первой (верхней​ тем самым в​ рамках марафона​ в возвращаемом функцией​ номер строки и​ чтобы видеть все​ рекомендуется применить функцию​

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

​2​ Чтобы ввести формулу​Подсчет символов в ячейках​ номера столбца или​

​ примечание ниже) открыто​

1. ​»имяфайла»​ аргумента переданы два​ выделяя каждый столбец​Если в качестве аргумента​ подсчета числа совпадений.​ левой) ячейки из​ некоторых случаях необходимость​30 функций Excel за​
2. ​ результате.​ столбца. Нужен ли​ данные.​ ИНДЕКС в форме​Значение ячейки на пересечении​ массива, нажмите сочетание​Подсчет количества вхождений значения​ строки, мало востребованы,​ несколько книг, то​Имя файла (включая полный​ диапазона ячеек.​ ячейки в качестве​ критерий передана ссылка​В результате получим таблицу:​

​ диапазона A5:C8.​

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

​ использования VBA. Функция​ 30 дней​Функция​

​ нам этот адрес?​Фрукты​ массива и использовать​ второй строки и​ клавиш CTRL+SHIFT+ВВОД.​

​Подсчет уникальных значений среди​ т.к. дублируются стандартными​

​ функция ЯЧЕЙКА() с​

• ​ путь), содержащего ссылку,​Если аргумент рассматриваемой функции​
• ​ параметра. Перед выбором​ на пустую ячейку​

​Пример 3. В таблице​Введем формулу для вычисления:​ особенно полезна, если​

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

​.​ADDRESS​ Можно ли сделать​Цена​ другую функцию для​ второго столбца в​Примечание:​ повторяющихся​ функциями ЕТЕКСТ(), ЕЧИСЛО(),​ аргументами адрес и имяфайла, будет отображать​ в виде текстовой​ ссылается на диапазон​ последующего столбца нажимаем​

​ или пустая строка​ Excel хранятся данные​Описание формулы аналогичное предыдущим​ необходимо вывести в​=INDIRECT(ADDRESS(C2,C3))​(АДРЕС) возвращает лишь​ то же самое​Количество​ вычисления диапазона, составляющего​ массиве, указанном выше.​

​  В Excel Web​Функция ИНДЕКС возвращает значение​ СТОЛБЕЦ() и др.​ имя того файла,​

​ строки. Если лист,​

• ​ ячеек, находящихся на​ и удерживаем кнопку​
• ​ «», результат вычисления​ о просмотрах страниц​

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

​ двум примерам.​ ячейки полный путь​=ДВССЫЛ(АДРЕС(C2;C3))​ адрес ячейки в​ с помощью других​Яблоки​

​ массив.  Например, определить​

​4​ App невозможно создавать​

exceltable.com

Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel

​ или ссылку на​Примечание:​ с который Вы​ содержащий ссылку, еще​ еще не созданном​ Ctrl. Если добавить​ для любого диапазона​ сайта за день​Функция СЧЁТЕСЛИ в Excel​ файла.​

​Функция​ виде текстовой строки.​ функций?​0,69 ₽​ используемый диапазон можно​Примечание:​ формулы массива.​ значение из таблицы​Мы стараемся как​ изменяли последним. Например,​ не был сохранен,​ листе, Excel предложит​ символ «)» и​ ячеек будет числовое​ пользователями. Определить число​ используется для подсчета​Функция ЯЧЕЙКА в своей​INDIRECT​ Если Вам нужно​Давайте обратимся к сведениям​40​ с помощью функции​ Это формула массива, которую​Аргументы «номер_строки» и «номер_столбца»​

Примеры использования функции СЧЁТЕСЛИ в Excel

​ или диапазона.​ можно оперативнее обеспечивать​ открыто 2 книги​ возвращается пустая строка​ создать лист с​ нажать Enter, появится​ значение 0 (нуль).​ пользователей сайта за​ количества ячеек в​

​ работе использует синтаксис,​

​(ДВССЫЛ) может работать​

​ значение ячейки, используйте​

​ по функции​

• ​Бананы​ ВЫБОР.​ нужно вводить с​
• ​ должны указывать на​Функцию ИНДЕКС можно использовать​
• ​ вас актуальными справочными​ в одном окне​ («»).​ указанным именем и​

​ диалоговое окно с​

​Функция может быть использована​ день, а также​ рассматриваемом диапазоне, содержащиеся​ который состоит из​

​ и без функции​

Подсчет количества определенного значения ячейки в Excel при условии

​ её в качестве​ADDRESS​0,34 ₽​Например, если аргумент «ссылка»​ помощью клавиш CTRL+SHIFT+ВВОД.​ ячейку внутри заданного​ двумя способами:​ материалами на вашем​ MS EXCEL: Базаданных.xlsx​»формат»​

​ сохранить книгу.​

​ сообщением о том,​ в качестве формулы​ сколько раз за​

​ данные в которых​

​ двух аргументов:​

• ​ADDRESS​ аргумента функции​(АДРЕС) и изучим​
• ​38​ определяет ячейки (A1:B4,D1:E4,G1:H4),​ Excel автоматически заключит​

​ массива; в противном​

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

​Если вы хотите возвращать​ языке. Эта страница​ и Отчет.xlsx. В книге Базаданных.xlsx имеется​Текстовое значение, соответствующее числовому​Если некоторые ячейки, например,​ что было введено​ массива, если требуется​ день на сайт​ соответствуют критерию, переданному​ЯЧЕЙКА(тип_сведений, [ссылка])​(АДРЕС). Вот как​INDIRECT​

​ примеры работы с​

​Лимоны​ номер области 1​ формулу в фигурные​ случае функция ИНДЕКС​ значение указанной ячейки​ переведена автоматически, поэтому​ формула =ЯЧЕЙКА(«имяфайла») для​ формату ячейки. Значения​ A1 и B1​ слишком много аргументов.​

​ выполнить расчет числа​ заходили пользователи с​ в качестве второго​Тип_сведений – текстовое значение,​ можно, используя оператор​(ДВССЫЛ) или примените​ ней. Если у​0,55 ₽​ соответствует диапазону A1:B4,​ скобки {}. Если​ возвратит значение ошибки​ или массива ячеек,​ ее текст может​

​ отображения в ячейке​ для различных форматов​ были объединены, при​ Добавим дополнительные открывающую​

​ ячеек с данными,​

Особенности использования функции СЧЁТЕСЛИ в Excel

​ логинами default и​ аргумента данной функции,​

​ задающее требуемый тип​

​ конкатенации «​

• ​ одну из альтернативных​ Вас есть дополнительная​15​ номер области 2​ вы попытаетесь ввести​ #ССЫЛКА!.​ см. раздел Форма​
• ​ содержать неточности и​ имени текущего файла,​ показаны ниже в​ выделении полученной ячейки​ и закрывающую скобки.​ удовлетворяющим сразу нескольким​ user_1.​ и возвращает соответствующее​ сведений о ячейке.​

​&​

1. ​ формул, показанных в​ информация или примеры,​Апельсины​ — диапазону D1:E4,​ их вручную, Excel​В этих примерах функция​ массива.​ грамматические ошибки. Для​ т.е. Базаданных.xlsx (с полным путем​ таблице. Если ячейка​ в строке имен​Результат вычислений:​ критериям. Данная особенность​
2. ​Вид исходной таблицы:​ числовое значение.​ При вводе функции​«, слепить нужный адрес​ примере 2.​ пожалуйста, делитесь ими​0,25 ₽​ а номер области​ отобразит формулу как​ ИНДЕКС используется для​Если требуется возвращать ссылку​
3. ​ нас важно, чтобы​ и с указанием​ изменяет цвет при​ будет отображено имя​Пример 3. Определить, принадлежит​ будет рассмотрена в​Поскольку каждый пользователь имеет​Функция СЧЁТЕСЛИ может быть​
4. ​ вручную высвечивается выпадающий​ в стиле​При помощи функции​ в комментариях.​25​ 3 — диапазону​ текст. Если при​ поиска значения ячейки,​ на указанные ячейки,​
5. ​ эта статья была​ листа, на котором​ выводе отрицательных значений,​ «A1». Несмотря на​ ли ячейка заданному​ одном из примеров.​ свой уникальный идентификатор​ использована для анализа​ список где указаны​R1C1​ADDRESS​Функция​Груши​ G1:H4.​ вводе формулы не​ находящейся на пересечении​ см. раздел Ссылочная​ вам полезна. Просим​ расположена эта формула).​ в конце текстового​

• ​ объединение ячеек функция​ диапазону ячеек.​
• ​Рассматриваемая функция может быть​ в базе данных​
• ​ числовых значений, текстовых​ все возможные значения​и в результате​(АДРЕС) Вы можете​ADDRESS​

exceltable.com

Примеры использования функции ОБЛАСТИ для диапазонов Excel

​0,59 ₽​После того как с​ нажать клавиши CTRL+SHIFT+ВВОД,​ заданных строки и​ форма.​ вас уделить пару​ Если перейти в​ значения добавляется «-».​ с аргументами ((A1;B1))​

Примеры работы функции ОБЛАСТИ в Excel для работы с диапазонами ячеек

​Рассматриваемая функция также позволяет​ использована для определения​ (Id), выполним расчет​ строк, дат и​

​ аргумента «тип сведений»:​ получить значение ячейки:​

​ получить адрес ячейки​(АДРЕС) возвращает ссылку​

​40​ помощью аргументов «ссылка»​ она возвратит ошибку​ столбца.​

​Возвращает значение элемента таблицы​ секунд и сообщить,​ окно книги Отчет.xlsx и поменять,​

​ Если положительные или​ все равно вернет​ определить, принадлежит ли​ количества совпадений как​ числа пользователей сайта​ данных другого типа.​Ссылка – необязательный аргумент.​

​=INDIRECT(«R»&C2&»C»&C3,FALSE)​ в виде текста,​ на ячейку в​

​Миндаль​

Как посчитать количество ссылок на столбцы таблицы Excel

​ и «номер_области» выбран​ #ЗНАЧ!​Скопируйте образец данных из​ или массив, заданного​ помогла ли она​

​ например, содержимое ячейки,​

​ все числа отображаются​ значение 2. Эта​ ячейка выделенной области.​ по одному, так​ за день по​ С ее помощью​ Ячейка, сведения о​=ДВССЫЛ(«R»&C2&»C»&C3;ЛОЖЬ)​ используя номер строки​ виде текста, основываясь​2,80 ₽​ диапазон, с помощью​К началу страницы​ следующей таблицы и​ номером строки и​

​ вам, с помощью​

Определение принадлежности ячейки к диапазону таблицы

​ то вернувшись в​ в круглых скобках,​ особенность показана на​

​ Выполним следующие действия:​ и сразу по​ следующей формуле массива​ можно определить количество​

1. ​ которой требуется получить.​Функция​ и столбца. Если​ на номере строки​10​
2. ​ аргументов «номер_строки» и​Возвращает ссылку на ячейку,​ вставьте их в​
3. ​ номером столбца.​ кнопок внизу страницы.​ окно книги Базаданных.xlsx (​
4. ​ в конце текстового​ рисунке ниже:​В какой-либо ячейке введем​

​ нескольким критериям поиска.​ и для ее​ неповторяющихся значений в​

​ Если этот аргумент​INDEX​ Вы введёте только​ и столбца. Она​

​Кешью​ «номер_столбца» выбирается конкретная​ расположенную на пересечении​ ячейку A1 нового​Если первый аргумент функции​

Особенности использования функции ОБЛАСТИ в Excel

​ Для удобства также​CTRL+TAB​ значения добавляется «()».​Функция возвращает значения даже​ часть формулы «=ОБЛАСТИ((»​

​ В последнем случае​

​ вычислений нажмем комбинацию​

​ИНДЕКС(массив; номер_строки; [номер_столбца])​

Синтаксис:

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

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

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

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 выделена полужирным шрифтом:

Code

ПОЛУЧИТЬ.ЯЧЕЙКУ(20, Лист1!$B$4)

Информацию, возвращенную функцией ПОЛУЧИТЬ.ЯЧЕЙКУ можно использовать для определения действия. Следующая макроформула выполняет пользовательскую функцию с именем BoldCell, если формула ПОЛУЧИТЬ.ЯЧЕЙКУ возвращает значение ЛОЖЬ:

Code

ЕСЛИ(ПОЛУЧИТЬ.ЯЧЕЙКУ(20; Лист1!$B$4); ; BoldCell())

В 4-ый день марафона 30 функций Excel за 30 дней мы получали детальные сведения о рабочей среде, используя функцию INFO (ИНФОРМ), – такие как версия Excel и режим пересчёта.

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

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

Функция 11: CELL (ЯЧЕЙКА)

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

Как можно использовать функцию CELL (ЯЧЕЙКА)?

Функция CELL (ЯЧЕЙКА) может сообщить о ячейке информацию следующего содержания:

• Числовой формат ячейки.
• Название листа.
• Выравнивание или ширину столбца.

Синтаксис CELL (ЯЧЕЙКА)

Функция CELL (ЯЧЕЙКА) имеет вот такой синтаксис:

CELL(info_type,reference)
ЯЧЕЙКА(тип_сведений;ссылка)

info_type (тип_сведений) — это один из вариантов аргумента:

• address (адрес) – ссылка на первую ячейку в аргументе reference (ссылка) в текстовом формате.
• col (столбец) – номер столбца ячейки в аргументе reference (cсылка).
• сolor (цвет) – возвращается 1, если форматированием ячейки предусмотрено изменение цвета для отрицательных значений; во всех остальных случаях возвращается 0 (ноль).
• contents (содержимое) – значение левой верхней ячейки в ссылке.
• filename (имяфайла) – имя файла и полный путь.
• format (формат) – числовой формат ячейки.
• parentheses (скобки) – возвращает 1, если форматированием ячейки предусмотрено отображение положительных или всех чисел в круглых скобках; во всех остальных случаях возвращает 0 (ноль).
• prefix (префикс) – текстовое значение, соответствующее префиксу метки ячейки (показывает тип выравнивания).
• protect (защита) — 0 = ячейка не заблокирована, 1 = заблокирована.
• row (строка) – номер строки ячейки.
• type (тип) – тип данных в ячейке (пустая, текст, другое).
• width (ширина) – ширина столбца ячейки.

Ловушки функции CELL (ЯЧЕЙКА)

Есть несколько моментов, за которыми нужно внимательно следить, используя функцию CELL (ЯЧЕЙКА):

• Если аргумент reference (ссылка) опущен, то результат возвращается для последней изменённой ячейки. Чтобы быть уверенным, что результат получился именно тот, что Вам нужен, ссылку желательно всегда указывать. Вы можете сослаться даже ячейку, в которой содержится сама функция CELL (ЯЧЕЙКА).
• При работе с функцией CELL (ЯЧЕЙКА), иногда необходимо делать пересчёт листа, чтобы обновить результат, который возвращает функция.
• Если в качестве аргумента info_type (тип_сведений) выбрано значение filename (имяфайла), а книга Excel ещё не сохранена, то результатом будет пустая строка.

Пример 1: Формат числа в ячейке

Со значением format (формат) Вы можете использовать функцию CELL (ЯЧЕЙКА), чтобы показать числовой формат ячейки. Например, если ячейка B7 имеет формат General (Общий), то результатом формулы будет G:

=CELL("format",C2)
=ЯЧЕЙКА("формат";C2)

Пример 2: Название листа

Со значением filename (имяфайла) функция CELL (ЯЧЕЙКА) покажет путь к файлу, имя файла и название листа.

=CELL("filename",B2)
=ЯЧЕЙКА("имяфайла";B2)

Из полученного результата при помощи других функций Вы можете извлечь название листа. В формуле ниже, используя функции MID (ПСТР) и FIND (НАЙТИ), находим квадратные скобки и возвращаем 32 символа, которые следуют за ними (длина названия листа ограничена 31 символом).

=MID(CELL("filename",C3),FIND("]",CELL("filename",C3))+1,32)
=ПСТР(ЯЧЕЙКА("имяфайла";C3);НАЙТИ("]";ЯЧЕЙКА("имяфайла";C3))+1;32)

Пример 3: Подстановка аргумента info_type (тип_сведений) из выпадающего списка

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

Когда выбрано значение protect (защита): результат равен 1, если ячейка заблокирована, или 0 (ноль), если не заблокирована.

=CELL(B4,B2)
=ЯЧЕЙКА(B4;B2)

Когда выбрано значение width (ширина), результат показывает ширину столбца в формате целого числа. Единицей измерения в этом случае является ширина одного символа при стандартном размере шрифта.