Отклонение смещения Excel составляет

Обновлено: 03.07.2024

Стандартная разница — это стандартное отклонение выборки и стандартное отклонение генеральной совокупности. Первый относится к извлечению некоторых данных из совокупности в качестве выборки для расчета стандартного отклонения, а второй относится к расчету стандартного отклонения для всех данных, и часто невозможно получить все данные в действительности. Поэтому он обычно рассчитывает стандартное отклонение выборки. Существует 6 функций для расчета стандартного отклонения Excel, но на самом деле их всего 4, потому что есть две новые, которые используются только для замены старых функций, они такие же, как и старые. Stdev, Stdev.S и StdevA используются для расчета стандартного отклонения выборки. StdevP, Stdev.P и StdevPA используются для расчета стандартного отклонения всей совокупности.

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

I, Функция расчета стандартного отклонения в Excel

(I) Расчетная функция стандартного отклонения выборки

<р>1. Выражение

Выражение функции стандартного отклонения: СТАНДОТКЛОН(Число1,[Число2]. )

Выражение функции Stdev.S: STDEV.S(Number1,[Number2]. )

Выражение функции StdevA: STDEVA(Value1,[Value2]. )

<р>2. Описание:

А. Функция Stdev и Stdev.S используются для расчета стандартного отклонения выборки, они должны использовать как минимум один аргумент, до 255; Функция Stdev.S добавлена в Excel 2010 и используется для замены функции Stdev. В будущих версиях Excel функция Stdev может больше не поддерживаться.

Б. Функция StdevA может вычислять стандартное отклонение выборки логических (истина или ложь) и текстовых значений в дополнение к вычислению стандартного отклонения выборки значения; он также должен иметь как минимум одно значение и не более 255 значений.

С. Различие функций Stdevn(Stdev.S) и StdevA: их различие сводится к тому, как поступать с логическими значениями и текстовыми значениями, для логических значений и текстовых значений, если они вводятся непосредственно в список аргументов, они включаются в расчет стандартных отклонение по функции Stdev, True преобразуется в 1, False преобразуется в 0; если они находятся в массиве или ячейках, на которые нужно сослаться, функция Stdev игнорирует их. Функция StdevA подсчитывает логические и текстовые значения в стандартное отклонение независимо от того, где они были введены. Кроме того, если в списке аргументов есть тексты, которые нельзя преобразовать в числовые значения, все они возвращают ошибку; если есть тексты, которые не могут быть преобразованы в числовое значение в массиве или ячейках, на которые нужно ссылаться, они игнорируются.

(II) Расчетная функция стандартного отклонения генеральной совокупности

<р>1. Выражение

Выражение функции StdevP: STDEVP(Number1,[Number2]. )

Выражение функции Stdev.P: STDEV.P(Number1,[Number2]. )

Выражение функции StdevPA: STDEVPA(Value1,[Value2]. )

<р>2. Описание:

А. Функция StdevP и функция Stdev.P также используются для расчета стандартного отклонения совокупности значений, они должны использовать как минимум один аргумент, до 255; Функция Stdev.P также добавлена в Excel 2010, используется для замены функции StdevP, от функции StdevP можно отказаться в будущих версиях Excel.

Б. Функция StdevPA используется для вычисления стандартного отклонения совокупности логических (истина или ложь) и текстовых значений в дополнение к использованию для вычисления стандартного отклонения совокупности значений; он также должен иметь как минимум одно значение и не более 255 значений.

С. Функция StdevP (функция Stdev.P) и функция StdevPA отличаются: их различие также ограничивается тем, как поступать с логическими значениями и текстовыми значениями, для логических значений и текстовых значений, если они вводятся непосредственно в список аргументов, они включаются в расчет стандартного отклонения по функции StdevP, True конвертируется в 1, False конвертируется в 0; если они находятся в массиве или ячейках, на которые нужно сослаться, функция StdevP игнорирует их. Функция StdevPA подсчитывает логические и текстовые значения при расчете стандартного отклонения. Кроме того, если в списке аргументов есть тексты, которые нельзя преобразовать в числовые значения, все они возвращают ошибку; если есть тексты, которые не могут быть преобразованы в числовое значение в массиве или ячейках, на которые нужно ссылаться, они также игнорируются.

II, Как рассчитать стандартное отклонение в Excel (стандартное отклонение выборки)

(I) Рассчитайте стандартное отклонение значений с помощью Stdev(Stdev.S)

