Как выражение a1 a10 интерпретируется в Excel

Обновлено: 24.11.2024

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

Читайте Excel 2013 по запросу или более 24 000 других книг и видео в Safari Books Online. Начните бесплатную пробную версию сегодня.

Эта глава из книги

Эта глава из книги

Эта глава из книги 

  • Понимание формул
  • Создание и редактирование формул
  • Ссылки на ячейки
  • Использовать абсолютные ссылки на ячейки
  • Используйте смешанные и трехмерные ссылки на ячейки
  • Название ячеек и диапазонов
  • Ввод имен и управление ими
  • Упрощение формулы с помощью диапазонов
  • Отображение вычислений в строке состояния
  • Расчет итогов с помощью автосуммы
  • Выполнить однократные вычисления
  • Преобразование формул и значений
  • Исправить ошибки в расчетах
  • Правильные формулы
  • Аудит рабочего листа
  • Найти циклические ссылки
  • Выполнение вычислений с использованием функций
  • Создание функций и вложенных функций
  • Расчет нескольких результатов
  • Использование констант и функций в именах

Введение

После того как вы введете данные на лист, вы захотите добавить формулы для выполнения расчетов. Microsoft Excel может помочь вам получить нужные результаты. Формулы могут быть как очень простыми, так и более сложными. Сложность формулы зависит от сложности результата, который вы хотите получить от своих данных. Например, если вы просто хотите подсчитать объем продаж за этот месяц, формула добавит ваш номер продаж и предоставит результат. Однако если вы хотите показать продажи в этом месяце на сумму более 100 долларов США с постоянными клиентами, вам потребуется немного больше времени для разработки формулы.

Поскольку Excel автоматически пересчитывает формулы, ваши листы остаются точными и актуальными независимо от того, как часто вы меняете данные. Использование абсолютных ссылок на ячейки привязывает формулы к конкретной ячейке. Excel предоставляет множество встроенных функций для добавления к вычислениям на листе. Такие функции, как СРЗНАЧ или СУММ, позволяют выполнять быстрый расчет по формуле.

Еще один способ упростить понимание формул – использовать в них диапазоны имен. Диапазоны имен — группа выбранных ячеек, называемых диапазоном, — могут помочь вам понять ваши более сложные формулы. Гораздо проще прочитать формулу, в которой используются диапазоны имен, чем посмотреть на формулу и попытаться ее расшифровать. Excel предлагает инструмент для аудита вашего рабочего листа. Глядя на «поток» вашей формулы, вы значительно уменьшаете количество ошибок в расчетах. Вы можете увидеть, как строится ваша формула, по одному уровню за раз с помощью ряда стрелок, которые указывают, откуда формула извлекает данные. По мере разработки формулы вы можете вносить в нее исправления.

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

Excel для Интернета делает это с помощью формул в ячейках. Формула выполняет вычисления или другие действия с данными на листе. Формула всегда начинается со знака равенства (=), за которым могут следовать числа, математические операторы (например, знак плюс или минус) и функции, которые действительно расширяют возможности формулы.

Например, следующая формула умножает 2 на 3, а затем добавляет к этому результату 5, чтобы получить ответ 11.

В следующей формуле используется функция ПЛТ для расчета платежа по ипотеке (1073,64 долл. США), основанного на процентной ставке 5 % (5 %, разделенные на 12 месяцев, равняется месячной процентной ставке) за 30-летний период (360 месяцев). ) для кредита в размере 200 000 долларов США:

Вот несколько дополнительных примеров формул, которые можно ввести на листе.

=A1+A2+A3 Складывает значения в ячейках A1, A2 и A3.

=SQRT(A1) Использует функцию SQRT для возврата квадратного корня из значения в A1.

=TODAY() Возвращает текущую дату.

=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции листа ПРОПИСН.

=IF(A1>0) Проверяет ячейку A1, чтобы определить, содержит ли она значение больше 0.

Части формулы

Формула также может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.

<р>1. Функции: функция PI() возвращает значение числа пи: 3,142.

<р>2. Ссылки: A2 возвращает значение в ячейке A2.

<р>3. Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2.

<р>4.Операторы: оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает числа.

Использование констант в формулах

Использование операторов вычисления в формулах

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

Типы операторов

Существует четыре различных типа операторов вычисления: арифметические операции, сравнение, конкатенация текста и ссылка.

Арифметические операторы

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

Арифметический оператор

Операторы сравнения

