Получилось так, что некоторые мои сообщения по ряду причин с форума http://forum.s-body.com/index.php?board=34.0 удаляются. Но я их храню в своих архивах и восстанавливаю на этом ресурсе в виде записей в блоге. Вот моё самое первое сообщение на том форуме:
------------ (начало удаленного сообщения)
Здравствуйте, фунтики! Принимайте в свою компанию! Это мое дебютное сообщение на этом Форуме.
Пока только в основном читаю вас и подбираюсь к МФ, неспешно проводя разгон. Но на днях подумал, что пора бы и свой посильный вклад начать вносить в копилку сообщества. Собственно, поэтому и пишу.
Для расчетов ТВН (типичный вес недели) и СКН (средняя калорийность недели) в ручном режиме я использую программу Excel. И думаю, что я не одинок в этом. Но, возможно, не все знают о существовании встроенных в эту программу функциях, особенно полезных фунтику в его расчетах.
В первую очередь, это функция МЕДИАНА, удобная при расчете ТВН. Если ввести в ячейки с A1 по G1 семь значений веса с "понедельника" по "воскресенье", то в ячейке I1 можно записать формулу для расчета ТВН по медиане:
А теперь - внимание! Далее будет представлена формула для расчета ТВН "по-взрослому" - через трехсреднее значение Тьюки, дающая те же результаты, что и калькулятор МФ (теория здесь: http://www.minusfunt.ru/su.pdf ).
Для этой формулы потребуется еще одно (восьмое) значение - вес в "понедельник" новой недели (насколько я могу судить, калькулятор считает именно по восьми значениям и эта причина того, что в него вводятся 15, а не 14 значений веса за две последние недели). Запишем восьмое значение в ячейку H1, которую мы предусмотрительно перед этим пропустили (собственно, и медиану выше можно считать не по 7, а по 8 значениям). Формула для ячейки J1 будет выглядеть так:
или - с округлением до двух знаков после запятой - так:
Формулы выше приведены для русской версии Excel, разделитель списка - точка с запятой. При необходимости адаптации к версиям Excel других стран следует уточнить локализованные имена соответствующих функций. Так, в английской версии МЕДИАНА - это MEDIAN, КВАРТИЛЬ - QUARTILE, ОКРУГЛ - ROUND. Разделитель списка в английской версии - запятая. Последняя формула в английской версии будет выглядеть так:
Всем успехов в расчетах!
------------ (конец удаленного сообщения)
В ходе обсуждения http://forum.s-body.com/index.php?topic=1181.60 мне было указано (сейчас также удалено оттуда), что формула с квартилями не всегда совпадает с расчетами калькулятора МФ: http://www.minusfunt.ru/cabinet/calc . Это дало толчок дальнейшим изысканиям и окончательный ключик к секрету калькулятора МФ был подобран.
По этому поводу готовилось еще одно сообщение, но опубликовать его тогда не получилось. Теперь же вот и оно:
------------ (начало неопубликованного сообщения)
Обещанная коррекция ранее представленной формулы ТрёхСреднего Тьюки (ТСТ) с квартилями. На этот раз с явными цитатами (а не просто ссылками), чтобы не сложилось впечатление, что миру незаконно предъявляется какая-то секретная информация ДСП.
И в другом источнике существует важное замечание о том, какой именно ряд значений следует использовать:
Ранее представленная 13.06.2013 формула использовала 8 последовательных значений веса из диапазона A1:H1 - с понедельника по новый понедельник. Следуя указаниям второй цитаты, теперь для формирования ряда из 14 значений МТ вес по понедельникам в ячейках A1 и H1 надо использовать по одному разу и вес в остальные дни недели в ячейках B1:G1 - по два раза. Таким образом, ссылку A1:H1 в формуле можно заменить на (A1;B1:G1;B1:G1;H1) :
С использованием массивов констант формуле можно придать более компактный окончательный вид:
Эта формула и вычисляет ТСТ так, как нам хотелось бы.
------------ (конец неопубликованного сообщения)
Итак, мы имеем формулу в Excel для расчета типичного веса недели как трехсреднего значения Тьюки для ряда ежедневных в течение недели значений массы тела. Как видно, формула содержит функцию КВАРТИЛЬ, которая скрывает в себе всю громоздкую элементарную арифметику. Создав эту формулу, вычисляющую ТВН так же, как и калькулятор МФ на сайте (по крайней мере, в его текущей реализации по состоянию на август 2013), мы невольно стали заложниками Excel. Само по себе это не так уж и плохо, но всё-таки возможно ли вычислить трехсреднее Тьюки при помощи обычного калькулятора, умеющего выполнять всего лишь четыре основных арифметических действия?
Оказывается, возможно. Будет несколько сложнее, чем нахождение медианы для ряда из 7 значений. К слову сказать, медиана-то находится вообще без расчетов - просто берется 4-е значение из отсортированного по возрастанию (или убыванию) ряда. Для ручного же расчета трехсреднего Тьюки на обычном (бухгалтерском) калькуляторе придется умножать (не более 6 раз), складывать (не более 5 раз) и делить (и не более 1 раза).
Но перед тем как выполнять арифметические действия, подготовим ряд из 14 значений массы тела за 8 дней. По одному разу в этот ряд включаются веса в "понедельник" (1) и в "понедельник новой недели" (1нов или 8 ), для остальных дней - со "вторника" (2) по "воскресенье" (7) - веса включаются по два раза. В итоге получается такой ряд из 14 значений веса по дням недели:
Эти 14 весов сортируются по возрастанию и получаем такой ряд значений:
Точная, совпадающая с калькулятором МФ на 100%, формула для трехсреднего значения Тьюки после преобразований квартилей через элементы списка из 14 значений будет выглядеть так:
Т.е. для расчета нам, таким образом, требуются всего 6 значений из 14 из возрастающего списка: 4зн,5зн,7зн,8зн,10зн,11зн. Желающие могут самостоятельно поразбираться с тем, как работает функция КВАРТИЛЬ в Excel.
Переходя к целым числам, умножив дробные коэффициенты и общий знаменатель на 4, получаем более компактный вид формулы с точки зрения количества требуемых операций и вообще нажатий клавиш на бухгалтерском калькуляторе:
В этой новой формуле с функцией НАИМЕНЬШИЙ хорошо просматриваются участвующие члены ряда значений, а также их целочисленные коэффициенты.
Но всё же использование формулы с функцией КВАРТИЛЬ предпочтительнее, поскольку формула с КВАРТИЛЬ начинает выдавать результат даже при неполной неделе, как только введено хотя бы одно значение. По мере заполнения недели это значение изменяется и после ввода восьмого значения (понедельник новой недели) становится окончательным.
Формуле же с НАИМЕНЬШИЙ требуется полностью заполненная неделя и следующий понедельник. Формально формула что-то засчитает как только сможет определить 11 значений ряда, что произойдет при 6 заполненных днях недели, но результат такой неполной недели будет лишен всякого смысла. При меньшем количестве заполненных дней формула с НАИМЕНЬШИЙ будет возвращать ошибку #ЧИСЛО!.
Все рассмотренные варианты формул для ТВН по Тьюки можно найти в прилагаемом файле Excel:
https://docs.google.com/file/d/0B12BTJG-Ola4MzBDb3pMal9najg/edit?usp=sharing
В этот файл как в "единый флакон" добавлены также формулы генерации скрипта для загрузки данных в калькулятор МФ из предыдущего сообщения от 13.08.2013. Таким образом, файл текущего сообщения можно рассматривать как предварительный шаблон "Дневника худеющего по системе МФ" (отсюда название файла - mfDiary). При желании из таблицы можно удалить неинтересующие столбцы или добавить свои, например, для дополнительных расчетов или комментариев.
ВНИМАНИЕ! Иногда при клике на ссылке, ведущей к файлу, появляется сообщение об ошибке. Бояться не надо, просто подтвердите его. Далее в пункте меню Файл на открывшейся странице (не путать с верхним Файл самого браузера) следует воспользоваться пунктом Скачать - и файл в вашем распоряжении.
------------ (начало удаленного сообщения)
Здравствуйте, фунтики! Принимайте в свою компанию! Это мое дебютное сообщение на этом Форуме.
Пока только в основном читаю вас и подбираюсь к МФ, неспешно проводя разгон. Но на днях подумал, что пора бы и свой посильный вклад начать вносить в копилку сообщества. Собственно, поэтому и пишу.
Для расчетов ТВН (типичный вес недели) и СКН (средняя калорийность недели) в ручном режиме я использую программу 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). При желании из таблицы можно удалить неинтересующие столбцы или добавить свои, например, для дополнительных расчетов или комментариев.
ВНИМАНИЕ! Иногда при клике на ссылке, ведущей к файлу, появляется сообщение об ошибке. Бояться не надо, просто подтвердите его. Далее в пункте меню Файл на открывшейся странице (не путать с верхним Файл самого браузера) следует воспользоваться пунктом Скачать - и файл в вашем распоряжении.