(1) Примеры расчетов с Stdev и Stdev.S

<р>1.Если вы хотите рассчитать стандартное отклонение выборки баллов по английскому языку. Дважды щелкните ячейку B2, скопируйте формулу =СТАНДОТКЛОН(B2:B18) в ячейку B2, нажмите Enter, вернитесь к 8,389857322; дважды щелкните B3, скопируйте формулу =STDEV.S(B2:B18) в B3, нажмите Enter и вернитесь к 8,389857322; выберите B2:B3, текущая вкладка «Главная», щелкните черный маленький перевернутый треугольник справа от «Общие», выберите «Число» во всплывающих параметрах, стандартное отклонение округляется до двух знаков после запятой; шаги операции, как показано на рисунке 1:

<р>2. Описание формулы:

А. B2:B18 — это ячейки, на которые нужно ссылаться для расчета стандартного отклонения. Результаты, возвращаемые двумя формулами, совпадают, что указывает на то, что Stdev работает так же, как Stdev.S.

Б. Результат, возвращаемый формулой, по умолчанию округляется до нескольких знаков после запятой, в демо-версии округляется только до двух знаков после запятой. Если вы хотите округлить до других десятичных разрядов, вы можете нажать Ctrl + 1, чтобы открыть окно «Формат ячеек», выбрать вкладку «Число», затем выбрать «Число» слева, затем ввести конкретное значение в поле "Десятичные знаки" справа, нажмите "ОК".

(2) True и False учитываются и игнорируются

<р>1. True и False учитываются при расчете стандартного отклонения

А. Дважды щелкните ячейку A1, скопируйте формулу =STDEV(24,27) в A1, нажмите Enter, вернитесь к 2,121320344; дважды щелкните A2, скопируйте формулу =STDEV(24,27,TRUE) в A2, нажмите Enter, верните 14,2243922; дважды щелкните A3, скопируйте формулу =STDEV(24,27,FALSE) в A3, нажмите Enter, вернитесь к 14,79864859; шаги операции показаны на рисунке 2:

Б. Описание формулы:

Формулы =СТАНДОТКЛОН(24,27,ИСТИНА) и =СТАНДОТКЛОН(24,27,ЛОЖЬ) возвращают результаты, отличные от =СТАНДОТКЛОН(24,27), указывая на то, что и ИСТИНА, и ЛОЖЬ учитываются при расчете стандартного отклонения, который показывает, что логическое значение записывается непосредственно в список аргументов функции Stdev и включается в стандартное отклонение.

<р>2. True и False игнорируются при расчете стандартного отклонения

А. Дважды щелкните ячейку C2, скопируйте формулу =STDEV(A2:A3) в C2, нажмите Enter, вернитесь к 2.121320344; дважды щелкните C3, скопируйте ту же формулу в C3, затем измените формулу на = СТАНДОТКЛОН (A2: A4), нажмите Enter, также будет возвращено 2,121320344; выберите B4, измените TRUE на false, нажмите C3, значение в C3 не изменилось; дважды щелкните C6, скопируйте формулу =STDEV() в C2, нажмите Enter, также будет возвращено 2,121320344; шаги рабочего процесса, как показано на рисунке 3:

��В. Описание формулы:

Как видно из демонстрации, каждая формула возвращает один и тот же результат независимо от того, содержит ли она значения True или False, указывая на то, что True и False игнорируются в ячейках или массиве функцией Stdev.

(3) Некоторые тексты, которые можно преобразовать в значения, учитываются и игнорируются.

<р>1. Дважды щелкните ячейку A1, скопируйте формулу =СТАНДОТКЛОН(2,58,2,39,"2,67") в ячейку A1, нажмите Enter, вернитесь к 0,142945211; снова дважды щелкните A1, удалите «, 2,67», нажмите Enter, вернитесь к 0,134350288; дважды щелкните A2, скопируйте формулу =STDEV(B1:B3) в A2, нажмите Enter, вернитесь к 0,134350288; шаги операции показаны на рисунке 4:

<р>2. Описание формулы:

Результат, возвращаемый формулой =СТАНДОТКЛОН(2,58,2,39,"2,67"), отличается от результата, возвращаемого формулой =СТАНДОТКЛОН(2,58,2,39), что указывает на то, что числовой текст "2,67" включен в расчет. стандартного отклонения; и результат, возвращаемый формулой =СТАНДОТКЛОН(B1:B3) и =СТАНДОТКЛОН(2,58, 2,39), одинаков, указывая на то, что 2,67 в ячейке B3 не учитывается при расчете стандартного отклонения, поскольку это текст.

(4) Возвращает ошибки, поскольку есть тексты, которые нельзя преобразовать в числовые значения и которые игнорируются

<р>2. Описание формулы:

Эти две формулы вычисляют стандартное отклонение одного и того же значения и текста. Формула =СТАНДОТКЛОН(2.58,2.39,"Десять") возвращает ошибку, указывающую, что текст, который не может быть преобразован в значение, не может быть записан в список аргументов; вместо этого формула =СТАНДОТКЛОН(B1:B3) может возвращать правильное значение, указывая на то, что массив или ячейки, на которые нужно сослаться, содержат текст, который нельзя преобразовать в значение, и этот текст игнорируется, не влияя на вычисление.

(II) Вычислить стандартное отклонение числовых значений, текстовых значений и логических значений с помощью StdevA

(1) В ячейках, на которые есть ссылки, есть числовые значения, текстовые значения и логические значения

<р>1. Дважды щелкните ячейку B2, скопируйте формулу = СТАНДОТКЛОН(A2:A6) в ячейку B2, нажмите Enter, чтобы вернуться к 47.00850987; выберите A4, удалите 86, значение в B2 станет 49,47305664, выберите A5, удалите True, значение в B2 изменится на 49,81298358 и удалите False в A6, а значение в B2 изменится на 5,658854249; шаги операции показаны на рисунке 6:

<р>2. Описание формулы:

Значения в форматах A2 и A3 являются числовыми, а значения в формате A4 – текстовыми. Когда значение в A4 удаляется, результат стандартного отклонения изменяется с 47,00850987 на 49.47305664, указывающее, что функция StdevA подсчитывает текст в ячейке в расчетное стандартное отклонение. Когда True и False удаляются по очереди, результат стандартного отклонения также изменяется, указывая на то, что функция StdevA подсчитывает логическое значение в стандартное отклонение.

(2) StdevA и Stdev сравнивают и подсчитывают примеры текстовых числовых подсчетов и игнорирования

<р>1. Независимо от того, записываются ли текстовые значения непосредственно в список аргументов или записываются в ячейки, они включаются в стандартное отклонение StdevA, но обработка десятичных чисел отличается. Например, результаты вычислений в E3, E4 и E5 различаются, что указывает на то, что StdevA пересчитывает текстовое значение в стандартное отклонение; при прямой записи «2,67» в список аргументов тип текста «2,67» преобразуется в числовой 2,67, а когда 2,67 в ячейке является текстом, он заменяется десятичной точкой, а затем преобразуется в число. Результат после преобразования в значение равен 267. Это можно подтвердить, изменив 2,67 в C5 на 267, а результат в дозе E5 не изменится. Демонстрация показана на рисунке 7:

<р>2. Разница между StdevA и Stdev для текстовых значений заключается в следующем: Stdev подсчитывает только текстовое значение «2,67», записанное непосредственно в список аргументов в стандартном значении, а StdevA подсчитывает текстовое значение «2,67» в списке аргументов и ячейки в стандартном отклонении. . На рис. 7 результаты расчета одного и того же цвета одинаковы, то есть текстовые значения обрабатываются одинаково.

III, Как рассчитать стандартное отклонение в Excel (стандартное отклонение совокупности)

(I) Пример расчета стандартного отклонения совокупности с помощью StdevP и Stdev.P

<р>1. Дважды щелкните ячейку B2, скопируйте формулу =STDEVP(A2:A6) в ячейку B2, нажмите Enter, вернитесь к 0,681791757; дважды щелкните B3, скопируйте ту же формулу в B3, а затем добавьте точку перед P, формула станет =STDEV.P(A2:A6), нажмите Enter, чтобы вернуть тот же результат; шаги операции показаны на рисунке 8:

<р>2. Описание формулы:

Формулы =СТАНДОТКЛОН(A2:A6) и =СТАНДОТКЛОН.P(A2:A6) возвращают один и тот же результат, указывая на то, что все они работают вместе для расчета стандартного отклонения совокупности значений.

(II) Вычислить стандартное отклонение совокупности, содержащей числовые значения, текстовые значения и логические значения, с помощью StdevPA

<р>1. Дважды щелкните ячейку B2, скопируйте формулу =СТАНДОТКЛОНПА(A2:A6) в ячейку B2, нажмите Enter, вернитесь к 3,851360279; выберите A4, удалите текстовое значение 8,16, значение в B2 станет 3,846526842; удалите False в A5, значение в B2 станет 3,377490995; затем удалите True в A6, значение в B2 станет 0,205; шаги операции показаны на рисунке 9:

<р>2. Описание формулы:

Когда текстовое значение в формате A4 удаляется, результат стандартного отклонения изменяется, указывая на то, что функция StdevPA подсчитывает текстовое значение в стандартное отклонение; затем удаляет True и False, и результат стандартного отклонения также изменяется, указывая на то, что функция StdevPA также подсчитывает логическое значение в стандартное отклонение.

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

IV, Примеры применения функции расчета стандартной разности в Excel

(I) Расчет стандартного отклонения выборки, отвечающей двум критериям

<р>1. Если вы хотите рассчитать стандартное отклонение выборки продаж свитера в Нью-Йорке. Дважды щелкните ячейку D12, скопируйте формулу =СТАНДОТКЛОН((B2:B11="Свитер")*(C2:C11="Нью-Йорк")*(D2:D11)) в ячейку D12, нажмите Ctrl + Shift + Enter, чтобы вернуться результат 728,2405; шаги рабочего процесса, как показано на рисунке 10:

<р>2. Формула =СТАНДОТКЛОН((B2:B11="Свитер")*(C2:C11="Нью-Йорк")*(D2:D11)) описание:

А. B2:B11="Свитер" - это первый критерий формулы, означающий: найдите "Свитер" в B2:B11, если это "Свитер", верните True, в противном случае верните False. Во время выполнения B2 извлекается из B2:B11 в первый раз; поскольку B2 — это «пуховик», он возвращается к False; B3 вынимается из B2:B11 во второй раз, и, поскольку B3 является «повседневным костюмом», он также возвращается к False; other и так далее, и, наконец, вернуть массив .

Б. C2:C11="Нью-Йорк" — второй критерий формулы, означающий: поиск "Нью-Йорк" в C2:C11. Если это «Нью-Йорк», возвращается True, в противном случае возвращается False и, наконец, возвращается Array .

С. Затем (B2:B11="Sweater")*(C2:C11="New York") становится *, затем сначала первый элемент первого массива умножается на первый элемент второго массива, и результат 0 (поскольку True конвертируется в 1, False конвертируется в 0); во-вторых, берет второй элемент первого массива и умножает второй элемент второго массива, результат равен 0; другой и так далее, и, наконец, возвращает массив .

Д. D2:D11 возвращает значения от D2 до D11 в виде массива, который возвращает .

Э. Затем формула принимает вид =СТАНДОТКЛОН(*), затем, перемножая соответствующие элементы двух массивов, формула изменяется на =СТАНДОТКЛОН(), и, наконец, стандартное отклонение выборки получается с помощью стандартного отклонения.

Подсказка. Вышеизложенное предназначено для расчета стандартного отклонения выборки, удовлетворяющей двум критериям. Если вы хотите рассчитать стандартное отклонение выборки, отвечающей трем или более критериям, вы можете продолжить добавлять критерии после второго символа * в формуле. Кроме того, если вы хотите рассчитать стандартное отклонение совокупности, измените Stdev на StdevP.

(II) Комбинация Stdev + OffSet + Match для автоматического расчета стандартного отклонения после добавления новых данных и расчета стандартного отклонения за последние несколько дней

(1) Рассчитать стандартное отклонение выборки всех данных

<р>1. Если требуется автоматически рассчитать стандартное отклонение выборки показателя для каждого дополнительного «Показателя». Дважды щелкните ячейку C3, скопируйте формулу =СТАНДОТКЛОН(СМЕЩ(B1,1,,ПОИСКПОЗ(9E+307,B:B)-1)) в ячейку C3, нажмите Enter, верните результат 71,0624; выберите A8, введите «7», нажмите клавишу Tab, чтобы переместить курсор на B8, введите 837, нажмите A9, значение в C3 автоматически изменится на 64,9300, что означает, что стандартное отклонение, содержащее вновь добавленные данные, автоматически пересчитывается после добавление строки; продолжайте вводить данные в A9 и B9, нажмите Enter, значение в C3 автоматически изменится на 68,3013; шаги операции показаны на рис. 11:

<р>2. Формула =СТАНДОТКЛОН(СМЕЩ(B1,1,,ПОИСКПОЗ(9E+307,B:B)-1)) описание:

А. 9E+307 — это максимальное значение, разрешенное Excel, которое используется для представления последней строки. ПОИСКПОЗ(9E+307,B:B) используется для возврата позиции последней строки в столбце B; когда в таблице всего 7 строк, возвращается 7; Match пропускает последний аргумент, который по умолчанию равен 1, потому что после второго аргумента B:B нет запятой, что означает: Найдите максимальное значение, которое меньше и равно 9E+307 в столбце B, и верните его позицию в столбец Б.

Б. Затем OFFSET(B1,1,,MATCH(9E+307,B:B)-1) становится OFFSET(B1,1,,7-1), затем на основе B1 возвращает ссылку на ячейку на пересечении 1 строка под B1 и 0 столбец справа от B1, высота равна 6, а ширина равна 1, то есть возвращает $B$2:$B$7. Функция OffSet опускает номер столбца третьего аргумента, значение по умолчанию равно 0. Ширина пятого аргумента также не указывается, ширина по умолчанию такая же, как B1, то есть берется 1.

С. Формула принимает вид =СТАНДОТКЛОН($B$2:$B$7), и, наконец, стандартное отклонение выборки вычисляется для всех значений в B2:B7.

(2) Рассчитайте стандартное отклонение выборки за последние 7 дней

<р>1. Дважды щелкните ячейку D3, скопируйте формулу =СТАНДОТКЛОН(СМЕЩ(B1,ПОИСКПОЗ(9E+307,B:B)-1,,-7)) в D3, нажмите Enter, верните результат 58,5800; порядок работы, как показано на рис. 12:

<р>2. Формула =СТАНДОТКЛОН(СМЕЩ(B1,ПОИСКПОЗ(9E+307,B:B)-1,,-7)) описание:

А. ПОИСКПОЗ (9E+307,B:B) также используется для возврата позиции последней строки в столбце B в таблице, и результат равен 9.

Б. Затем OFFSET(B1,MATCH(9E+307,B:B)-1,,-7) становится OFFSET(B1,9-1,,-7), а затем на основе B1 возвращает ссылку на ячейку в пересечение 8-й строки под B1 и 0-го столбца справа от B1 и высоты до -7 и ширины до 1, то есть возвращает $B$3:$B$9. Следующие 8 рядов B1 идут до B9, а -7 означает снизу вверх, здесь это означает: от B9 до верхних 7 рядов, то есть B3.

С. Формула принимает вид =СТАНДОТКЛОН($B$3:$B$9), и, наконец, рассчитывается стандартное отклонение выборки для значений в B3:B9, то есть рассчитывается стандартное отклонение за последние 7 дней. Если вы хотите рассчитать 5 или 14 дней, измените -7 на -5 или -14.

Хотите ли вы создавать формулы, которые «двигаются» вместе с вашим набором необработанных данных?

Именно тогда вам нужно использовать функцию СМЕЩЕНИЯ Excel.

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

В OFFSET сложно разобраться из-за большого количества задействованных аргументов.

Думайте об этом как о GPS:

Вы даете ему отправную точку, а затем рассказываете:

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

В этом видео показаны 3 практических случая применения Excel OFFSET

В качестве альтернативы прочитайте, если вы предпочитаете это просмотру видео…

Полный курс здесь!

Хотите улучшить свои навыки работы с Excel? Ознакомьтесь с моим курсом: расширенные советы и формулы Excel, чтобы работать эффективнее!

Функция Excel OFFSET для динамических расчетов — простые шаги

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

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

Смещение Excel: данные расположены вертикально (0:45)

В этом примере у нас есть таблица с месяцами в первом столбце и доходом от продаж во втором столбце.

Один из способов получить среднее значение за последние шесть месяцев — использовать функцию СРЗНАЧ. Для этого вы выделяете ячейки под столбцом «Продажи».

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

Функция OFFSET поможет вам избежать этого!

Для начала давайте пройдемся по функции СМЕЩ отдельно, прежде чем мы перейдем к ее интеграции в функцию СРЗНАЧ. Синтаксис OFFSET:

=OFFSET(ссылка, строки, столбцы, [высота], [ширина])

Давайте рассмотрим простой пример:

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

