Функция СТОШYX
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функции СТОШYX в Microsoft Excel.
Описание
Возвращает стандартную ошибку предсказанных значений y для каждого значения x в регрессии. Стандартная ошибка — это мера ошибки предсказанного значения y для отдельного значения x.
Синтаксис
Аргументы функции СТОШYX описаны ниже.
- Известные_значения_y Обязательный. Массив или диапазон зависимых точек данных.
- Известные_значения_x Обязательный. Массив или диапазон независимых точек данных.
Замечания
- Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками.
- Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов.
- Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения пропускаются; однако ячейки, которые содержат нулевые значения, учитываются.
- Аргументы, которые представляют собой значения ошибок или текст, не преобразуемый в числа, вызывают ошибку.
- Если аргументы «известные_значения_y» и «известные_значения_x» содержат различное количество точек данных, то функция СТОШYX возвращает значение ошибки #Н/Д.
- Если known_y и known_x пустые или имеют менее трех точек данных, steYX возвращает #DIV/0! значение ошибки #ЗНАЧ!.
- Уравнение для стандартной ошибки предсказанного y имеет следующий вид: где x и y — выборочные средние значения СРЗНАЧ(известные_значения_x) и СРЗНАЧ(известные_значения_y), а n — размер выборки.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Известные значения y
Известные значения x
Добавление, изменение и удаление отрезков ошибок на диаграмме
Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Excel для Microsoft 365 для Mac Word для Microsoft 365 для Mac PowerPoint для Microsoft 365 для Mac Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Excel 2021 для Mac Word 2021 для Mac PowerPoint 2021 для Mac Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Excel 2019 для Mac Word 2019 для Mac PowerPoint 2019 для Mac Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Excel 2016 для Mac Word 2016 для Mac PowerPoint 2016 для Mac Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Excel 2010 Word 2010 Outlook 2010 PowerPoint 2010 Excel 2007 Outlook 2007 Excel Starter 2010 Еще. Меньше
Планки погрешностей на создаваемых диаграммах помогают быстро определять пределы погрешностей и стандартные отклонения. Их можно отобразить для всех точек или маркеров данных в ряду данных как стандартную ошибку, относительную ошибку или стандартное отклонение. Можно задать собственные значения для отображения точных величин погрешностей. Например, отобразить положительную и отрицательную величины погрешностей в 10 % в результатах научного эксперимента можно следующим образом:
Планки погрешностей можно использовать на плоских диаграммах с областями, гистограммах, линейчатых диаграммах, графиках, точечных и пузырьковых диаграммах. На точечных и пузырьковых диаграммах планки погрешностей можно изобразить для значений X и Y.
Примечание: Следующие процедуры применяются к Office 2013 и более поздним версиям. Ищете инструкции по Office 2010?
Добавление и удаление отрезков ошибок
- Щелкните в любом месте диаграммы.
- Нажмите кнопку «Элементы диаграммы
рядом с диаграммой, а затем установите флажок «Панели ошибок «. (Снимите флажок, чтобы удалить отрезки ошибок.) - Чтобы изменить отображаемую сумму ошибки, щелкните стрелку рядом с полосами ошибок и выберите нужный вариант.
- Выберите предопределенный параметр планок погрешностей, такой как Стандартная погрешность, Относительное отклонение или Стандартное отклонение.
- Выберите пункт Дополнительные параметры, чтобы задать собственные величины пределов погрешностей, а затем выберите нужные параметры в разделе Вертикальный предел погрешностей или Горизонтальный предел погрешностей. Здесь также можно изменить направление и стиль концов пределов погрешностей или создать собственные пределы погрешностей.
Примечание: Направление планок погрешностей зависит от типа диаграммы. Для точечных диаграмм могут отображаются и горизонтальные, и вертикальные планки погрешностей. Чтобы удалить планки погрешностей, выделите их и нажмите клавишу DELETE.
Формулы для расчета величины погрешности
Пользователи часто спрашивают, как в Excel вычисляется величина погрешности. Для вычисления стандартной погрешности и стандартного отклонения, которые отображаются на диаграмме, используются указанные ниже формулы.
Используемое уравнение
Стандартная погрешность
s = номер ряда;
i = номер точки в ряду s;
m = номер ряда для точки y на диаграмме;
n = число точек в каждом ряду;
yis = значение данных ряда s и i-й точки;
ny = суммарное число значений данных во всех рядах.
Стандартное отклонение
s = номер ряда;
i = номер точки в ряду s;
m = номер ряда для точки y на диаграмме;
n = число точек в каждом ряду;
yis = значение данных ряда s и i-й точки;
ny = суммарное число значений данных во всех рядах;
M = среднее арифметическое.
Добавление, изменение и удаление отрезков ошибок на диаграмме в Office 2010
Проверка формул для вычисления сумм ошибок (Office 2010)
В Excel можно отобразить столбцы ошибок, использующие стандартную сумму ошибок, процент от значения (5 %) или стандартное отклонение.
Стандартные ошибки и стандартное отклонение используют следующие уравнения для вычисления сумм ошибок, отображаемых на диаграмме.
Используемое уравнение
Стандартная погрешность
s = номер ряда;
i = номер точки в ряду s;
m = номер ряда для точки y на диаграмме;
n = число точек в каждом ряду;
yis = значение данных ряда s и i-й точки;
ny = суммарное число значений данных во всех рядах.
Стандартное отклонение
s = номер ряда;
i = номер точки в ряду s;
m = номер ряда для точки y на диаграмме;
n = число точек в каждом ряду;
yis = значение данных ряда s и i-й точки;
ny = суммарное число значений данных во всех рядах;
M = среднее арифметическое.
Добавление отрезков ошибок (Office 2010)
- На двухмерной диаграмме, линейчатой диаграмме, столбце, линии, хи (точечной) или пузырьковой диаграмме выполните одно из следующих действий:
- Чтобы добавить гистограммы во все ряды данных на диаграмме, щелкните область диаграммы.
- Чтобы добавить панели ошибок в выбранную точку данных или ряд данных, щелкните нужные точки данных или ряды данных или выполните следующие действия, чтобы выбрать ее из списка элементов диаграммы:
- Щелкните в любом месте диаграммы. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
- На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
Примечание: Направление гистограммы зависит от типа диаграммы. Для точечных диаграмм по умолчанию отображаются горизонтальные и вертикальные полосы ошибок. Вы можете удалить один из этих столбцов ошибок, выбрав их и нажав клавишу DELETE.
Изменение отображения отрезков ошибок (Office 2010)
- На двухстрочной области, линейчатой диаграмме, столбце, линии, хи (точечной) или пузырьковой диаграмме щелкните отрезки ошибок, точку данных или ряд данных с полосами ошибок, которые вы хотите изменить, или выполните следующие действия, чтобы выбрать их из списка элементов диаграммы:
- Щелкните в любом месте диаграммы. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
- На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
Изменение параметров суммы ошибок (Office 2010)
- На двухстрочной области, линейчатой диаграмме, столбце, линии, хи (точечной) или пузырьковой диаграмме щелкните отрезки ошибок, точку данных или ряд данных с полосами ошибок, которые вы хотите изменить, или выполните следующие действия, чтобы выбрать их из списка элементов диаграммы:
- Щелкните в любом месте диаграммы. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
- На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
Совет: Чтобы указать диапазон листа, можно нажать кнопку «Свернуть «, а затем выбрать данные, которые нужно использовать на листе. Снова нажмите кнопку «Свернуть диалоговое окно», чтобы вернуться к диалоговом окне.
Примечание: В Microsoft Office Word 2007 или Microsoft Office PowerPoint 2007 диалоговом окне «Настраиваемые панели ошибок» кнопка «Свернуть диалоговое окно» может не отображаться, а введите только значения количества ошибок, которые вы хотите использовать.
Удаление отрезков ошибок (Office 2010)
- На двухстрочной области, панели, столбце, линии, хи (точечной) или пузырьковой диаграмме щелкните гистограмму, точку данных или ряд данных с отрезками ошибок, которые нужно удалить, или выполните следующие действия, чтобы выбрать их из списка элементов диаграммы:
- Щелкните в любом месте диаграммы. Будут отображены средства Работа с диаграммами, включающие вкладки Конструктор, Макет и Формат.
- На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, а затем выберите нужный элемент диаграммы.
- На вкладке « Макет» в группе «Анализ » щелкните » Панели ошибок» и выберите пункт » Нет».
- Нажмите клавишу DELETE.
Совет: Вы можете удалить полосы ошибок сразу после их добавления на диаграмму, нажав кнопку «Отменить» на панели быстрого доступа или нажав клавиши CTRL+Z.
Выполните одно из следующих действий:
Выражение погрешности в виде процентной доли, стандартного отклонения или стандартной ошибки
Стандартная погрешность
Применение стандартной ошибки с использованием следующей формулы:
s — номер ряда;
I — номер точки в ряду s;
m — количество рядов для точки y на диаграмме;
n — количество точек в каждом ряду;
y — значение данных ряда s и I-й точки;
n y — общее число значений данных во всех рядах.Применение процентной доли значения к каждой точке данных в ряду данных
Стандартное отклонение
Применение кратного стандартного отклонения с использованием следующей формулы:
s — номер ряда;
I — номер точки в ряду s;
m — количество рядов для точки y на диаграмме;
n — количество точек в каждом ряду;
y — значение данных ряда s и I-й точки;
n y — общее число значений данных во всех рядах;
M — арифметическое среднее.Выражение погрешностей в виде пользовательских значений
- На диаграмме выберите ряд данных, к которому нужно добавить панели ошибок.
- На вкладке «Конструктор диаграммы » нажмите кнопку «Добавить элемент диаграммы» и выберите пункт «Дополнительные параметры гистограммы».
- В области «Формат гистограмм» на вкладке «Параметры панели ошибок» в разделе «Сумма ошибки» нажмите кнопку «Настраиваемое» и выберите команду «Указать значение».
- В разделе Величина погрешности выберите пункт Настраиваемая, а затем — пункт Укажите значение.
- В полях Положительное значение ошибки и Отрицательное значение ошибки введите нужные значения для каждой точки данных, разделенные точкой с запятой (например, 0,4; 0,3; 0,8), и нажмите кнопку ОК.
Примечание: Значения погрешностей можно также задать в виде диапазона ячеек из той же книги Excel. Чтобы указать диапазон ячеек, в диалоговом окне Настраиваемые планки погрешностей очистите содержимое поля Положительное значение ошибки или Отрицательное значение ошибки и укажите нужный диапазон ячеек.
Добавление полос повышения и понижения
- На диаграмме выберите ряд данных, в который нужно добавить отрезки вверх и вниз.
- На вкладке «Конструктор диаграмм » нажмите кнопку «Добавить элемент диаграммы», наведите указатель мыши на полосы вверх и вниз, а затем щелкните «Стрелки вверх /вниз». В зависимости от типа диаграммы, некоторые параметры могут быть недоступны.
Как найти погрешность в Excel
Excel — это мощный инструмент для обработки данных, который позволяет нам выполнять различные операции расчетов и анализа. Одним из важных аспектов работы с данными является нахождение и вычисление погрешности. Погрешность — это разница между фактическим значением и ожидаемым значением. Найти и посчитать погрешность в Excel можно с помощью нескольких простых шагов.
Во-первых, нужно определить, какую погрешность вы хотите найти. Это может быть абсолютная погрешность, которая показывает величину разницы между фактическим и ожидаемым значением, или относительная погрешность, которая выражается в процентах. Выбор зависит от ваших конкретных потребностей и задач.
Затем используйте функции Excel для вычисления погрешности. Для нахождения абсолютной погрешности можно использовать функцию ABS, которая возвращает абсолютное значение числа. Например, если вы хотите найти абсолютную погрешность между фактическим значением в ячейке A1 и ожидаемым значением в ячейке B1, вы можете использовать формулу =ABS(A1-B1).
Для вычисления относительной погрешности можно использовать функцию DIVIDE, которая делит одно число на другое. Например, если вы хотите вычислить относительную погрешность между фактическим значением в ячейке A1 и ожидаемым значением в ячейке B1, вы можете использовать формулу =DIVIDE(ABS(A1-B1),B1)*100. Эта формула сначала вычисляет абсолютную погрешность, затем делит ее на ожидаемое значение и умножает на 100, чтобы получить процентное значение.
Методы подсчета погрешности в Excel
Первый метод – это использование функции «МИНУС». Эта функция позволяет найти разницу между двумя значениями. Например, если у вас есть измеренное значение и истинное значение, вы можете использовать функцию «МИНУС», чтобы найти разницу между ними и тем самым посчитать погрешность.
Второй метод – использование формулы для расчета погрешности. Формула для расчета погрешности может быть разной в зависимости от задачи. Например, для расчета относительной погрешности используется формула: (измеренное значение — истинное значение) / истинное значение * 100%. Для расчета абсолютной погрешности используется формула: измеренное значение — истинное значение.
Читайте также: Как перевести текст в Excel
Третий метод – использование специальных функций Excel. В Excel есть несколько функций, которые могут помочь в подсчете погрешности. Например, функция «СРЗНАЧ» может быть использована для расчета средней абсолютной погрешности.
Если вам нужно посчитать погрешность в Excel, вам необходимо выбрать подходящий метод и внести соответствующую формулу или использовать специальные функции. Это позволит вам точно рассчитать погрешность и использовать полученные данные для анализа и принятия решений.
Расчет абсолютной погрешности в Excel
При выполнении различных вычислений и анализе данных в Excel важно учитывать погрешность полученных результатов. Погрешность может возникать из-за неточности входных данных, ошибок в формулах или округлений чисел. Расчет абсолютной погрешности в Excel позволяет оценить степень точности полученных результатов и учесть возможные ошибки.
Существует несколько способов вычисления абсолютной погрешности в Excel. Один из наиболее простых методов — использование функции ABS. Функция ABS возвращает абсолютное значение числа, то есть его модуль. Для вычисления абсолютной погрешности следует вычесть из ожидаемого значения фактическое значение и применить функцию ABS к полученному результату.
Например, если ожидаемое значение равно 10, а фактическое значение 8, то формула для расчета абсолютной погрешности будет выглядеть следующим образом:
=ABS(10-8)
Результатом данной формулы будет число 2, которое является абсолютной погрешностью.
В Excel также можно использовать функцию IF для автоматического определения наибольшей абсолютной погрешности среди нескольких значений. Формула, использующая функцию IF, будет иметь следующий вид:
=IF(ABS(A1-B1)>ABS(A2-B2);ABS(A1-B1);ABS(A2-B2))
В данной формуле A1 и B1 представляют ожидаемые значения, а A2 и B2 — фактические значения. Формула сравнивает абсолютные погрешности для каждой пары значений и возвращает наибольшую из них.
Как посчитать погрешность в excel
Пусть – точное значение, – приближенное значение некоторого числа.
Абсолютная погрешность приближенного числа равна модулю разности между его точным и приближенным значениями:
Довольно часто точное значение неизвестно, поэтому вместо абсолютной погрешности используют понятие границы абсолютной погрешности:
Число называется предельной абсолютной погрешностью, оно равно или превышает значение абсолютной погрешности.
Основной характеристикой точности числа является относительная погрешность.
Относительная погрешность – это отношение абсолютной погрешности к приближенному значению числа:
Результат действий над приближенными числами представляет собой приближенное число. Погрешность результата выражается через погрешности первоначальных данных по правилам:
Общая формула для оценки предельной абсолютной погрешности функции нескольких переменных имеет вид:
где –предельная абсолютная погрешность числа .
Пример: Известно, что где
Для оценки предельной абсолютной погрешности воспользуемся формулой:
Рис. 1. Вид экрана для вычисления абсолютной и относительной погрешностей
Исходные данные вводятся в блок А1:B6 (рис. 1). В ячейки С1:С6вводятся формулы для вычисления частных производных искомой функции. В ячейку Е8записывается формула . Модуль вводится с использованием функции =abs().
В ячейках D1:E6рассчитываются верхние и нижние оценки значений переменных по формулам (аналогично для других переменных).
В ячейках B8:B10вычисляются верхняя и нижняя оценки значений функции и само значение функции отличие вычисляемых функций в используемом наборе аргументов.
В ячейку Е9записывается формула для вычисления абсолютной погрешности Найденная абсолютная погрешность не должна превышать значение предельной абсолютной погрешности, т.е.
В ячейку Е10записывается формула для вычисления относительной погрешности
Предельную относительную погрешность заданной функции вычислим следующим образом:
Полученную формулу записывают в ячейку Е11. Найденная относительная погрешность не должна превышать значение предельной относительной погрешности, т.е.
Задания для самостоятельного выполнения.
Из таблицы 1 приложения взять исходные данные своего варианта. Вариант определяется по порядковому номеру в списке группы. Вычислить частные производные, верхнюю и нижнюю оценки значений функции и само значение функции, изменить формулу вычисления предельной относительной погрешности. Все остальные ячейки пересчитаются автоматически.
Контрольные вопросы
1. Как записать основные математические функции в Excel.
2. Сформулируйте определение абсолютной и относительной погрешностей.
3. Запишите формулы для вычисления предельной абсолютной и предельной относительной погрешностей.
4. Основные правила вычисления абсолютной и относительной погрешностей.
Читайте также: Как написать администратору группы в контакте
Не нашли то, что искали? Воспользуйтесь поиском:
Лучшие изречения: Только сон приблежает студента к концу лекции. А чужой храп его отдаляет. 8833 — | 7547 — или читать все.
78.85.5.224 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.
Отключите adBlock!
и обновите страницу (F5)
очень нужноС использованием встроенных функций Excel расчет доверительного интервала проводится следующим образом.
1) Рассчитывается среднее значение
=СРЗНАЧ(число1; число2; . )
число1, число2, . — аргументы, для которых вычисляется среднее.2) Рассчитывается стандартное отклонение
=СТАНДОТКЛОНП(число1; число2; . )
число1, число2, . — аргументы, для которых вычисляется стандартное отклонение.3) Рассчитывается абсолютная погрешность
=ДОВЕРИТ(альфа ;станд_откл;размер)
альфа — уровень значимости используемый для вычисления уровня надежности.( , т.е. означает надежности );
станд_откл — стандартное отклонение, предполагается известным;
размер — размер выборки.Задание: Обработать заданный набор экспериментальных данных методом Стьюдента, построить экспериментальные кривые методом наименьших квадратов.
Предположим, в ходе эксперимента по измерению электросопротивления были получены следующие данные:
Используя для определения сопротивления закон Ома произведем обработку данной серии экспериментальных данных.
Используемуе формулы 
Результат расчета 
Для построения графика используем мастер диаграмм.
Полученные экспериментальные данные следует аппроксимировать. Для выполнения этой процедуры в Excel предусмотрен мастер, добавляющий линию тренда, производящий аппроксимацию и сглаживание.
В меню «Диаграмма» выберите пункт «Добавить линию тренда…».
В результате, должен получиться следующий график.
Задание 1.
Просчитать погрешность измерений и построить график ее распределения.
Задание 1 Задание 2 Задание 3 № опыта № опыта № опыта 10,3 15,55 25,65 10,277 15,527 25,627 10,325 15,575 25,675 10,285 15,535 25,635 10,297 15,547 25,647 10,31 15,56 25,66 10,35 15,6 25,7 10,35 15,6 25,7 10,29 15,54 25,64 10,38 15,63 25,73 Задание 4 Задание 5 Задание 6 № опыта №опыта № опыта 27,65 23,65 17,3 27,627 23,627 17,277 27,675 23,675 17,325 27,635 23,635 17,285 27,647 23,647 17,297 27,66 23,66 17,31 27,7 23,7 17,35 27,7 23,7 17,35 27,64 23,64 17,29 27,73 23,73 17,38 Задание 7 Задание 8 Задание 9 № опыта № опыта № опыта 10,3 13,55 12,65 10,277 13,527 12,627 10,325 13,575 12,675 10,285 13,535 12,635 10,297 13,547 12,647 10,31 13,56 12,66 10,35 13,6 12,7 10,35 13,6 12,7 10,29 13,54 12,64 10,38 13,63 12,73 Задание 10 Задание 11 Задание 12 № опыта №опыта № опыта 26,65 24,65 18,3 26,627 24,627 18,277 26,675 24,675 18,325 26,635 24,635 18,285 26,647 24,647 18,297 26,66 24,66 18,31 26,7 24,7 18,35 26,7 24,7 18,35 26,64 24,64 18,29 26,73 24,73 18,38 Задание 13 Задание 14 Задание 15 № опыта № опыта № опыта 10,3 15,55 25,65 10,277 15,527 25,627 10,325 15,575 25,675 10,285 15,535 25,635 10,297 15,547 25,647 10,31 15,56 25,66 10,35 15,6 25,7 10,35 15,6 25,7 10,29 15,54 25,64 10,38 15,63 25,73 Задание 16 Задание 17 Задание 18 № опыта №опыта № опыта 27,65 23,65 17,3 27,627 23,627 17,277 27,675 23,675 17,325 27,635 23,635 17,285 27,647 23,647 17,297 27,66 23,66 17,31 27,7 23,7 17,35 27,7 23,7 17,35 27,64 23,64 17,29 27,73 23,73 17,38 Читайте также: Как настроить адаптер wifi на ноутбуке
Задание 2.
Определить является ли 3-е измерение промахом.Доброго дня, друзья.
Так как в после прошлого поста несколько человек заинтересовались моей таблицей, решил поделиться с вами еще одной своей таблицей.
Есть у нас лаборатория, и мы постоянно сверяем результаты наших исследований с результатами контрагентов. По нормативным документам нужно придерживаться определенных пределов расхождений в результатах.
Чтобы постоянно не открывать таблицу с значениями пределов воспроизведения, селал себе такую таблчку.
Чтобы было понятно, Результаты испытаний записываются в виде X±Δ
где X – результат анализа;
±Δ – погрешность результатов анализа, в нашем случае воспроизводимость..То есть для первого испытания на медь для Пробы 1 результат у нас (H7) 1,30±0,12, а у контрагентов (ячейка C7) 4,81±0,12. А разница между результатами 4,81-1,30=3,51
Мы не входим в предел воспроизведения, ячейка M7 окрасилась в красный и сразу видим, что и один из нас хочет другого немного обмануть)) Если бы ячейка стала зеленой, то все норм.
Вот чтобы такие расчеты постоянно не делать, была создана данная таблица.
Данные вычисления могут быть полезны чтобы узнать и в других областях, где нужно узнать, вписываемся мы в пределы или нет.
Вот так выглядит рабочая таблица на странице Данные:
Левая табличка разделена на две чати — наши данные при отгрузке продукта и данные контрагента при приемке продкта. В правой табличке, соответственно производятся все вычисления и ячейка окрашивается в определенный цвет при выполнении и невыполнении условий.
Также имеется вторая табличка на странице Пределы, где расписаны пределы по диапазонам:
Вычисления производятся по желтым ячейкам, а розовые для информации. Первый и третий столбци по сути одно и то же.
Читайте также: Как китайцы подделывают куриные яйца
Итак погнали. Что тут творится вообще ))
Буду объяснять для пробы 1, результаты Cu, ячейки M7 и N7. Остальное аналогично
Сперва вычислим раницу между нашими результатами испытаний. Нам нужны только абсолютные значения, так как разность может быть отрицательной. В M7 ввоим формулу:
В N7 вводим следующую формулу:
Тут остановимся, разберем формулу по частям:
Берем значение из ячейки H7 (это наш результат) и ищем на странице Пределы в массиве для Cu пределы значений, куда входит наш результат. Находим, что походит диапазон 1,2-1,6
Ищем номер строки значениея из ячейки H7 в таблице на листе Пределы. В предыдущей формуле мы нашли, что значение относится к пределам 1,2-1,6 и теперь легком можем найти номер строки, где он находится.
Так, номер строки нашли, и нам надо узнать значение погрешности или воспроизведения. Тут нам поможет функция ИНДЕКС, который возвращает значение на пересечении указанных номеров строки и столбца в массиве.Номер строки мы узнали из предыдущей формулы, номер столбца, где нужно искать результат укажем вручную:
Тут Пределы!$B$4:$C$13 это массив где мы делаем поиск
ПОИСКПОЗ(ВПР(Данные!H7;Пределы!$A$4:$C$13;3;ИСТИНА);Пределы!$C$4:$C$13;0) — номер строки.
И единичка в конце — номер столбца.
Теперь мы узнали, что наш результат должен быть 1,30±0,12
А разница результатов двух предприятий 3,51. Это означает, что мы не входим в предел воспроизведения.
Чтобы визуально сразу увидеть это, окрасим эту ячейку в красный. Делается это через меню Условное форматирование
Выбираем в меню Условное форматирование — Правила выделения ячеек — Больше (Меньше) и задаем форматирование — окрасить ячейку в красный или зеленый цвет.
Также у нас есть ограничение в поставке продукта. Качество должно быть не менее определенного значения. Чтобы тоже сразу наглядно это увидеть, я через Условное форматирование выбрал пункт Между.. и задал нужные значения
Если отгрузим товар с качеством по меди меньше 1,5%, то ячейка окрашивается в красный цвет.
Спасибо что дочитали, надеюсь кому-нибудь пригодится данная таблица или формулы.