Вы можете сравнить два значения с помощью следующих операторов. Когда два значения сравниваются с помощью этих операторов, результатом является логическое значение — либо ИСТИНА, либо ЛОЖЬ.

Оператор сравнения

> (знак больше)

= (знак больше или равно)

Больше или равно

(не равно знаку)

Оператор объединения текста

Используйте амперсанд (&), чтобы соединить (объединить) одну или несколько текстовых строк, чтобы получить единый фрагмент текста.

Текстовый оператор

Соединяет или объединяет два значения для создания одного непрерывного текстового значения

"Север"&"ветер" приводит к "Борей"

Ссылочные операторы

Объедините диапазоны ячеек для вычислений с помощью следующих операторов.

Оператор ссылки

Оператор диапазона, который создает одну ссылку на все ячейки между двумя ссылками, включая две ссылки.

Оператор объединения, который объединяет несколько ссылок в одну ссылку

Оператор пересечения, создающий одну ссылку на ячейки, общие для двух ссылок

Порядок, в котором Excel для Интернета выполняет операции в формулах

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

Порядок расчета

Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равенства (=). Excel в Интернете интерпретирует символы, следующие за знаком равенства, как формулу. После знака равенства следуют вычисляемые элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычисления. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.

Приоритет оператора

Если вы объединяете несколько операторов в одной формуле, Excel в Интернете выполняет операции в порядке, указанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом (например, если формула содержит оператор умножения и деления), Excel в Интернете оценивает операторы слева направо.

Описание

Отрицание (как в –1)

Умножение и деление

Сложение и вычитание

Соединяет две строки текста (объединение)

Использование скобок

Чтобы изменить порядок вычисления, заключите в круглые скобки ту часть формулы, которая будет вычисляться первой. Например, следующая формула дает 11, так как Excel в Интернете выполняет умножение перед сложением. Формула умножает 2 на 3, а затем добавляет к результату 5.

Наоборот, если вы используете круглые скобки для изменения синтаксиса, Excel для Интернета суммирует 5 и 2, а затем умножает результат на 3, чтобы получить 21.

В следующем примере круглые скобки, заключающие первую часть формулы, заставляют Excel для Интернета сначала вычислить B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.< /p>

Использование функций и вложенных функций в формулах

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

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

Следующий пример функции ОКРУГЛ, округляющей число в ячейке A10, иллюстрирует синтаксис функции.

<р>1. Структура. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающая скобка, аргументы функции, разделенные запятыми, и закрывающая скобка.

<р>2. Имя функции. Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите SHIFT+F3.

