Запись типа 3e 4 в ячейке calc excel означает

Обновлено: 03.07.2024

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

Одной из лучших функций ссылок на ячейки в Excel является трехмерная ссылка или размерная ссылка, как ее еще называют.

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

Что такое 3D-справка в Excel?

Как отмечалось выше, ссылка Excel 3D позволяет ссылаться на одну и ту же ячейку или диапазон ячеек на нескольких листах. Другими словами, он ссылается не только на диапазон ячеек, но и на диапазон имен рабочих листов. Ключевым моментом является то, что все листы, на которые ссылаются, должны иметь один и тот же шаблон и один и тот же тип данных. Рассмотрим следующий пример.

Предположим, что у вас есть ежемесячные отчеты о продажах на 4 разных листах:

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

Но что, если у вас есть 12 листов на весь год или еще больше листов на несколько лет? Это было бы очень много работы. Вместо этого вы можете использовать функцию СУММ с 3D-привязкой для суммирования по листам:
=СУММ(Янв:Апр!В6)

Эта формула СУММ выполняет те же вычисления, что и более длинная формула выше, т. е. суммирует значения в ячейке B6 на всех листах между двумя указанными граничными рабочими листами, Янв и Апр. в этом примере:

Совет. Если вы собираетесь скопировать трехмерную формулу в несколько ячеек и не хотите, чтобы ссылки на ячейки менялись, вы можете заблокировать их, добавив знак $, т. е. используя абсолютные ссылки на ячейки, такие как =СУММ(Янв:Апр!$ 6 долларов США).

Вам даже не нужно вычислять промежуточный итог в каждом ежемесячном листе — включите диапазон ячеек, которые будут вычисляться, непосредственно в вашей 3D-формуле:

=СУММ(Янв:Апр!B2:B5)

Если вы хотите узнать общий объем продаж по каждому отдельному продукту, составьте сводную таблицу, в которой элементы отображаются точно в том же порядке, что и в ежемесячных листах, и введите следующую трехмерную формулу в самом верхнем ячейка, B2 в этом примере:

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

На основе приведенных выше примеров создадим общий трехмерный справочник Excel и трехмерную формулу.

Справочник по Excel 3D

Трехмерная формула Excel

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

Как создать трехмерную ссылку в Excel

Чтобы создать формулу с трехмерной ссылкой, выполните следующие действия:

1. Нажмите на ячейку, в которую хотите ввести 3D-формулу.
2. Введите знак равенства (=), введите имя функции и введите открывающую скобку, например. =СУММ(
3. Перейдите на вкладку первого рабочего листа, который вы хотите включить в трехмерную ссылку.
4. Удерживая нажатой клавишу Shift, щелкните вкладку последнего листа, который нужно включить в трехмерный образец.
5. Выберите ячейку или диапазон ячеек, которые вы хотите вычислить.
6. Введите оставшуюся часть формулы как обычно.
7. Нажмите клавишу Enter, чтобы завершить трехмерную формулу Excel.

Как включить новый лист в формулу Excel 3D

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

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

Для этого создайте пустой лист, например Декабрь, и сделайте его последним листом в 3D-образце:

Когда в книгу вставляется новый лист, просто переместите его в любое место между январем и декабрем:

Вот оно! Поскольку ваша формула СУММ содержит трехмерную ссылку, она суммирует указанный диапазон ячеек (B2:B5) на всех листах в пределах указанного диапазона имен рабочих листов (янв:декабрь!). Просто помните, что все листы, включенные в справочник Excel 3D, должны иметь одинаковую компоновку данных и один и тот же тип данных.

Как создать имя для трехмерной ссылки Excel

Чтобы вам было еще проще использовать 3D-формулы в Excel, вы можете создать определенное имя для своего 3D-образца.

1. На вкладке Формулы перейдите в группу Определенные имена и щелкните Определить имя.
   
2. В диалоговом окне Новое имя введите осмысленное и легко запоминающееся имя в поле Имя длиной до 255 символов. В этом примере пусть это будет что-то очень простое, скажем, my_reference.
3. Удалите содержимое поля Ссылки на, а затем введите трехмерную ссылку следующим образом:
   • Тип = (знак равенства).
   • Удерживая нажатой клавишу Shift, щелкните вкладку первого листа, на который вы хотите сослаться, а затем щелкните последний лист.
   • Выберите ячейку или диапазон ячеек, на которые нужно сослаться. Вы также можете сослаться на весь столбец, щелкнув букву столбца на листе.

А теперь, чтобы просуммировать числа в столбце B на всех листах с января по апреля, используйте эту простую формулу:

Функции Excel, поддерживающие трехмерные ссылки

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

СУММ – суммирует числовые значения.

СРЕДНИЙ — вычисляет среднее арифметическое чисел.

СРЗНАЧА – вычисляет среднее арифметическое значений, включая числа, текст и логические операции.

COUNT – подсчет ячеек с числами.

COUNTA — подсчитывает непустые ячейки.

MAX – возвращает наибольшее значение.

MAXA – возвращает наибольшее значение, включая текст и логические значения.

MIN – находит наименьшее значение.

MINA – находит наименьшее значение, включая текст и логические значения.

PRODUCT — умножает числа.

СТАНДОТКЛОН, СТАНДОТКЛОН, СТАНДОТКЛОН, СТАНДОТКЛОНПА – расчет выборочного отклонения заданного набора значений.

VAR, VARA, VARP, VARPA — возвращает примерную дисперсию указанного набора значений.

Как меняются ссылки Excel 3-D при вставке, перемещении или удалении листов

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

Поскольку почти все легче понять на примере, дальнейшие объяснения будут основываться на следующей трехмерной формуле, которую мы создали ранее:

Вставляйте, перемещайте или копируйте листы в конечных точках. Если вы вставите, скопируете или переместите рабочие листы между конечными точками 3D-справки (в этом примере листы Jan и Apr), указанный диапазон (ячейки B2–B5) во всех вновь добавленных листы будут включены в расчеты.

Удалите листы или переместите листы за пределы конечных точек. Когда вы удаляете какие-либо рабочие листы между конечными точками или перемещаете листы за пределы конечных точек, такие листы исключаются из вашей 3D-формулы.

Переместить конечную точку.Если вы переместите любую конечную точку (лист Jan или Apr или оба листа) в новое место в той же книге, Excel изменит вашу трехмерную формулу, чтобы включить новые листы. которые попадают между конечными точками, и исключить те, которые выпадают из конечных точек.

Поменять местами конечные точки. Обращение опорных конечных точек Excel 3D приводит к изменению одного из листов конечных точек. Например, если вы переместите начальный лист (Январь) после конечного листа (Апр), лист Январь будет удален из списка 3. -D ссылка, которая изменится на Feb:Apr!B2:B5.

Перемещение конечного листа (апрель) перед начальным листом (январь) будет иметь аналогичный эффект. В этом случае лист Апрель будет исключен из 3D-эталона, который изменится на Январь:Мар!B2:B5.

Обратите внимание, что восстановление исходного порядка конечных точек не приведет к восстановлению исходной трехмерной ссылки. В приведенном выше примере, даже если мы переместим лист Jan обратно на первую позицию, 3D-ссылка останется Feb:Apr!B2:B5, и вам придется отредактировать ее вручную, чтобы включить Ян в своих расчетах.

Удалить конечную точку. Когда вы удаляете один из листов конечных точек, он удаляется из 3D-привязки, а удаленная конечная точка изменяется следующим образом:

• Если первый лист удален, конечная точка изменится на следующий за ним лист. В этом примере, если лист Jan удаляется, 3D-ссылка меняется на Feb:Apr!B2:B5.
• Если последний лист удален, конечная точка изменится на предыдущий лист. В этом примере при удалении листа Апрель 3D-ссылка изменится на Янв:Мар!B2:B5.

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

На сегодня все. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

поиск меню

Урок 2. Простые формулы

Введение

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

Посмотрите видео ниже, чтобы узнать, как создавать формулы в Excel.

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

Excel использует стандартные операторы для формул, такие как знак "плюс" для сложения (+), знак "минус" для вычитания (-), звездочка для умножения (*), косая черта для деления (/) и знак вставки. (^) для показателей степени.

Стандартные операторы

Все формулы в Excel должны начинаться со знака равенства (=). Это связано с тем, что ячейка содержит или равна формуле и вычисляемому ею значению.

Ссылки на ячейки

Хотя вы можете создавать простые формулы в Excel вручную (например, =2+2 или =5*5), в большинстве случаев для создания формулы вы будете использовать адреса ячеек. Это называется созданием ссылки на ячейку. Использование ссылок на ячейки гарантирует, что ваши формулы всегда будут точными, поскольку вы можете изменить значение ячеек, на которые ссылаетесь, без необходимости переписывать формулу.

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

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

Примеры простых формул

Чтобы создать формулу:

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

Выберите ячейку, которая будет содержать формулу. В нашем примере мы выберем ячейку B3.

Выбор ячейки B3

Ввод знака =

Ссылка на ячейку B1

Ссылка на ячейку B2

Полная формула и расчетное значение

Изменение значений со ссылками на ячейки

Истинное преимущество ссылок на ячейки заключается в том, что они позволяют обновлять данные на листе без необходимости переписывать формулы. В приведенном ниже примере мы изменили значение ячейки B1 с 1200 до 1800 долларов. Формула в ячейке B3 автоматически пересчитает и отобразит новое значение в ячейке B3.

Пересчитанное значение ячейки

Excel не всегда сообщит вам, если ваша формула содержит ошибку, поэтому вы должны проверить все свои формулы. Чтобы узнать, как это сделать, вы можете прочитать урок «Перепроверьте свои формулы» из нашего учебника по формулам Excel.

Чтобы создать формулу с помощью метода «укажи и щелкни»:

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

Выберите ячейку, которая будет содержать формулу. В нашем примере мы выберем ячейку D3.

Выбор ячейки D3

Ссылка на ячейку B3

Ссылка на ячейку C3

Заполненная формула и вычисленное значение

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

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

Чтобы изменить формулу:

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

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

Выбор ячейки B3

Выбор формулы для редактирования

Неуместная ссылка на ячейку

Редактирование формулы

Новое рассчитанное значение

Если вы передумаете, вы можете нажать клавишу Esc на клавиатуре, чтобы случайно не внести изменения в формулу.

Чтобы отобразить все формулы в электронной таблице, вы можете, удерживая клавишу Ctrl, нажать ` (ударение). Клавиша серьезного акцента обычно находится в верхнем левом углу клавиатуры. Вы можете снова нажать Ctrl+`, чтобы вернуться к обычному виду.

Если вы новичок в 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 с числами, текстом, пробелами или ячейками на основе определенных критериев. Используйте функции СРЗНАЧ, СРЗНАЧ, СРЗНАЧСЛИ или СРЗНАЧЕСЛИМН или ТРИММЕЗНАЧ для получения среднего значения, исключающего выбросы.

Средние значения ячеек с числами -- AVERAGE

Функция СРЗНАЧ усредняет ячейки, содержащие числа. Его синтаксис:
=СРЗНАЧ(значение1, значение2. значение30).
Аргументы (например, значение1) могут быть ссылками на ячейки или значениями, введенными в формулу СРЗНАЧ.

В следующем примере функции СРЗНАЧ используется один аргумент — ссылка на ячейки A1:A5.

1. Введите образец данных на лист.
2. В ячейке A7 введите формулу СРЗНАЧ для усреднения чисел в столбце А: =СРЗНАЧ(A1:A5)

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

Среднее число ячеек с данными -- AVERAGEA

Функция СРЗНАЧА усредняет непустые ячейки. Его синтаксис:
=СРЗНАЧA(значение1, значение2. значение30).
Аргументы (например, значение1) могут быть ссылками на ячейки или значениями, введенными в формулу. В следующем примере используется один аргумент — ссылка на ячейки A1:A5.

1. Введите образец данных на лист.
2. В ячейке A7 введите формулу СРЗНАЧА, чтобы усреднить числа в столбце А: =СРЗНАЧА(A1:A5)

Примечание. СРЗНАЧА будет включать ячейки с формулами, в том числе те, которые выглядят пустыми, потому что они оцениваются как "", например. =ЕСЛИ(B2="","",B2).

Среднее число ячеек, соответствующих критериям -- СРЗНАЧЕСЛИ

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

Точное соответствие критерию

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

1. Выберите ячейку, в которой вы хотите увидеть среднее значение (в данном примере ячейка A12).
2. Введите знак равенства (=), чтобы начать формулу.
3. Тип: СРЗНАЧЕСЛИ(
4. Выберите ячейки, содержащие значения для проверки критерия. В этом примере будут проверены ячейки A1:A10
5. Введите запятую, чтобы разделить аргументы
6. Введите критерий. В этом примере вы проверяете текст, поэтому введите слово в двойных кавычках: "Перо"
   Примечание: прописные и строчные буквы обрабатываются одинаково
   
7. Введите запятую, чтобы разделить аргументы
8. Выберите ячейки, содержащие значения для усреднения. В этом примере ячейки B1:B10 содержат значения
9. Введите закрывающую скобку.
   Полная формула: =СРЗНАЧЕСЛИ(A1:A10;"Перо",B1:B10)

Критерий соответствия в строке

В Excel усреднение ячеек в строках, содержащих критерий как часть содержимого ячейки. В этом примере все заказы Pen, Gel Pen и Pencil будут усреднены, так как они содержат строку "pen".

1. Выберите ячейку, в которой вы хотите увидеть среднее значение (в данном примере ячейка A12).
2. Введите знак равенства (=), чтобы начать формулу.
3. Тип: СРЗНАЧЕСЛИ(
4. Выберите ячейки, содержащие значения для проверки критерия. В этом примере будут проверены ячейки A1:A10
5. Введите запятую, чтобы разделить аргументы
6. Введите критерий. В этом примере вы проверяете текст, поэтому введите слово в двойных кавычках с одним или несколькими подстановочными знаками звездочки (*): "*Pen*"
   Примечание: верхний и нижний регистр обрабатываются поровну
   
7. Введите запятую, чтобы разделить аргументы
8. Выберите ячейки, содержащие значения для усреднения. В этом примере ячейки B1:B10 содержат значения
9. Введите закрывающую скобку.
   Полная формула: =СРЗНАЧЕСЛИ(A1:A10;"*Перо*",B1:B10)

Примечание. Вместо ввода критерия в формулу можно обратиться к ячейке. Например, формулу на шаге 7 выше можно изменить на:
=СРЗНАЧЕСЛИ(A1:A10,"*" & B12 & "*",B1:B10)
если ячейка B12 содержит текст — ручка.

Критерий и оператор

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

1. Выберите ячейку, в которой вы хотите увидеть среднее значение (в данном примере ячейка A12).
2. Введите знак равенства (=), чтобы начать формулу.
3. Тип: СРЗНАЧЕСЛИ(
4. Выберите ячейки, содержащие значения для проверки критерия. В этом примере будут проверены ячейки B1:B10
5. Введите запятую, чтобы разделить аргументы
6. Введите критерий. В этом примере вы проверяете строки, в которых количество больше или равно 10. Оператор >= используется перед числом, а весь критерий заключен в двойные кавычки: ">=10"
   Примечание. Несмотря на то, что это числовой критерий, он должен быть заключен в двойные кавычки.
   
7. Введите закрывающую скобку
8. Завершенная формула:
   =СРЗНАЧЕСЛИ(B1:B10;">=10")
   
9. Нажмите клавишу Enter, чтобы завершить ввод

Соответствие нескольким критериям

В Excel 2007 и более поздних версиях можно использовать функцию СРЗНАЧЕСЛИМН для усреднения строк, соответствующих двум или более критериям. В этом примере будут усреднены только строки, в которых элемент "Ручка" и количество больше или равно десяти.

Критерии введены в ячейки D3 и E3. Мы будем ссылаться на эти ячейки в формуле, а не вводить значения в формуле.

1. Выберите ячейку, в которой вы хотите увидеть итог.
2. Введите знак равенства (=), чтобы начать формулу.
3. Тип: СРЗНАЧЕСЛИМН(
4. Выберите ячейки, содержащие значения для усреднения. В этом примере ячейки B2:B10 будут усреднены
5. Чтобы начать следующий аргумент, введите запятую.
6. Выберите ячейки, содержащие значения для проверки первого критерия. В этом примере будут проверены ячейки A2:A10
7. Введите запятую и нажмите на ячейку D3, содержащую первый критерий "Перо".
8. Чтобы начать следующий набор критериев, введите запятую.
9. Выберите ячейки, содержащие значения для проверки второго критерия. В этом примере будут проверены ячейки B2:B10
10. Введите запятую и оператор для второго критерия: ">="
    Примечание. Оператор заключен в двойные кавычки.
    
11. Введите амперсанд (&) и нажмите на ячейку E3, которая содержит минимальное число для усредненных ячеек – 10.
12. Чтобы начать следующий аргумент, введите запятую.
13. Выберите ячейки, содержащие значения для проверки второго критерия. В этом примере будут проверены ячейки B2:B10
14. Заканчивайте закрывающей скобкой: )
15. Заполненная формула показана ниже.
    
16. Нажмите клавишу Enter, чтобы завершить ввод.

Завершенная формула:

=AVERAGEIFS(B2:B10,A2:A10,D3,B2:B10,"> img-responsive" src="https://www.contextures.com/images/functions/averageifs01.jpg" width="515" height="408" border="1" alt="Excel Оператор СРЗНАЧЕСЛИ">

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

=СРЗНАЧЕСЛИ(B2:B10,A2:A10,"Перо",B2:B10,">=10")

Среднее число строк в отфильтрованном списке – ПРОМЕЖУТОЧНЫЙ ИТОГ

После того как вы отфильтровали строки в списке, вы можете использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ для усреднения видимых строк.

1. Примените автофильтр к таблице. Инструкции можно найти здесь -- Основы автофильтра
2. Отфильтруйте хотя бы один из столбцов в таблице. В этом примере первый столбец был отфильтрован по Binders.
3. Выберите ячейку непосредственно под столбцом, который вы хотите суммировать.
4. Нажмите кнопку "Автосумма" на вкладке ленты "Главная" или вкладке "Формулы".
   • Если вы хотите, чтобы функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ находилась в ячейке, отличной от ячейки, расположенной непосредственно под отфильтрованным списком, вы можете ввести формулу вместо использования кнопки "Автосумма".
5. Формула ПРОМЕЖУТОЧНЫХ ИТОГОВ будет автоматически вставлена, суммируя видимые ячейки в столбце
   • Первый аргумент в функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ – это номер функции, указывающий, как следует рассчитывать числа. Значение по умолчанию равно 9, что говорит Excel о суммировании чисел.
   • Можно использовать и другие номера функций, например 1 для СРЗНАЧ или 2 для СЧЁТ.
6. Чтобы усреднить все непустые ячейки в столбце D, выберите 1 - СРЗНАЧ из раскрывающегося списка функций в качестве первого аргумента:
   =ПРОМЕЖУТОЧНЫЙ ИТОГ(1,D2:D10)
   < /p>

Примечание. В функции промежуточного итога можно использовать 101 вместо 1:
=ПРОМЕЖУТОЧНЫЙ ИТОГ(101,D2:D10)
для промежуточных итогов, которые были скрыты вручную, а также для отфильтрованных строк. .

Видео: усеченное среднее с помощью функции TRIMMEAN

Чтобы исключить определенный процент выпадающих данных из среднего значения, вы можете использовать функцию Excel ОБРЕЗАТЬСРЕДНЕЕ. Посмотрите это видео, чтобы узнать, как настроить формулу, а письменные инструкции находятся под видео.

Усеченное среднее с помощью функции TRIMMEAN

Чтобы исключить выбросы из среднего значения, можно использовать функцию ОБРЕЗАТЬСРЕДНЕЕ. Эта функция исключает определенный процент точек данных из верхней и нижней части набора данных, а затем возвращает среднее (среднее) оставшихся точек данных.

ПРИМЕЧАНИЕ. В этом примере количества отсортированы в порядке возрастания, поэтому легче увидеть верхние и нижние числа. Числа НЕ нужно сортировать, чтобы функция ТРИММЕАН вычисляла правильно.

Функция ОБРЕЗАТЬСРЕДНЕЕ

Функция ТРИММЕАН требует 2 аргумента: ТРИММЕАН(массив, процент)

В этом примере

• значения массива находятся в ячейках B2:B21
• процент обрезки вводится в ячейку E3 как 25% (или 0,25)

Чтобы вычислить усеченное среднее, введите эту формулу в ячейку E4::

=ОБРЕЗАТЬСРЕДН(B2:B21, E3)

Результат ТРИММЕЗНАЧ (53,06) отличается от СРЕДНЕГО (51,95), показанного в ячейке E5.

Как работает ТРИММЕАН

В этом примере есть 20 значений, а процент обрезки составляет 25%.

Чтобы рассчитать, сколько чисел нужно обрезать,

• значения подсчитываются, а затем умножаются на процент обрезки (например, 20 * 0,25 = 5
• Это число делится на 2, чтобы число обрезалось с каждой стороны (например, 5 / 2 = 2,5).
• Чтобы удалить одинаковое количество точек данных на каждом конце, число округляется до ближайшего целого числа (например, INT( 2.5) = 2)

Таким образом, в этом примере первые 2 (86,97) и нижние 2 (3,4) точки данных не будут включены в среднее значение, вычисляемое TRIMMEAN.

Функция СРЗНАЧ, используемая в этом примере для ячеек B4:B19, возвращает тот же результат, что и функция ОБРЕЗАТЬСРЕДН, с процентом обрезки 25%.

Получить рабочую тетрадь

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

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

  
• Как исправить ошибку браузера
  
• Кратко о том, как обрабатывается информация с помощью Microsoft Word
  
• Формат Plo, в чем открыть
  
• Как изменить стиль текста по умолчанию в Word
  
• Программное обеспечение для прослушивания телефонных звонков