После этого укажите, на сколько строк вы хотите переместиться вниз. Допустим, я хочу опуститься на 1 строку вниз. Затем укажите, сколько столбцов вы хотите переместить. Я хочу остаться в этом столбце, я наберу 0.

Последние два аргумента — это [высота] и [ширина]. Запись 1 для высоты и 1 для ширины означает, что я хочу одну ячейку. Эти последние два аргумента никогда не могут быть равны 0,0. Минимум они могут быть 1,1. Если вы хотите сослаться на три строки, вы должны написать 3,1.

Для этого примера начнем с 1,1. Моя формула на данном этапе выглядит так:

=СМЕЩЕНИЕ(B3,1,0,1,1)

Вы заметите, что результатом формулы является 100. Что происходит, так это то, что она начинается с ячейки B3 и идет вниз на 1 строку, затем она не перемещает столбцы, а затем дает нам одну ячейку. Это дает нам 100.

Если вы установите для параметра строки значение 3, он переместится на три строки вниз и даст вам 130.

=СМЕЩЕНИЕ(B3,3,0,1,1)

Для параметра столбца установка -1 приведет к перемещению на один столбец влево и возврату даты в ячейку A6. Использование +1 для параметра столбца перемещает один столбец вправо и возвращает 0, поскольку ячейка C6 пуста. Вот как работает СМЕЩЕНИЕ.

Если мы хотим использовать диапазон вместо одной ячейки, мы должны указать значение больше 1 (или меньше -1) для параметра [height]. Написание синтаксиса как:

=СМЕЩЕНИЕ(B3,3,0,3,1)

Это приводит к ошибке. Почему? Давайте проследим, что он делает. Он начинается с ячейки B3, перемещается на три клетки вниз к B6, а затем пытается дать нам ячейки B6, B7 и B8. Однако на самом деле он не может поместить эти три ячейки в одну ячейку.

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

В этом случае мы будем использовать формулу СРЕДНЕГО. Теперь функция будет такой:

=СРЕДНЕЕ(СМЕЩЕНИЕ(B3,3,0,3,1))

В результате получается 108 – среднее значение ячеек B6, B7 и B8. Если бы мы расширили эту формулу, чтобы получить последние шесть месяцев, нам пришлось бы написать формулу по-другому.

Один из способов — определить последнюю ячейку в столбце с помощью функции COUNT, а затем использовать -6 в качестве [высоты], что поможет нам выделить наш диапазон «назад».

Еще один способ – найти последнюю ячейку, вернуться на 6 строк назад и указать [высоту] как +6. Вначале это может показаться запутанным, поэтому давайте сначала начнем с более простого метода.

Начните с использования ячейки B3 в качестве ссылки. На сколько клеток мы должны спуститься? Используйте функцию COUNT или COUNTA, чтобы подсчитать количество заполненных ячеек в столбце «Продажи» прямо под B3.

Формула COUNT учитывает только числа.

Функция COUNTA считает ячейку, даже если это текст.

В этом случае не имеет значения, используем ли мы функцию COUNT или COUNTA.

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

=СРЕДНЕЕ(СМЕЩЕНИЕ(B3,СЧЕТЧ(B4:B15),0,-6,1))

Это разбивка формулы:

Ссылка на ячейку B3.
Параметр Row использует функцию COUNTA, результатом которой является 7. Это указывает OFFSET переместить 7 ячеек ниже B3.
Параметр столбца имеет значение 0, что означает, что он остается в том же столбце.
Параметр [Высота] равен -6, потому что мы хотим взять последние 6 ячеек (переместиться назад). В этом случае будут выделены ячейки с B5 по B10.
Параметр [Width] по умолчанию равен 1.

Эта формула дает 113.

Теперь, когда мы добавляем данные за новый месяц в ячейку B11 (скажем, 200), формула автоматически обновляется до среднего значения 127, что мы можем проверить, сравнив его со средним значением, отображаемым в строке состояния, когда ячейки B6 до B11 выделены.

Мы также можем проверить это, выбрав «Формулы» > «Оценить формулу», а затем нажав кнопку «Оценить». Вы увидите, что окончательный диапазон, который он принимает, — это среднее значение от B6 до B11, как мы и предполагали.

Другой способ записать эту формулу среднего смещения:

