Как в excel просчитать процентное отклонение от нормы?
pollux87io
Дата: Суббота, 21.01.2017, 16:52 |
Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
Подскажите пожалуйста как в excel просчитать процентное отклонение от нормы? Например анализируя результаты каких-либо медицинских анализов нужно узнать какое отклонение от нормы в %.
Подскажите пожалуйста как в excel просчитать процентное отклонение от нормы? Например анализируя результаты каких-либо медицинских анализов нужно узнать какое отклонение от нормы в %. pollux87io
Сообщение отредактировал pollux87io - Суббота, 21.01.2017, 16:53
Ответить
Сообщение Подскажите пожалуйста как в excel просчитать процентное отклонение от нормы? Например анализируя результаты каких-либо медицинских анализов нужно узнать какое отклонение от нормы в %. Автор - pollux87io Дата добавления - 21.01.2017 в 16:52
Udik
Дата: Суббота, 21.01.2017, 17:41 |
Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1588
Репутация:
192
±
Замечаний:
0% ±
Excel 2016 х 64
Если не напутал, то такКод
=ЕСЛИ(B2<ЗНАЧЕН(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3)));100*(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3))-B2)/СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3));ЕСЛИ(B2>ЗНАЧЕН(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3)));100*(B2-СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3)))/СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3));0))
Если не напутал, то такКод
=ЕСЛИ(B2<ЗНАЧЕН(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3)));100*(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3))-B2)/СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3));ЕСЛИ(B2>ЗНАЧЕН(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3)));100*(B2-СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3)))/СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3));0))
Udik
К сообщению приложен файл:
0t.xlsx
(8.6 Kb)
вот вам барабан яд 41001231307558 wm R419131876897 udik1968@gmail.com
Ответить
Сообщение Если не напутал, то такКод
=ЕСЛИ(B2<ЗНАЧЕН(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3)));100*(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3))-B2)/СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3));ЕСЛИ(B2>ЗНАЧЕН(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3)));100*(B2-СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3)))/СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(C2;"-";" ");3));0))
Автор - Udik Дата добавления - 21.01.2017 в 17:41
pollux87io
Дата: Суббота, 21.01.2017, 18:13 |
Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
Спасибо Вам большое!!!!
Ответить
Сообщение Спасибо Вам большое!!!! Автор - pollux87io Дата добавления - 21.01.2017 в 18:13
AlexM
Дата: Суббота, 21.01.2017, 19:58 |
Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4495
Репутация:
1116
±
Замечаний:
0% ±
Excel 2003
Еще вариантКод
=ABS(КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(B2&"-"&C2;"-";" ");{1;11;21};10);2)-B2)/КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(B2&"-"&C2;"-";" ");{1;11;21};10);2)%
Еще вариантКод
=ABS(КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(B2&"-"&C2;"-";" ");{1;11;21};10);2)-B2)/КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(B2&"-"&C2;"-";" ");{1;11;21};10);2)%
AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение Еще вариантКод
=ABS(КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(B2&"-"&C2;"-";" ");{1;11;21};10);2)-B2)/КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(B2&"-"&C2;"-";" ");{1;11;21};10);2)%
Автор - AlexM Дата добавления - 21.01.2017 в 19:58
pollux87io
Дата: Суббота, 21.01.2017, 21:51 |
Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
Спасибо вы все гении Excelя!!! И как вы только все это так делаете!!!???
Спасибо вы все гении Excelя!!! И как вы только все это так делаете!!!??? pollux87io
Ответить
Сообщение Спасибо вы все гении Excelя!!! И как вы только все это так делаете!!!??? Автор - pollux87io Дата добавления - 21.01.2017 в 21:51
AlexM
Дата: Суббота, 21.01.2017, 23:06 |
Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4495
Репутация:
1116
±
Замечаний:
0% ±
Excel 2003
как вы только все это так делаете
Можно рассказать. Фрагмент формулы B2&"-"&C2 создает текстовую строку "20-0-3 " (для второй строки данных) Функция ПОДСТАВИТЬ() вместо "-" ставит 10 пробелов. Функция ПСТР() дробит строку по 10 символов начиная с 1,11 и 21 позиции. Получается массив из трех текстовых элементов содержащие числа. Два минуса перед ПСТР() делает массив из трех чисел в числовом формате. Функция КВАРТИЛЬ() со вторым аргументом 2 из полученного массива {20;0;3} выбирает медиану, т.е. значение между мин и макс. В данном массиве это число 3. Формула отклонения для рассмотренных выше данных 100*abs(3-20)/3 или что тоже самое abs(3-20)/3% abs - чтобы не получилось отрицательного числа 3 - медиана. 20 - показатель.
как вы только все это так делаете
Можно рассказать. Фрагмент формулы B2&"-"&C2 создает текстовую строку "20-0-3 " (для второй строки данных) Функция ПОДСТАВИТЬ() вместо "-" ставит 10 пробелов. Функция ПСТР() дробит строку по 10 символов начиная с 1,11 и 21 позиции. Получается массив из трех текстовых элементов содержащие числа. Два минуса перед ПСТР() делает массив из трех чисел в числовом формате. Функция КВАРТИЛЬ() со вторым аргументом 2 из полученного массива {20;0;3} выбирает медиану, т.е. значение между мин и макс. В данном массиве это число 3. Формула отклонения для рассмотренных выше данных 100*abs(3-20)/3 или что тоже самое abs(3-20)/3% abs - чтобы не получилось отрицательного числа 3 - медиана. 20 - показатель.AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение как вы только все это так делаете
Можно рассказать. Фрагмент формулы B2&"-"&C2 создает текстовую строку "20-0-3 " (для второй строки данных) Функция ПОДСТАВИТЬ() вместо "-" ставит 10 пробелов. Функция ПСТР() дробит строку по 10 символов начиная с 1,11 и 21 позиции. Получается массив из трех текстовых элементов содержащие числа. Два минуса перед ПСТР() делает массив из трех чисел в числовом формате. Функция КВАРТИЛЬ() со вторым аргументом 2 из полученного массива {20;0;3} выбирает медиану, т.е. значение между мин и макс. В данном массиве это число 3. Формула отклонения для рассмотренных выше данных 100*abs(3-20)/3 или что тоже самое abs(3-20)/3% abs - чтобы не получилось отрицательного числа 3 - медиана. 20 - показатель.Автор - AlexM Дата добавления - 21.01.2017 в 23:06
pollux87io
Дата: Суббота, 21.01.2017, 23:24 |
Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
Большое Вам спасибо!!!
Ответить
Сообщение Большое Вам спасибо!!! Автор - pollux87io Дата добавления - 21.01.2017 в 23:24
pollux87io
Дата: Воскресенье, 22.01.2017, 01:13 |
Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
AlexM , что-то пошло не так....
Ответить
Сообщение AlexM , что-то пошло не так....Автор - pollux87io Дата добавления - 22.01.2017 в 01:13
AlexM
Дата: Воскресенье, 22.01.2017, 09:34 |
Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4495
Репутация:
1116
±
Замечаний:
0% ±
Excel 2003
формат данных пошел не так. Числа стали дробные, одно значение для нормы, а не диапазон. Так получилось, потому что задача описана не точно. Исправленная формулаКод
=ABS(КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(D2&"-"&E2&"-"&E2;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);2)-D2)/КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(D2&"-"&E2&"-"&E2;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);2)%
В формуле коллеги Udik значения отмеченные цветом не совпадают со значениями моей формулы. Это связано с тем, что в его формуле функция ПОДСТАВИТЬ() заменяет "-" всего на 5 пробелов, в функции ЛЕВСИМВ() и ПРАВСИМВ() из текстовой строки выбирают по три символа, что недостаточно, где дробные числа более 3 символов, например 3,57 или 69,49. В столбце Н я исправил и сократил формулу Udik. В столбцах "Проверка формул я показал какие значения подставляются в формулу расчета отклонения.
формат данных пошел не так. Числа стали дробные, одно значение для нормы, а не диапазон. Так получилось, потому что задача описана не точно. Исправленная формулаКод
=ABS(КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(D2&"-"&E2&"-"&E2;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);2)-D2)/КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(D2&"-"&E2&"-"&E2;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);2)%
В формуле коллеги Udik значения отмеченные цветом не совпадают со значениями моей формулы. Это связано с тем, что в его формуле функция ПОДСТАВИТЬ() заменяет "-" всего на 5 пробелов, в функции ЛЕВСИМВ() и ПРАВСИМВ() из текстовой строки выбирают по три символа, что недостаточно, где дробные числа более 3 символов, например 3,57 или 69,49. В столбце Н я исправил и сократил формулу Udik. В столбцах "Проверка формул я показал какие значения подставляются в формулу расчета отклонения. AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение формат данных пошел не так. Числа стали дробные, одно значение для нормы, а не диапазон. Так получилось, потому что задача описана не точно. Исправленная формулаКод
=ABS(КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(D2&"-"&E2&"-"&E2;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);2)-D2)/КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(D2&"-"&E2&"-"&E2;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);2)%
В формуле коллеги Udik значения отмеченные цветом не совпадают со значениями моей формулы. Это связано с тем, что в его формуле функция ПОДСТАВИТЬ() заменяет "-" всего на 5 пробелов, в функции ЛЕВСИМВ() и ПРАВСИМВ() из текстовой строки выбирают по три символа, что недостаточно, где дробные числа более 3 символов, например 3,57 или 69,49. В столбце Н я исправил и сократил формулу Udik. В столбцах "Проверка формул я показал какие значения подставляются в формулу расчета отклонения. Автор - AlexM Дата добавления - 22.01.2017 в 09:34
AlexM
Дата: Воскресенье, 22.01.2017, 09:41 |
Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4495
Репутация:
1116
±
Замечаний:
0% ±
Excel 2003
Укороченная формулаКод
=ABS(1-D2/КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(D2&"-"&E2&"-"&E2;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);2))/1%
получилась преобразованием формулы abs(X-Y)/X% в abs(X/X-Y/X)/X/X% и далее в abs(1-Y/X)/1% . Где Y - значение из столбца D, а X - длинный фрагмент формулы с функцией КВАРТИЛЬ(). В укороченном варианте этот фрагмент используется один раз, а не два.
Укороченная формулаКод
=ABS(1-D2/КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(D2&"-"&E2&"-"&E2;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);2))/1%
получилась преобразованием формулы abs(X-Y)/X% в abs(X/X-Y/X)/X/X% и далее в abs(1-Y/X)/1% . Где Y - значение из столбца D, а X - длинный фрагмент формулы с функцией КВАРТИЛЬ(). В укороченном варианте этот фрагмент используется один раз, а не два. AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Сообщение отредактировал AlexM - Воскресенье, 22.01.2017, 10:20
Ответить
Сообщение Укороченная формулаКод
=ABS(1-D2/КВАРТИЛЬ(--ПСТР(ПОДСТАВИТЬ(D2&"-"&E2&"-"&E2;"-";ПОВТОР(" ";99));99*{0;1;2}+1;99);2))/1%
получилась преобразованием формулы abs(X-Y)/X% в abs(X/X-Y/X)/X/X% и далее в abs(1-Y/X)/1% . Где Y - значение из столбца D, а X - длинный фрагмент формулы с функцией КВАРТИЛЬ(). В укороченном варианте этот фрагмент используется один раз, а не два. Автор - AlexM Дата добавления - 22.01.2017 в 09:41
pollux87io
Дата: Воскресенье, 22.01.2017, 10:32 |
Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
AlexM , можете, пожалуйста, здесь внести необходимые изменения, это окончательный вариант больше данных не будет
AlexM , можете, пожалуйста, здесь внести необходимые изменения, это окончательный вариант больше данных не будетpollux87io
Сообщение отредактировал pollux87io - Воскресенье, 22.01.2017, 10:35
Ответить
Сообщение AlexM , можете, пожалуйста, здесь внести необходимые изменения, это окончательный вариант больше данных не будетАвтор - pollux87io Дата добавления - 22.01.2017 в 10:32
AlexM
Дата: Воскресенье, 22.01.2017, 10:42 |
Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4495
Репутация:
1116
±
Замечаний:
0% ±
Excel 2003
Я же формулу показал в сообщении №10
Я же формулу показал в сообщении №10 AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение Я же формулу показал в сообщении №10 Автор - AlexM Дата добавления - 22.01.2017 в 10:42
pollux87io
Дата: Воскресенье, 22.01.2017, 13:07 |
Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 27
Репутация:
0
±
Замечаний:
0% ±
Excel 2010
AlexM , спасибо Вам!!!
Ответить
Сообщение AlexM , спасибо Вам!!!Автор - pollux87io Дата добавления - 22.01.2017 в 13:07
_Boroda_
Дата: Воскресенье, 22.01.2017, 15:52 |
Сообщение № 14
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация:
6481
±
Замечаний:
0% ±
2003; 2007; 2010; 2013 RUS
Леш, а почему ты вместо КВАРТИЛЬ(...;2) просто МЕДИАНА не пишешь? Я в решение не вникал, просто вот это прочиталФункция КВАРТИЛЬ() со вторым аргументом 2 из полученного массива {20;0;3} выбирает медиану
Леш, а почему ты вместо КВАРТИЛЬ(...;2) просто МЕДИАНА не пишешь? Я в решение не вникал, просто вот это прочиталФункция КВАРТИЛЬ() со вторым аргументом 2 из полученного массива {20;0;3} выбирает медиану
_Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение Леш, а почему ты вместо КВАРТИЛЬ(...;2) просто МЕДИАНА не пишешь? Я в решение не вникал, просто вот это прочиталФункция КВАРТИЛЬ() со вторым аргументом 2 из полученного массива {20;0;3} выбирает медиану
Автор - _Boroda_ Дата добавления - 22.01.2017 в 15:52
AlexM
Дата: Воскресенье, 22.01.2017, 16:49 |
Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4495
Репутация:
1116
±
Замечаний:
0% ±
Excel 2003
Потому что функция КВАРТИЛЬ() в списке раньше МЕДИАНА(). Не дошел до нее. ;-)
Потому что функция КВАРТИЛЬ() в списке раньше МЕДИАНА(). Не дошел до нее. ;-) AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Сообщение отредактировал AlexM - Воскресенье, 22.01.2017, 17:12
Ответить
Сообщение Потому что функция КВАРТИЛЬ() в списке раньше МЕДИАНА(). Не дошел до нее. ;-) Автор - AlexM Дата добавления - 22.01.2017 в 16:49
Serge_007
Дата: Воскресенье, 22.01.2017, 22:48 |
Сообщение № 16
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация:
2749
±
Замечаний:
±
Excel 2016
функция КВАРТИЛЬ() в списке раньше МЕДИАНА()
Ты что, все функции в алфавитном порядке перебирал пока не нашел подходящую?!
функция КВАРТИЛЬ() в списке раньше МЕДИАНА()
Ты что, все функции в алфавитном порядке перебирал пока не нашел подходящую?! Serge_007
ЮMoney :41001419691823 | WMR :126292472390
Ответить
Сообщение функция КВАРТИЛЬ() в списке раньше МЕДИАНА()
Ты что, все функции в алфавитном порядке перебирал пока не нашел подходящую?! Автор - Serge_007 Дата добавления - 22.01.2017 в 22:48
AlexM
Дата: Воскресенье, 22.01.2017, 23:40 |
Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4495
Репутация:
1116
±
Замечаний:
0% ±
Excel 2003
Как видно по формуле, начал перебор с ABS()
Как видно по формуле, начал перебор с ABS() AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение Как видно по формуле, начал перебор с ABS() Автор - AlexM Дата добавления - 22.01.2017 в 23:40