Минус Фунт: Формула Тьюки для типичного веса недели

Получилось так, что некоторые мои сообщения по ряду причин с форума http://forum.s-body.com/index.php?board=34.0 удаляются. Но я их храню в своих архивах и восстанавливаю на этом ресурсе в виде записей в блоге. Вот моё самое первое сообщение на том форуме:

------------ (начало удаленного сообщения)
Здравствуйте, фунтики! Принимайте в свою компанию! Это мое дебютное сообщение на этом Форуме.

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

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

В первую очередь, это функция МЕДИАНА, удобная при расчете ТВН. Если ввести в ячейки с A1 по G1 семь значений веса с "понедельника" по "воскресенье", то в ячейке I1 можно записать формулу для расчета ТВН по медиане:

=МЕДИАНА(A1:G1)

А теперь - внимание! Далее будет представлена формула для расчета ТВН "по-взрослому" - через трехсреднее значение Тьюки, дающая те же результаты, что и калькулятор МФ (теория здесь: http://www.minusfunt.ru/su.pdf ).

Для этой формулы потребуется еще одно (восьмое) значение - вес в "понедельник" новой недели (насколько я могу судить, калькулятор считает именно по восьми значениям и эта причина того, что в него вводятся 15, а не 14 значений веса за две последние недели). Запишем восьмое значение в ячейку H1, которую мы предусмотрительно перед этим пропустили (собственно, и медиану выше можно считать не по 7, а по 8 значениям). Формула для ячейки J1 будет выглядеть так:

=(КВАРТИЛЬ(A1:H1;1)+2*КВАРТИЛЬ(A1:H1;2)+КВАРТИЛЬ(A1:H1;3))/4

или - с округлением до двух знаков после запятой - так:

=ОКРУГЛ((КВАРТИЛЬ(A1:H1;1)+2*КВАРТИЛЬ(A1:H1;2)+КВАРТИЛЬ(A1:H1;3))/4;2)

Формулы выше приведены для русской версии Excel, разделитель списка - точка с запятой. При необходимости адаптации к версиям Excel других стран следует уточнить локализованные имена соответствующих функций. Так, в английской версии МЕДИАНА - это MEDIAN, КВАРТИЛЬ - QUARTILE, ОКРУГЛ - ROUND. Разделитель списка в английской версии - запятая. Последняя формула в английской версии будет выглядеть так:

=ROUND((QUARTILE(A1:H1,1)+2*QUARTILE(A1:H1,2)+QUARTILE(A1:H1,3))/4,2)

Всем успехов в расчетах!
------------ (конец удаленного сообщения)

В ходе обсуждения http://forum.s-body.com/index.php?topic=1181.60 мне было указано (сейчас также удалено оттуда), что формула с квартилями не всегда совпадает с расчетами калькулятора МФ:  http://www.minusfunt.ru/cabinet/calc . Это дало толчок дальнейшим изысканиям и окончательный ключик к секрету калькулятора МФ был подобран.

По этому поводу готовилось еще одно сообщение, но опубликовать его тогда не получилось. Теперь же вот и оно:

------------ (начало неопубликованного сообщения)
Обещанная коррекция ранее представленной формулы ТрёхСреднего Тьюки (ТСТ) с квартилями. На этот раз с явными цитатами (а не просто ссылками), чтобы не сложилось впечатление, что миру незаконно предъявляется какая-то секретная информация ДСП.

"Сколько существует способов нормализации избыточной массы тела?" ( http://www.minusfunt.ru/su.pdf )

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

ТСТ = (q1 + 2q2 + q3) / 4 ,

где q1, q2, q3 – соответственно 1, 2, 3 квартили ряда значений.

И в другом источнике существует важное замечание о том, какой именно ряд значений следует использовать:

Кромер В.В. (Русско-немецкий университет, г. Новосибирск) "Автоматическое регулирование при диетотерапии" ( http://kromer.newmail.ru/kvv_c_52.htm )

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

Ранее представленная 13.06.2013 формула использовала 8 последовательных значений веса из диапазона A1:H1 - с понедельника по новый понедельник. Следуя указаниям второй цитаты, теперь для формирования ряда из 14 значений МТ вес по понедельникам в ячейках A1 и H1 надо использовать по одному разу и вес в остальные дни недели в ячейках B1:G1 - по два раза. Таким образом, ссылку A1:H1 в формуле можно заменить на (A1;B1:G1;B1:G1;H1) :

=ОКРУГЛ((КВАРТИЛЬ(A1:H1;1)+2*КВАРТИЛЬ(A1:H1;2)+КВАРТИЛЬ(A1:H1;3))/4;2)

=ОКРУГЛ((КВАРТИЛЬ((A1;B1:G1;B1:G1;H1);1)+2*КВАРТИЛЬ((A1;B1:G1;B1:G1;H1);2)+КВАРТИЛЬ((A1;B1:G1;B1:G1;H1);3))/4;2)

С использованием массивов констант формуле можно придать более компактный окончательный вид:

=ОКРУГЛ(СУММ(КВАРТИЛЬ((A1;B1:G1;B1:G1;H1);{1;2;2;3}))/4;2)

Эта формула и вычисляет ТСТ так, как нам хотелось бы.
------------ (конец неопубликованного сообщения)

Итак, мы имеем формулу в Excel для расчета типичного веса недели как трехсреднего значения Тьюки для ряда ежедневных в течение недели значений массы тела. Как видно, формула содержит функцию КВАРТИЛЬ, которая скрывает в себе всю громоздкую элементарную арифметику. Создав эту формулу, вычисляющую ТВН так же, как и калькулятор МФ на сайте (по крайней мере, в его текущей реализации по состоянию на август 2013), мы невольно стали заложниками Excel. Само по себе это не так уж и плохо, но всё-таки возможно ли вычислить трехсреднее Тьюки при помощи обычного калькулятора, умеющего выполнять всего лишь четыре основных арифметических действия?

Оказывается, возможно. Будет несколько сложнее, чем нахождение медианы для ряда из 7 значений. К слову сказать, медиана-то находится вообще без расчетов - просто берется 4-е значение из отсортированного по возрастанию (или убыванию) ряда. Для ручного же расчета трехсреднего Тьюки на обычном (бухгалтерском) калькуляторе придется умножать (не более 6 раз), складывать (не более 5 раз) и делить (и не более 1 раза).

Но перед тем как выполнять арифметические действия, подготовим ряд из 14 значений массы тела за 8 дней. По одному разу в этот ряд включаются веса в "понедельник" (1) и в "понедельник новой недели" (1нов или 8 ), для остальных дней - со "вторника" (2) по "воскресенье" (7) - веса включаются по два раза. В итоге получается такой ряд из 14 значений веса по дням недели:

1, 2,2, 3,3, 4,4, 5,5, 6,6, 7,7, 1нов

Эти 14 весов сортируются по возрастанию и получаем такой ряд значений:

1зн,2зн,3зн,4зн,5зн,6зн,7зн,8зн,9зн,10зн,11зн,12зн,13зн,14зн

Точная, совпадающая с калькулятором МФ на 100%, формула для трехсреднего значения Тьюки после преобразований квартилей через элементы списка из 14 значений будет выглядеть так:

ТСТ = ( 0,75*4зн + 0,25*5зн + 7зн + 8зн + 0,25*10зн + 0,75*11зн ) / 4

Т.е. для расчета нам, таким образом, требуются всего 6 значений из 14 из возрастающего списка: 4зн,5зн,7зн,8зн,10зн,11зн. Желающие могут самостоятельно поразбираться с тем, как работает функция КВАРТИЛЬ в Excel.

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

ТСТ = [ 3*(4зн + 11зн) + 5зн + 10зн + 4*(7зн + 8зн) ] / 16

Из последней формулы для ручного расчета вытекает альтернативная формула для Excel. С чего начали - к тому же и вернулись, но уже без функции КВАРТИЛЬ:

=ОКРУГЛ(СУММ(НАИМЕНЬШИЙ((A1;B1:G1;B1:G1;H1);{4;5;7;8;10;11})*{3;1;4;4;1;3})/16;2)

В этой новой формуле с функцией НАИМЕНЬШИЙ хорошо просматриваются участвующие члены ряда значений, а также их целочисленные коэффициенты.

Но всё же использование формулы с функцией КВАРТИЛЬ предпочтительнее, поскольку формула с КВАРТИЛЬ начинает выдавать результат даже при неполной неделе, как только введено хотя бы одно значение. По мере заполнения недели это значение изменяется и после ввода восьмого значения (понедельник новой недели) становится окончательным.

Формуле же с НАИМЕНЬШИЙ требуется полностью заполненная неделя и следующий понедельник. Формально формула что-то засчитает как только сможет определить 11 значений ряда, что произойдет при 6 заполненных днях недели, но результат такой неполной недели будет лишен всякого смысла. При меньшем количестве заполненных дней формула с НАИМЕНЬШИЙ будет возвращать ошибку #ЧИСЛО!.

Все рассмотренные варианты формул для ТВН по Тьюки можно найти в прилагаемом файле Excel:
https://docs.google.com/file/d/0B12BTJG-Ola4MzBDb3pMal9najg/edit?usp=sharing

В этот файл как в "единый флакон" добавлены также формулы генерации скрипта для загрузки данных в калькулятор МФ из предыдущего сообщения от 13.08.2013. Таким образом, файл текущего сообщения можно рассматривать как предварительный шаблон "Дневника худеющего по системе МФ" (отсюда название файла - mfDiary). При желании из таблицы можно удалить неинтересующие столбцы или добавить свои, например, для дополнительных расчетов или комментариев.

ВНИМАНИЕ! Иногда при клике на ссылке, ведущей к файлу, появляется сообщение об ошибке. Бояться не надо, просто подтвердите его. Далее в пункте меню Файл на открывшейся странице (не путать с верхним Файл самого браузера) следует воспользоваться пунктом Скачать - и файл в вашем распоряжении.