=СРЕДНЕЕ(СМЕЩЕНИЕ(B4,СЧЁТ(B4:B14)-6,0,6,1)

Разбивка этого:

Ссылка устанавливается на первое значение данных, B4
В строке используется функция COUNTA, которая подсчитывает все заполненные ячейки от B4 до B14 и вычитает 6.
Столбец равен 0, что указывает на отсутствие перемещения или смещения столбцов.
[Высота] равна 6, что означает выделение 6 ячеек (внизу).
[Ширина] по умолчанию равна 1, так как нам нужен только один столбец.

Это также приводит к 127. Это два разных способа записи формулы.

Возьмем еще один пример, в котором данные расположены горизонтально.

Смещение Excel: данные расположены горизонтально (9:01)

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

=СРЕДНЕЕ(СМЕЩЕНИЕ(A17,0,СЧЁТ(B17:O17),1,-6))

что переводится как:

Ссылка установлена на A17
Строка установлена на 0, что указывает на отсутствие движения. Мы хотим остаться в том же ряду.
Параметр "Столбец" использует функцию COUNT, которая подсчитывает количество ячеек со значениями в столбцах от B до O. Столбец O – это оценка того, где, по вашему мнению, будут числа. Эта часть дает нам 7.
[height] имеет значение 1, поскольку мы ссылаемся только на одну строку.
[width] имеет значение –6, что выделяет 6 столбцов слева от последней заполненной ячейки (переход назад).

Это дает то же самое, что и среднее значение за последние шесть месяцев, начиная с июля. Если мы снова добавим данные за август, они автоматически обновятся, включив август, а затем вычислят среднее значение за последние шесть месяцев. В результате получается 127.

Просто проявите творческий подход и практикуйте это во всех направлениях, пока не научитесь.

Смещение Excel: динамический диапазон для расчета скользящего среднего (10:42)

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

Это ваши необработанные данные:

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

Давайте установим начальную точку в заголовке столбца B25.

Для строки мы хотим переместить количество ячеек вниз, чтобы получить выбранный месяц, который пользователь выбрал из раскрывающегося списка. Функция ПОИСКПОЗ даст нам позицию выбранного месяца в таблице данных.

Например, если выбран месяц 04/2014, что соответствует 90, формула должна найти позицию 90 в таблице данных, которая является четвертой строкой. Чтобы использовать функцию ПОИСКПОЗ(), используйте следующий синтаксис:

=ПОИСКПОЗ(искомое_значение, массив, [тип_сопоставления])

Мы ссылаемся на раскрывающуюся ячейку F25 как на искомое_значение, а на столбец Месяц A26:A51 — как на массив. [match_type] устанавливается равным 0 для идеального совпадения. Формула будет выглядеть так:

=ПОИСКПОЗ(F25,A25:A51,0).

Это подсчитает позицию выбранного месяца. В случае с 04/2014 получается 4.

Столбец может быть установлен на 0, потому что мы хотим остаться в столбце B.

[Высота] зависит от вопроса. Если мы хотим включить выбранный месяц в расчет, мы должны использовать 3.

[Ширина] установлена на 1 .

Результирующая формула:

=СРЗНАЧ(СМЕЩЕНИЕ(B25,ПОИСКПОЗ(F25,A26:A51,0),0,3,1)

что дает 102.

Если мы не хотим включать выбранный месяц в расчет, мы должны добавить +1 к нашей функции ПОИСКПОЗ. Это говорит формуле найти этот выбранный месяц и спуститься на одну дополнительную ячейку вниз. Окончательная формула выглядит так:

=СРЕДНЕЕ(СМЕЩЕНИЕ(B25,ПОИСКПОЗ(F25,A26:A51,0)+1,0,3,1)

Средняя доходность составляет 113, что является средним значением за три месяца после выбранного месяца, т. е. среднее значение B30:B32.

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

Вот как вы можете использовать функцию СРЗНАЧ, функцию СМЕЩЕНИЯ и функции ПОИСКПОЗ вместе, чтобы создать этот динамический эффект в ваших файлах Excel.

Внимание!

Необходимо предупредить, что СМЕЩ — это изменчивая формула Excel, что означает, что она вычисляется каждый раз, когда вы делаете ход в Excel. Честно говоря, в файлах, в которых я использовал OFFSET, я почти не видел, чтобы мои файлы становились медленнее из-за использования этой функции. Но я и не переусердствовал с ними. Я рекомендую вам не злоупотреблять ими.

Зарегистрированный пользователь Дата присоединения 07-02-2014 Местоположение London MS-Off Ver 2013 Сообщения 3

Функции STDEV и OFFSET для расчета скользящей волатильности

Я пытаюсь получить значения изменяющейся волатильности цен на акции, используя функции STDEV и OFFSET. У меня есть набор цен на одну акцию за 50 дней подряд (столбец B от B5 до B54). Я не могу просто использовать STDEV, потому что мне нужно изменить количество дней в расчете волатильности (в ячейке D1), и электронная таблица должна автоматически адаптироваться к этому. Сначала я вычисляю Ui — изменение цены акции LN (Underlying/Underlying-1) — которое начинается с C5 до C54 (C5 равно нулю, поэтому фактический Ui начинается с C6). Затем я рассчитываю скользящую волатильность Ui (ниже формула для дня 2 - в ячейке D6), используя эту формулу:

Но это дает неправильные результаты. Правильные результаты, к которым я стремлюсь, приведены в столбце E. Чтобы рассчитать годовую волатильность, я умножаю дневную волатильность на SQRT(365).

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

Ваше стандартное отклонение смотрит ВПЕРЕД, а не назад. Например, D6 вычисляет стандартное отклонение для C6:C10. в будущее. Вам нужно немного изменить уравнение

Формула:

и вы получите ожидаемый результат.

Зарегистрированный пользователь Дата присоединения 07-02-2014 Местоположение London MS-Off Ver 2013 Сообщения 3

Я пытался изменить строки с 0 на C:C и COUNT(C:C), но оба результата дают неверный ответ. Спасибо за помощь!

Согласно нынешним настройкам, первые несколько дней будут бесполезными, поскольку они возвращаются к ячейкам без данных. Таким образом, D6 оглядывается назад, чтобы вычислить STDEV от C2 до c6. Как только вы увеличите интервал до 7 или более, он попытается вернуться к нулевой строке или ниже; чего не может.

Я полагаю, важно, чтобы он работал должным образом с первого дня??

Функция СМЕЩ в Excel возвращает ссылку на диапазон, составленный из пяти входных данных: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцах. СМЕЩ удобен в формулах, требующих динамического диапазона.

ссылка – начальная точка, указанная в виде ссылки на ячейку или диапазона.
rows — количество строк, на которое нужно сместить начальную ссылку.
столбцы — количество столбцов, смещенных справа от начальной ссылки.
height – [необязательно] высота возвращаемой ссылки в строках.
width – [необязательно] ширина возвращаемой ссылки в столбцах.

Функция Excel OFFSET возвращает динамический диапазон, построенный с пятью входными данными: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцах.

Начальной точкой (аргументом ссылки) может быть одна ячейка или диапазон ячеек. Аргументы rows и cols — это количество ячеек, которые нужно «сместить» от начальной точки. Аргументы высоты и ширины являются необязательными и определяют размер создаваемого диапазона. Если высота и ширина не указаны, по умолчанию они равны высоте и ширине ссылки.

Например, для ссылки C5, начинающейся с A1, ссылка — это A1, строки – 4, а столбцы – 2:

Чтобы сослаться на C1:C5 из A1, ссылка — это A1, строки — 0, столбцы — 2, высота — 5, а ширина — 1:

Примечание: ширину можно не указывать, так как по умолчанию она равна 1.

Часто можно увидеть, что OFFSET заключен в другую функцию, которая ожидает диапазон. Например, для СУММ C1:C5, начиная с A1:

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

Примечание. В документации Excel указано, что высота и ширина не могут быть отрицательными, но отрицательные значения отлично работают с начала 1990-х годов. Функция СМЕЩ в Google Таблицах не допускает отрицательного значения аргументов высоты или ширины.

Примеры

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

На приведенном ниже экране мы используем OFFSET, чтобы вернуть третье значение (март) во втором столбце (запад). Формула в H4:

На приведенном ниже экране мы используем OFFSET, чтобы вернуть последнее значение (июнь) в третьем столбце (север). Формула в H4:

Ниже мы используем OFFSET для возврата всех значений в третьем столбце (север). Формула в H4:

Ниже мы используем OFFSET для возврата всех значений за май (пятая строка). Формула в H4:

Ниже мы используем OFFSET, чтобы получить значения апреля, мая и июня для западного региона. Формула в H4:

Ниже мы используем OFFSET, чтобы получить значения апреля, мая и июня для запада и севера. Формула в H4:

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