Top-office11.ru

IT и мир ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Формула ячейка excel

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

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

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

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

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

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

Тип_ сведений Возвращаемое значение
«адрес»Ссылка на первую ячейку в аргументе «ссылка» в виде текстовой строки.
«столбец»Номер столбца ячейки в аргументе «ссылка».
«цвет»1, если ячейка изменяет цвет при выводе отрицательных значений; во всех остальных случаях — 0 (ноль).
«содержимое»Значение левой верхней ячейки в ссылке; не формула.
«имяфайла»Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка («»).
«формат»Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется «-». Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется «()».
«скобки»1, если положительные или все числа отображаются в круглых скобках; во всех остальных случаях — 0.
«префикс»Текстовое значение, соответствующее префиксу метки ячейки. Апостроф (‘) соответствует тексту, выровненному влево, кавычки («) — тексту, выровненному вправо, знак крышки (^) — тексту, выровненному по центру, обратная косая черта () — тексту с заполнением, пустой текст («») — любому другому содержимому ячейки.
«защита»0, если ячейка разблокирована, и 1, если ячейка заблокирована.
«строка»Номер строки ячейки в аргументе «ссылка».
«тип»Текстовое значение, соответствующее типу данных в ячейке. Значение «b» соответствует пустой ячейке, «l» — текстовой константе в ячейке, «v» — любому другому значению.
«ширина»Ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера.

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

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

Большинство сведений об ячейке касаются ее формата. Альтернативным источником информации такого рода может случить только 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

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

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

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

= Если (ЯЧЕЙКа («тип»; a1) = «v»; a1 * 2; 0)

Эта формула вычисляет произведение A1*2, только если в ячейке A1 содержится числовое значение, и возвращает значение 0, если в ячейке A1 содержится текст или она пустая.

Синтаксис

Аргументы функции ЯЧЕЙКА описаны ниже.

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

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

значения info_type

В следующем списке описаны текстовые значения, которые можно использовать для аргумента info_type. Эти значения должны быть введены в функцию ЯЧЕЙКИ с кавычками («»).

Ссылка на первую ячейку в аргументе «ссылка» в виде текстовой строки.

Номер столбца ячейки в аргументе «ссылка».

1, если форматированием ячейки предусмотрено изменение цвета для отрицательных значений; во всех остальных случаях — 0 (ноль).

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Значение левой верхней ячейки в ссылке; не формула.

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

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется «-«. Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется «()».

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

1, если форматированием ячейки предусмотрено отображение положительных или всех чисел в круглых скобках; во всех остальных случаях — 0.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Текстовое значение, соответствующее префиксу метки ячейки. Одиночная кавычка (‘) соответствует тексту, выровненному влево, двойная кавычка («) — тексту, выровненному вправо, знак крышки (^) — тексту, выровненному по центру, обратная косая черта () — тексту, распределенному по всей ширине ячейки, а пустой текст («») — любому другому содержимому ячейки.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

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

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Номер строки ячейки в аргументе «ссылка».

Текстовое значение, соответствующее типу данных в ячейке. Значение «b» соответствует пустой ячейке, «l» — текстовой константе в ячейке, «v» — любому другому содержимому.

Возвращает массив с двумя элементами.

Первый элемент в массиве — это ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера.

Второй элемент массива — это логическое значение, равно TRUE, если ширина столбца является значением по умолчанию или FALSE, если ширина была явно задана пользователем.

Примечание: Это значение не поддерживается в Excel в Интернете, Excel Mobile и Excel Starter.

Коды форматов функции ЯЧЕЙКА

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

Формат Microsoft Excel

Значение, возвращаемое функцией ЯЧЕЙКА

10 формул Excel, которые пригодятся каждому

Не нужно тратить время на подсчёты в Microsoft Excel вручную, ведь существует множество формул, которые помогут быстро справиться с поставленными задачами и повысить точность ваших отчётов. Мы собрали 10 наиболее полезных формул, которые вы сможете выучить за один день.

Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

Читать еще:  Как делать графики в excel

Функция СУММ позволяет вычислить сумму двух или более чисел. В этой формуле вы также можете использовать ссылки на ячейки.

С помощью формулы вы можете:

  • посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
  • посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
  • посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

Англоязычный вариант: =COUNT(A1:A10)

Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

СЧЁТЗ

Англоязычный вариант: =COUNTA(A1:A10)

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

ДЛСТР

Англоязычный вариант: =LEN(A1)

Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.

СЖПРОБЕЛЫ

Англоязычный вариант: =TRIM(A1)

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

Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.

ЛЕВСИМВ, ПСТР и ПРАВСИМВ

=ЛЕВСИМВ(адрес_ячейки; количество знаков)

=ПРАВСИМВ(адрес_ячейки; количество знаков)

=ПСТР(адрес_ячейки; начальное число; число знаков)

Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)

Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)

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

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

  1. В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
  2. В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)
  • B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
  • A1:B13 = таблица, в которой находится искомое значение.
  • 2 – номер столбца, где находится искомое значение.
  • ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.

Эта формула не такая простая, как предыдущие, тем не менее она очень полезна в работе.

Формула: =ЕСЛИ(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)

Англоязычный вариант: =IF(логическое_выражение; «текст, если логическое выражение истинно; «текст, если логическое выражение ложно»)

Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

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

Читать еще:  Как настроить дату в excel

В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:

=ЕСЛИ(B1>C1; «Норма выполнена»; «Норма не выполнена»)

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

СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)

=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)

Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)

Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.

Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.

Используя функции на предыдущем примере, мы можем узнать:

СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.

СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.

СЧЁТЕСЛИ – количество продавцов, выполнивших норму.

Конкатенация

Формула: =(ячейка1&» «&ячейка2)

За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&» «&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между » «, чтобы между объединёнными данными появился пробел.

Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;» «; B1) или в англоязычном варианте =concatenate(A1;» «; B1).

Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

Эта статья является лишь верхушкой айсберга в изучении Excel. Для профессионального использования программы рекомендуем учится у профессионалов на курсах по Microsoft Excel.

Функция ЯЧЕЙКА в 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.

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

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

Ссылка на основную публикацию
Adblock
detector