<р>4. Подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, введите =ROUND( и появится всплывающая подсказка. Подсказки появляются только для встроенных функций.

Ввод функций

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

Чтобы упростить создание и редактирование формул и свести к минимуму опечатки и синтаксические ошибки, используйте автозаполнение формул. После того как вы введете = (знак равенства) и начальные буквы или триггер отображения, Excel в Интернете отобразит под ячейкой динамический раскрывающийся список допустимых функций, аргументов и имен, которые соответствуют буквам или триггеру. Затем вы можете вставить элемент из раскрывающегося списка в формулу.

Вложенные функции

В некоторых случаях вам может понадобиться использовать функцию в качестве одного из аргументов другой функции. Например, следующая формула использует вложенную функцию СРЗНАЧ и сравнивает результат со значением 50.

<р>1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Ограничения уровня вложенности Формула может содержать до семи уровней вложенности функций. Когда одна функция (назовем ее Функцией Б) используется в качестве аргумента в другой функции (назовем ее Функцией А), Функция Б действует как функция второго уровня. Например, функция СРЗНАЧ и функция СУММ являются функциями второго уровня, если они используются в качестве аргументов функции ЕСЛИ. Функция, вложенная во вложенную функцию СРЗНАЧ, становится функцией третьего уровня и т. д.

Использование ссылок в формулах

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

Справочный стиль A1

Стили ссылок по умолчанию По умолчанию Excel в Интернете использует стиль ссылок A1, который ссылается на столбцы с буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с номерами (от 1 до 1 048 576). Эти буквы и цифры называются заголовками строк и столбцов. Чтобы сослаться на ячейку, введите букву столбца, а затем номер строки. Например, B2 относится к ячейке на пересечении столбца B и строки 2.

Для ссылки

Ячейка в столбце A и строке 10

Диапазон ячеек в столбце А и строках с 10 по 20

Диапазон ячеек в строке 15 и столбцах с B по E

Все ячейки в строке 5

Все ячейки в строках с 5 по 10

Все ячейки в столбце H

Все ячейки в столбцах с H по J

Диапазон ячеек в столбцах от A до E и строках с 10 по 20

Создание ссылки на другой лист В следующем примере функция листа AVERAGE вычисляет среднее значение для диапазона B1:B10 на листе Marketing в той же книге.

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10 включительно

<р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Разница между абсолютными, относительными и смешанными ссылками

Относительные ссылки Относительная ссылка на ячейку в формуле, например A1, основана на относительном положении ячейки, содержащей формулу, и ячейки, на которую ссылается ссылка. Если положение ячейки, содержащей формулу, изменяется, ссылка изменяется. Если вы скопируете или заполните формулу между строками или столбцами, ссылка будет автоматически скорректирована. По умолчанию в новых формулах используются относительные ссылки. Например, если вы скопируете или заполните относительную ссылку из ячейки B2 в ячейку B3, она автоматически изменится с =A1 на =A2.

Абсолютные ссылки Абсолютная ссылка на ячейку в формуле, например $A$1, всегда указывает на ячейку в определенном месте. Если положение ячейки, содержащей формулу, изменяется, абсолютная ссылка остается прежней. Если вы скопируете или заполните формулу между строками или столбцами, абсолютная ссылка не изменится. По умолчанию в новых формулах используются относительные ссылки, поэтому вам может потребоваться переключить их на абсолютные ссылки. Например, если вы скопируете или заполните абсолютную ссылку из ячейки B2 в ячейку B3, она останется одинаковой в обеих ячейках: =$A$1.

Смешанные ссылки Смешанная ссылка имеет либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку принимает форму A$1, B$1 и т. д. Если положение ячейки, содержащей формулу, изменяется, относительная ссылка изменяется, а абсолютная ссылка не изменяется.Если вы копируете или заполняете формулу по строкам или столбцам, относительная ссылка корректируется автоматически, а абсолютная ссылка не корректируется. Например, если вы скопируете или заполните смешанную ссылку из ячейки A2 в ячейку B3, она изменится с =A$1 на =B$1.

Трехмерный эталонный стиль

Удобные ссылки на несколько листов Если вы хотите анализировать данные в одной и той же ячейке или диапазоне ячеек на нескольких листах в книге, используйте трехмерную ссылку. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, которому предшествует диапазон имен рабочих листов. Excel в Интернете использует все листы, хранящиеся между начальным и конечным именами ссылки. Например, =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5, на всех листах между листами 2 и 13 включительно.

Трехмерные ссылки можно использовать для ссылки на ячейки на других листах, для определения имен и создания формул с помощью следующих функций: СУММ, СРЗНАЧ, СРЗНАЧ, СЧЕТ, СЧЕТ, МАКС, МАКС, МИН, МИН, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA и VARPA.

Объемные ссылки нельзя использовать в формулах массива.

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

Что происходит при перемещении, копировании, вставке или удалении листов В следующих примерах показано, что происходит при перемещении, копировании, вставке или удалении листов, включенных в трехмерную ссылку. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для добавления ячеек с A2 по A5 на листах со 2 по 6.

Вставка или копирование Если вы вставляете или копируете листы между Листами2 и Лист6 (конечными точками в этом примере), Excel в Интернете включает в расчеты все значения в ячейках с A2 по A5 из добавленных листов.

Удалить. Если вы удалите листы между Листами2 и Лист6, Excel в Интернете удалит их значения из расчета.

Переместить. Если вы перемещаете листы между Листами2 и Лист6 в место за пределами указанного диапазона листов, Excel в Интернете удаляет их значения из расчета.

Перемещение конечной точки. Если вы перемещаете Лист2 или Лист6 в другое место в той же книге, Excel в Интернете корректирует расчет, чтобы учесть новый диапазон листов между ними.

Удалить конечную точку. Если вы удаляете Лист2 или Лист6, Excel в Интернете корректирует расчет, чтобы учесть диапазон листов между ними.

Стиль ссылок R1C1

Вы также можете использовать справочный стиль, в котором и строки, и столбцы на листе пронумерованы. Справочный стиль R1C1 полезен для вычисления позиций строк и столбцов в макросах. В стиле R1C1 Excel для Интернета указывает расположение ячейки буквой "R", за которой следует номер строки, и буквой "C", за которой следует номер столбца.

Относительная ссылка на ячейку двумя строками выше и в том же столбце

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

Абсолютная ссылка на ячейку во второй строке и во втором столбце

Относительная ссылка на всю строку над активной ячейкой

Абсолютная ссылка на текущую строку

При записи макроса Excel в Интернете записывает некоторые команды, используя стиль ссылок R1C1. Например, если вы записываете команду, например нажатие кнопки "Автосумма", чтобы вставить формулу, которая добавляет диапазон ячеек, Excel в Интернете записывает формулу, используя стиль R1C1, а не стиль A1, ссылки.

Использование имен в формулах

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

СЧЁТЕСЛИ — это функция Excel для подсчёта ячеек в диапазоне, удовлетворяющих одному условию. СЧЁТЕСЛИ можно использовать для подсчёта ячеек, содержащих даты, числа и текст. Критерии, используемые в COUNTIF, поддерживают логические операторы (>, ,=) и подстановочные знаки (*,?) для частичного совпадения.

  • range — диапазон ячеек для подсчета.
  • критерии — критерии, определяющие, какие ячейки следует учитывать.

Функция СЧЁТЕСЛИ в Excel подсчитывает количество ячеек в диапазоне, соответствующих одному заданному условию. Критерии могут включать логические операторы (>, ,=) и подстановочные знаки (*,?) для частичного совпадения. Критерии также могут быть основаны на значении из другой ячейки, как описано ниже.

СЧЁТЕСЛИ входит в группу из восьми функций Excel, которые разбивают логические критерии на две части (диапазон + критерии). В результате синтаксис, используемый для построения критериев, отличается, и СЧЁТЕСЛИ требуется диапазон ячеек, вы не можете использовать массив.

COUNTIF поддерживает только одиночное условие. Если вам нужно применить несколько критериев, используйте функцию СЧЁТЕСЛИМН. Если вам нужно манипулировать значениями в аргументе диапазон как часть логической проверки, см. функции СУММПРОИЗВ и/или ФИЛЬТР.

Базовый пример

На листе, показанном выше, в ячейках G5, G6 и G7 используются следующие формулы:

Обратите внимание, что COUNTIF не учитывает регистр, "CA" и "ca" обрабатываются одинаково.

Двойные кавычки ("") в критериях

Как правило, текстовые значения необходимо заключать в двойные кавычки (""), а числа - нет. Однако, когда логический оператор включен в число, число и оператор должны быть заключены в кавычки, как показано во втором примере ниже:

Значение из другой ячейки

Значение из другой ячейки можно включить в критерии с помощью конкатенации. В приведенном ниже примере функция СЧЕТЕСЛИ вернет количество значений в ячейке A1:A10, которые меньше значения в ячейке B1. Обратите внимание, что оператор «меньше» (текст) заключен в кавычки.

Не равно

Чтобы создать критерий "не равно", используйте оператор "<>", заключенный в двойные кавычки (""). Например, приведенная ниже формула будет подсчитывать ячейки не равные «красному» в диапазоне A1:A10:

Пустые ячейки

СЧЁТЕСЛИ может подсчитывать пустые или непустые ячейки. Приведенные ниже формулы подсчитывают пустые и непустые ячейки в диапазоне A1:A10:

Даты

Самый простой способ использовать СЧЁТЕСЛИ с датами — сослаться на допустимую дату в другой ячейке с помощью ссылки на ячейку. Например, для подсчета ячеек в A1:A10, содержащих дату, превышающую дату в B1, можно использовать следующую формулу:

Обратите внимание, что мы должны объединить оператор с датой в ячейке B1. Чтобы использовать более сложные критерии дат (например, все даты в данном месяце или все даты между двумя датами), вам нужно переключиться на функцию СЧЁТЕСЛИМН, которая может обрабатывать несколько критериев.

Самый безопасный способ жестко закодировать дату в СЧЁТЕСЛИ — использовать функцию ДАТА. Это гарантирует, что Excel поймет дату. Для подсчета ячеек в A1:A10, содержащих дату менее 1 апреля 2020 года, можно использовать следующую формулу

Подстановочные знаки

В критериях можно использовать знак вопроса (?), звездочку (*) или тильду (~). Знак вопроса (?) соответствует любому одному символу, а звездочка (*) соответствует нулю или более символов любого типа. Например, для подсчета ячеек в формате A1:A5, содержащих текст «яблоко», можно использовать следующую формулу:

Чтобы подсчитать ячейки в A1:A5, содержащие любые 3 текстовых символа, вы можете использовать:

Тильда (~) – это escape-символ, соответствующий подстановочным знакам. Например, чтобы подсчитать буквальный вопросительный знак (?), звездочку (*) или тильду (~), добавьте тильду перед подстановочным знаком (например, ~?, ~*, ~~).

логика ИЛИ

Функция СЧЁТЕСЛИ предназначена для применения только одного условия. Однако для подсчета ячеек, содержащих «это ИЛИ то», вы можете использовать константу массива и функцию СУММ следующим образом:

Приведенная выше формула подсчитывает ячейки в диапазоне, которые содержат "красный" или "синий". По сути, СЧЁТЕСЛИ возвращает два числа в массиве (одно для «красного» и одно для «синего»), а функция СУММ возвращает сумму. Для получения дополнительной информации см. этот пример.

Примечания

  • СЧЁТЕСЛИ регистр не учитывается. Используйте функцию EXACT для подсчета с учетом регистра.
  • COUNTIF поддерживает только одно условие. Используйте функцию СЧЁТЕСЛИМН для нескольких критериев.
  • Текстовые строки в критериях должны быть заключены в двойные кавычки (""), например, "apple", ">32", "ja*"
  • Ссылки на ячейки в критериях не заключены в кавычки, т. е. "Расчет требуемых часов работы волонтера

Есть два требования, которые должны быть выполнены, и оба они оцениваются в одном операторе AND. Первое требование – не менее 5 часов волонтерской работы в каждой из трех категорий: A, B и C. Это требование.

Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, соответствующих заданным критериям. В этом случае критерии предоставляются как «>0», что оценивается как «значения больше нуля». СЧЁТЕСЛИ возвращает число.

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

СЧЕТЕСЛИ подсчитывает количество ячеек в диапазоне, соответствующих заданным критериям. Если вы попытаетесь использовать СЧЕТЕСЛИ с несколькими диапазонами, разделенными запятыми, вы получите сообщение об ошибке. Одним из решений является запись диапазонов в виде текста в файле.

В Excel оператором не равно является "". Например: =A1<>10 // A1 не равно 10 =A1<>"яблоко" // A1 не равно "яблоку" Функция СЧЁТЕСЛИ подсчитывает количество ячеек.

Функция ФИЛЬТР принимает массив значений и аргумент "включить", который фильтрует массив на основе логического выражения или значения. В этом случае массив предоставляется как именованный диапазон "list1", который содержит.

Основой этой формулы является СЧЁТЕСЛИ, которая возвращает ноль, если ни одна из подстрок не найдена, и положительное число, если найдена хотя бы одна подстрока. Изюминка в этом случае в том, что мы даем COUNTIF больше, чем один.

В этой формуле используется СЧЁТЕСЛИ с расширяющимся диапазоном, чтобы сначала проверить, является ли текущая строка первым вхождением данного номера счета-фактуры: СЧЁТЕСЛИ($B$5:B5,B5)=1 Это выражение возвращает ИСТИНА, только если это первое вхождение. .

Цель этого примера – вернуть минимальное уникальное значение, т. е. минимальное значение, которое встречается в данных только один раз. Функция UNIQUE, новая в Excel 365, возвращает уникальный список значений из набора.

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

При работе с данными обычно требуется выполнять сводные вычисления, которые по-разному отображают общее количество. Например, общее количество по категории, цвету, размеру, статусу и т. д. Функция СЧЁТЕСЛИ — хороший способ.

В этом примере цель состоит в том, чтобы рассчитать количество и процентное соотношение для каждой категории, показанной в столбце B. Для удобства значения категории в столбце B находятся в категории именованного диапазона (B5:B122). Для подсчета мы.

Правила проверки данных срабатывают, когда пользователь добавляет или изменяет значение ячейки. В этом примере мы используем формулу, которая проверяет, что входные данные еще не существуют в именованном диапазоне «электронные письма»: СЧЁТЕСЛИ(ids,B5)

Эта формула разрывает ничью с помощью простого подхода: первая ничья в списке "выигрывает" и получает более высокий ранг. В первой части формулы обычно используется функция RANK: =RANK(C5,points) Rank возвращает a.

При использовании формулы для применения условного форматирования формула оценивается относительно активной ячейки в выделении во время создания правила. В этом случае правило оценивается для каждой ячейки в ячейке B4:G12.

Похожие видео

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

Сводные таблицы — это отличный инструмент для обобщения данных, но вы также можете использовать формулы для создания собственных сводок, используя такие функции, как СЧЁТЕСЛИ и СУММЕСЛИ. Посмотрите, как это сделать, в этом 3-минутном видео.

В этом видео мы рассмотрим, как отображать более 3000 ответов на вопрос в гистограмме Excel.

В этом видео мы рассмотрим, как использовать функцию СЧЕТЕСЛИ для решения распространенной проблемы: как найти значения в одном списке, которые появляются в другом списке.

В этом видео мы рассмотрим, как создать динамический именованный диапазон с таблицей. Это самый простой способ создать динамический именованный диапазон в Excel.

В этом видео мы рассмотрим, как составить список повторяющихся значений с помощью функции ФИЛЬТР. Другими словами, значения, которые встречаются в наборе данных более одного раза.

Функция Excel AVERAGE вычисляет среднее (среднее арифметическое) предоставленных чисел. СРЗНАЧ может обрабатывать до 255 отдельных аргументов, включая числа, ссылки на ячейки, диапазоны, массивы и константы.

  • число1 – число или ссылка на ячейку, которая относится к числовым значениям.
  • число2 – [необязательно] число или ссылка на ячейку, которая ссылается на числовые значения.

Функция СРЗНАЧ вычисляет среднее значение чисел, переданных в качестве аргументов. Чтобы вычислить среднее значение, Excel суммирует все числовые значения и делит их на количество числовых значений.

СРЗНАЧ принимает несколько аргументов в виде число1, число2, число3 и т. д., всего до 255 аргументов. Аргументы могут включать числа, ссылки на ячейки, диапазоны, массивы и константы. Пустые ячейки и ячейки, содержащие текст или логические значения, игнорируются. Однако включаются нулевые (0) значения. Вы можете игнорировать нулевые (0) значения с помощью функции СРЗНАЧЕСЛИМН, как описано ниже.

Функция СРЗНАЧ игнорирует логические значения и числа, введенные в виде текста. Если вам нужно включить эти значения в среднее значение, см. функцию СРЗНАЧA.

Если значения, переданные функции СРЗНАЧ, содержат ошибки, функция СРЗНАЧ возвращает ошибку. Вы можете использовать функцию АГРЕГАТ, чтобы игнорировать ошибки.

Основное использование

Обычным способом использования функции СРЗНАЧ является указание диапазона, как показано ниже. Формула в F3, скопированная вниз, выглядит так:

В каждой новой строке СРЗНАЧ вычисляет среднее значение результатов теста для каждого человека.

Пустые ячейки

Функция СРЗНАЧ автоматически игнорирует пустые ячейки. Обратите внимание, что на приведенном ниже экране ячейка C4 пуста, СРЗНАЧ просто игнорирует ее и вычисляет среднее значение только с ячейками B4 и D4:

Однако обратите внимание, что нулевое (0) значение в C5 включено в среднее значение, поскольку оно является допустимым числовым значением. Чтобы исключить нулевые значения, используйте вместо них СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН. В приведенном ниже примере СРЗНАЧЕСЛИ используется для исключения нулевых значений. Как и функция СРЗНАЧ, функция СРЗНАЧЕСЛИ автоматически исключает пустые ячейки.

Смешанные аргументы

Числа, предоставляемые функции AVERAGE, могут быть как ссылками, так и константами:

В среднем по критериям

Чтобы вычислить среднее значение с критериями, используйте СРЗНАЧЕСЛИ или СРЗНАЧСЛИМН. В приведенном ниже примере функция СРЗНАЧЕСЛИМН используется для расчета среднего балла для красной и синей групп:

Функция СРЗНАЧЕСЛИМН также может применять несколько критериев.

В среднем топ-3

Комбинируя функцию СРЗНАЧ с функцией НАИБОЛЬШИЙ, вы можете вычислить среднее значение первых n значений. В приведенном ниже примере формула в столбце I вычисляет среднее значение трех лучших результатов викторины в каждой строке:

Средневзвешенное значение

Чтобы вычислить взвешенное среднее значение, используйте функцию СУММПРОИЗВ, как показано ниже:

Если количество числовых значений равно нулю, функция ЕСЛИ возвращает пустую строку (""). Когда счетчик больше нуля, функция СРЗНАЧ возвращает среднее значение. Этот пример объясняет эту идею более подробно.

Усреднение вручную

Для расчета среднего значения функция СРЗНАЧ суммирует все числовые значения и делит их на количество числовых значений. Это поведение можно воспроизвести с помощью функций SUM и COUNT вручную следующим образом:

Читайте также: