Но я бы хотел показать несколько примеров использования стандартных функций листа программы Excel. Достаточно часто приходится городить огород из формул, чтобы вытащить из ячеек нужный текст или числа. Происходит это как правило из-за неверного представления данных. Числа — это числа, текст — это текст, не надо их смешивать. Даты (и время) это числа, с которыми можно и нужно производить вычисления. Не надо путать 14.03.2018 с 14 марта 2018 г. Второе это только пользовательский формат даты [$-F800], то, что мы видим в ячейке, а в строке формул будет 14.03.2018. И хватит об этом.
1. Допустим у вас есть таблица с временами начала и окончания каких-то событий, например, телефонных разговоров.
На рисунке часть таблицы _t2. Необходимо вычислить максимальное одновременное количество событий. Для этого есть такая формула:
=МАКС(МУМНОЖ((_t2[start]>=ТРАНСП(_t2[start]))*(_t2[start]<=ТРАНСП(_t2[end]));СТРОКА(_t2[start])^0))
Или в английской локали
=MAX(MMULT((_t2[start]>=TRANSPOSE(_t2[start]))*(_t2[start]<=TRANSPOSE(_t2[end])),ROW(_t2[start])^0))Формула массивная, ввод подтверждается одновременным нажатием кнопок Control, Shift, Enter (CSE). В строке формул будет видно, что формула в фигурных скобках. МУМНОЖ делает виртуальную матрицу размером число строк таблица на число строк таблицы, в строках которой результаты вычисления, как если бы в строки таблицы была введена следующая формула (для второй строки) =СУММПРОИЗВ(Ч((C$2:C2>A2)))/ =SUMPRODUCT(N((C$2:C2>A2))), и протянуть на всю таблицу.
2. То же самое, но посложнее. Кроме начала и окончания есть количество агрегатов или отдаваемая/получаемая мощность. Необходимо определить максимальную мощность в какой-то момент времени.
Часть таблицы _t1:
И сама формула:
=МАКС(МУМНОЖ((ТРАНСП(_t1[start]*1440)<=СТРОКА(1:1436)-1)*(ТРАНСП(_t1[stop]*1440)>=СТРОКА(1:1436)-1);_t1[power]))
=MAX(MMULT((TRANSPOSE(_t1[start]*1440)<=ROW(1:1436)-1)*(TRANSPOSE(_t1[stop]*1440)>=ROW(1:1436)-1),_t1[power]))Поскольку время — это доля единицы, умножаем значения на количество минут в сутках, чтобы получить целые числа. И второй аргумент МУМНОЖ – столбец значений мощности. В первом случае был сформирован столбец единиц.
3. Слева в таблице представлены суммы, соответствующие определенным интервалам. Необходимо вычислить суммы по месяцам. Предполагается, что суммы распределены равномерно внутри своих интервалов.
В столбце Н первые числа месяцев, в столбце I следующие формулы:
=СУММ(_tis[sum per day]*ЕСЛИОШИБКА((ЕСЛИ(КОНМЕСЯЦА(H3;0)>_tis[end];_tis[end];КОНМЕСЯЦА(H3;0))-ЕСЛИ(H3>_tis[start];H3;_tis[start])+1)^0,5;)^2)
=SUM(_tis[sum per day]*IFERROR((IF(EOMONTH(H3,0)>_tis[end],_tis[end],EOMONTH(H3,0))-IF(H3>_tis[start],H3,_tis[start])+1)^0.5,)^2)Формулы массивные. Можно было бы использовать СУММПРОИЗВ/SUMPRODUCT, но ЕСЛИОШИБКА не работает без массивного ввода. Также, к сожалению, МАКС и МИН не могут сформировать виртуальный массив значений. Поэтому для вычисления частей интервалов, приходящихся на месяц, используется ЕСЛИ. Так как полученные части могут быть отрицательными, искусственно вызывается ошибка (квадратный корень из отрицательного числа приводит к ней). В исходной таблице можно было обойтись без столбцов длительности интервала и дневной суммы. Формула стала бы немного длиннее.
4. Небольшое развлечение. Сколько в году может быть счастливых пятниц?
Количество пятниц:
=СУММПРОИЗВ((ДЕНЬ(СТРОКА(ИНДЕКС(A:A;B2):ИНДЕКС(A:A;C2)))=13)*(ДЕНЬНЕД(СТРОКА(ИНДЕКС(A:A;B2):ИНДЕКС(A:A;C2));2)=5))
=SUMPRODUCT((DAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)))=13)*(WEEKDAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)),2)=5))Даты пятниц:
=ЕСЛИОШИБКА(АГРЕГАТ(15;6;СТРОКА(ИНДЕКС($A:$A;$B2):ИНДЕКС($A:$A;$C2))/(ДЕНЬ(СТРОКА(ИНДЕКС($A:$A;$B2):ИНДЕКС(A:A;$C2)))=13)/(ДЕНЬНЕД(СТРОКА(ИНДЕКС($A:$A;$B2):ИНДЕКС($A:$A;$C2));2)=5);СТОЛБЕЦ(A$2));"")
=IFERROR(AGGREGATE(15,6,ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2))/(DAY(ROW(INDEX($A:$A,$B2):INDEX(A:A,$C2)))=13)/(WEEKDAY(ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2)),2)=5),COLUMN(A$2)),"")Это очень хорошо (здесь), что первая строка листа соответствует 01.01.1901. Поэтому достаточно легко сформировать массив дат года с помощью ИНДЕКС и СТРОКА и считать только пятницы тринадцатое.
5. Размер процентной ставки, зависящей от суммы, встречается достаточно часто. Если пользоваться ими неправильно, то график полученных сумм будет такой как красный график ниже:
Чтобы этого избежать, необходимо применять ставку только к части суммы, приходящейся на интервал. А для младших интервалов брать фиксированные суммы.
Для таких исходных данных:
Формула:
=ВПР(A3;tbl;2)*(A3-ВПР(A3;tbl;1))+СУММПРОИЗВ(ЕСЛИОШИБКА((tbl[limit]<=A3)*(tbl[limit]-СМЕЩ(tbl[limit];-1;))*СМЕЩ(tbl[rate];-1;);))
=VLOOKUP(A3,tbl,2)*(A3-VLOOKUP(A3,tbl,1))+SUMPRODUCT(IFERROR((tbl[limit]<=A3)*(tbl[limit]-OFFSET(tbl[limit],-1,))*OFFSET(tbl[rate],-1,),))Формула массивная. В А3 – сумма, для которой нужно вычислить проценты. Исходные данные –tbl.
И, напоследок, кредитная линия с изменяемой процентной ставкой. Формула для расчета суммы процентов в зависимости от дат/сумм траншей/возвратов.
История кредита:
Изменение ставки:
=ЕСЛИ(B1=0;СУММ(ЕСЛИОШИБКА(ПРОСМОТР(_d;_s[[date]:[rate]]);)*ТРАНСП(B5:B16)*ИНДЕКС((_d>ТРАНСП(A5:A16))*(_d<=A2);;)*1/(365+(ОСТАТ(ГОД(_d);4)=0)))-СУММ(ЕСЛИОШИБКА(ПРОСМОТР(_d;_s[[date]:[rate]]);)*ТРАНСП(D5:D16)*ИНДЕКС((_d>ТРАНСП(C5:C16))*(_d<=A2);;)*1/(365+(ОСТАТ(ГОД(_d);4)=0)));)
=IF(B1=0,SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(B5:B16)*INDEX((_d>TRANSPOSE(A5:A16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0)))-SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(D5:D16)*INDEX((_d>TRANSPOSE(C5:C16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0))),)Формула массивная. Считает проценты по дням, учитывает високосные года.
Спасибо за внимание!
Комментарии (59)
qw1
01.05.2018 21:28Статья была бы полезной, если бы автор разжевал все эти редкие функции и сначала показал, как они работают, на тривиальных примерах. А потом уже перешёл бы к реальным применениям.
А тут вывалили сразу трёхэтажный код и разбираться в нём неохота.tim2018 Автор
02.05.2018 19:06Статья не обучающая :). Примеры, кстати, тривиальные. Спрашивайте, отвечу. Выложу ссылки на заинтересовавшие файлы
Imbecile
01.05.2018 21:34Мало того, что стоило бы разжевать формулы, нужно ещё их дважды записывать — в русском и английском варианте. Читать мешанину языков — мозг взрывается.
ky0
01.05.2018 23:40+1Вспомнив формулы из 1С, решил, что тут всё довольно логично, компактно и трививально. Ещё и по-английски можно написать — вообще мечта…
Serge78rus
02.05.2018 10:44Не являюсь специалистом в 1С, но разве там нет возможности записать по-английский и нормально структурировав конструкцию с помощью отступов? В приведенных формулах проблема не только в не читаемых сокращенных русских словах (в конце концов автор приводит и англоязычный вариант, который выглядит лишь ненамного лучше), а еще и в отсутствии структурирования.
tyomitch
02.05.2018 11:01Если вы пользовались Экселем, то вы замечали, что он парные скобки подсвечивает одинаковым цветом. Это, конечно, не так удобно, как отступы, но намного легче читается, чем использованные в статье одноцветные формулы.
Serge78rus
02.05.2018 11:41+1Мой единственный положительный опыт использования Экселя сводится к написанию прайс-листов с автоматическим пересчетом рублевой цены в соответствии с текущим курсом. Но было это во времена версии 1, когда эксель представлял собой именно простую электронную таблицу, да и формула, как Вы понимаете, была простейшей. Более поздние несколько попыток его использовать были с отрицательным результатом, каждый раз приходил к выводу, что я, если и не быстрее, то с меньшей затратой сил и меньшим количеством ошибок напишу свою программу конкретно под данную задачу. А когда обнаружил еще и ошибку в его математике на уровне третьей значащей цифры на определенном наборе данных (по-моему, это была версия из офиса 97), то вообще перестал рассматривать возможность его использования для чего-либо, кроме как визуализации готовых сторонних данных, и это был уже не Эксель, а Calc из Libre Office.
Подсветка парных скобок — это, конечно, лучше, чем ничего, но, если бы авторы Экселя изначально рассчитывали на обработку формул подобной сложности, то должны были бы предусмотреть хоть какое-то структурирование текста.
Lego-engineer
02.05.2018 13:34Скобочки не панацея.
1. Alt+enter дает отступы с новой строки
2. +Ч(«комментарий к формуле») или +N(«комментарий к формуле») с ограничениями — нельзя писать в комментариях только «числа», «даты», «истина» тк эксель будет воспринимать частью формулы
philya
02.05.2018 15:09Писать мегаформулы это путь экономиста ) Программист нажмет правую кнопочку мыши на ярлыке листа, выберет "исходный код" и напишет на VBA функцию с отступами. Далее будет вызывать ее =МояМегаФормула(A1:A100). Там еще и отладчик есть.
SbWereWolf
02.05.2018 01:21спасибо что поделились, но думаю местные не оценят. любому программисту формулы проще на родном SQL самому себе написать, чем на чуждом — пользовательском — Excel.
tyomitch
02.05.2018 09:31Не всегда у программиста есть выбор, на чём писать.
Serge78rus
02.05.2018 10:55Человек, у которого в арсенале инструментов только Эксель, не является программистом в том смысле, в котором это слово трактуется большинством людей.
tyomitch
02.05.2018 11:03Serge78rus
02.05.2018 11:49В соответствии с этим комиксом, я тоже не совсем «real programmer», т.к. в основном пользуюсь не emacs и vim, а Eclipse.
Lego-engineer
02.05.2018 05:53Не самые длинные формулы)
Мой маразм доводил до такого:
помесячное распределение ресурсов между сроками начала и окончания работ с возможностью использования кривых потребления=ЕСЛИ(
ЕСЛИОШИБКА(
ПОИСКПОЗ($A584;'Отдельные данные'!$A$1:$A$600;0);0)>0;
СУММПРОИЗВ('Отдельные данные'!$B$1:$H$600*('Отдельные данные'!$A$1:$A$600='Остатки по сметам 31010'!$A584)*('Отдельные данные'!$B$1:$H$1='Остатки по сметам 31010'!X$1))/СУММПРОИЗВ('Отдельные данные'!$B$1:$H$600*('Отдельные данные'!$A$1:$A$600='Остатки по сметам 31010'!$A584))*$H584;
ЕСЛИ(ИЛИ($H584<0; $L584=0; МЕСЯЦ(X$1)>МЕСЯЦ($M584); МЕСЯЦ(X$1)<МЕСЯЦ($L584));0;
ЕСЛИ(МЕСЯЦ($M584)=МЕСЯЦ($L584); ($M584-$L584+1)*$H584/($M584-$L584+1);
ЕСЛИ(МЕСЯЦ(X$1)=МЕСЯЦ($L584);(ДЕНЬ(КОНМЕСЯЦА(X$1;0))-ДЕНЬ($L584)+1)*$H584/($M584-$L584+1);
ЕСЛИ(МЕСЯЦ(X$1)=МЕСЯЦ($M584); ДЕНЬ($M584)*$H584/($M584-$L584+1);
$H584/($M584-$L584+1)*ДЕНЬ(КОНМЕСЯЦА(X$1;0)))))))Alexandr2001
02.05.2018 09:51Неужели кто-то такое пишет?
Lego-engineer
02.05.2018 10:09+1Пишет. Обоснование элементарное — связка между плановым и сметным отделами через эксель. Подготовка производства так же через эксель.
Конкретно этот случайtyomitch
02.05.2018 10:15+1Если макрос написан по уму и использует Named Ranges вместо абсолютных адресов, то останется рабочим.
По моему опыту, основная проблема с макросами — что с каждой версией Офиса всё сложнее и сложнее разрешить их выполнение, причём эти разрешения забываются при апгрейде.Serge78rus
02.05.2018 11:01А отсутствие структурирования текста конструкции разве не является проблемой?
tyomitch
02.05.2018 11:05Макросы (в отличие от формул) пишутся на VBA.
VBA — это полноценный язык программирования.Serge78rus
02.05.2018 12:07Хотя Бейсик в среде программистов и является словом нецензурным, но я с Вами согласен. Просто в комментарии Lego-engineer присутствует именно малочитаемая формула, а в последующем комментарии ссылка на картинку под катом не рабочая, поэтому я и написал о том, что увидел.
tim2018 Автор
02.05.2018 19:41+1что с каждой версией Офиса всё сложнее и сложнее разрешить их выполнение
отнюдь. версии 2007 и далее. Параметры, центр управления безопасностью, параметры центра управления безопасностью, параметры макросов, отметить нужное.
разрешения забываются при апгрейде
да, ничто не совершенно, настройки панелей тоже улетят. И никто не предлагает сохранить их настройку, хотя это возможно. Но панель макросов и личная книга макросов всё равно в ручную
Lego-engineer
02.05.2018 12:52Поправляюсь.
Ссылка на картинкуBoriska007
02.05.2018 18:16Давным давно, в очередной раз скучая на работе, просто ради шутки собрал в экселе такую формулу:
Скрытый текст=ЕСЛИ(ИЛИ(B4=0;C4=0;D4=0);«Не достаточно данных!»; СЦЕПИТЬ(ЕСЛИ(ИЛИ(И(C4=«апрель»;B4>30); И(C4=«июнь»;B4>30); И(C4=«сентябрь»;B4>30); И(C4=«ноябрь»;B4>30));30; ЕСЛИ(И(C4=«февраль»;B4>28;D4/4=ЦЕЛОЕ(D4/4));29; ЕСЛИ(И(C4=«февраль»;B4>28);28;B4)));" "; ЕСЛИ(C4=«январь»;" января "; ЕСЛИ(C4=«февраль»;" февраля "; ЕСЛИ(C4=«март»;" марта "; ЕСЛИ(C4=«апрель»;" апреля "; ЕСЛИ(C4=«май»;" мая "; ЕСЛИ(C4=«июнь»;" июня "; ЕСЛИ(C4=«июль»;" июля "; ЕСЛИ(C4=«август»;" августа "; ЕСЛИ(C4=«сентябрь»;" сентября "; ЕСЛИ(C4=«октябрь»;" октября "; ЕСЛИ(C4=«ноябрь»;" ноября ";" декабря ")))))))))));" ";D4;" года — "; ЕСЛИ(ДЕНЬНЕД(СЦЕПИТЬ(ЕСЛИ(ИЛИ(И(C4=«апрель»;B4>30); И(C4=«июнь»;B4>30); И(C4=«сентябрь»;B4>30); И(C4=«ноябрь»;B4>30));30; ЕСЛИ(И(C4=«февраль»;B4>28;D4/4=ЦЕЛОЕ(D4/4));29; ЕСЛИ(И(C4=«февраль»;B4>28);28;B4)));"."; ЕСЛИ(C4=«январь»;«01»; ЕСЛИ(C4=«февраль»;«02»; ЕСЛИ(C4=«март»;«03»; ЕСЛИ(C4=«апрель»;«04»; ЕСЛИ(C4=«май»;«05»; ЕСЛИ(C4=«июнь»;«06»; ЕСЛИ(C4=«июль»;«07»; ЕСЛИ(C4=«август»;«08»; ЕСЛИ(C4=«сентябрь»;«09»; ЕСЛИ(C4=«октябрь»;«10»; ЕСЛИ(C4=«ноябрь»;«11»;«12»)))))))))));".";D4);2)=1;«понедельник»; ЕСЛИ(ДЕНЬНЕД(СЦЕПИТЬ(ЕСЛИ(ИЛИ(И(C4=«апрель»;B4>30); И(C4=«июнь»;B4>30); И(C4=«сентябрь»;B4>30); И(C4=«ноябрь»;B4>30));30; ЕСЛИ(И(C4=«февраль»;B4>28;D4/4=ЦЕЛОЕ(D4/4));29; ЕСЛИ(И(C4=«февраль»;B4>28);28;B4)));"."; ЕСЛИ(C4=«январь»;«01»; ЕСЛИ(C4=«февраль»;«02»; ЕСЛИ(C4=«март»;«03»; ЕСЛИ(C4=«апрель»;«04»; ЕСЛИ(C4=«май»;«05»; ЕСЛИ(C4=«июнь»;«06»; ЕСЛИ(C4=«июль»;«07»; ЕСЛИ(C4=«август»;«08»; ЕСЛИ(C4=«сентябрь»;«09»; ЕСЛИ(C4=«октябрь»;«10»; ЕСЛИ(C4=«ноябрь»;«11»;«12»)))))))))));".";D4);2)=2;«вторник»; ЕСЛИ(ДЕНЬНЕД(СЦЕПИТЬ(ЕСЛИ(ИЛИ(И(C4=«апрель»;B4>30); И(C4=«июнь»;B4>30); И(C4=«сентябрь»;B4>30); И(C4=«ноябрь»;B4>30));30; ЕСЛИ(И(C4=«февраль»;B4>28;D4/4=ЦЕЛОЕ(D4/4));29; ЕСЛИ(И(C4=«февраль»;B4>28);28;B4)));"."; ЕСЛИ(C4=«январь»;«01»; ЕСЛИ(C4=«февраль»;«02»; ЕСЛИ(C4=«март»;«03»; ЕСЛИ(C4=«апрель»;«04»; ЕСЛИ(C4=«май»;«05»; ЕСЛИ(C4=«июнь»;«06»; ЕСЛИ(C4=«июль»;«07»; ЕСЛИ(C4=«август»;«08»; ЕСЛИ(C4=«сентябрь»;«09»; ЕСЛИ(C4=«октябрь»;«10»; ЕСЛИ(C4=«ноябрь»;«11»;«12»)))))))))));".";D4);2)=3;«среда»; ЕСЛИ(ДЕНЬНЕД(СЦЕПИТЬ(ЕСЛИ(ИЛИ(И(C4=«апрель»;B4>30); И(C4=«июнь»;B4>30); И(C4=«сентябрь»;B4>30); И(C4=«ноябрь»;B4>30));30; ЕСЛИ(И(C4=«февраль»;B4>28;D4/4=ЦЕЛОЕ(D4/4));29; ЕСЛИ(И(C4=«февраль»;B4>28);28;B4)));"."; ЕСЛИ(C4=«январь»;«01»; ЕСЛИ(C4=«февраль»;«02»; ЕСЛИ(C4=«март»;«03»; ЕСЛИ(C4=«апрель»;«04»; ЕСЛИ(C4=«май»;«05»; ЕСЛИ(C4=«июнь»;«06»; ЕСЛИ(C4=«июль»;«07»; ЕСЛИ(C4=«август»;«08»; ЕСЛИ(C4=«сентябрь»;«09»; ЕСЛИ(C4=«октябрь»;«10»; ЕСЛИ(C4=«ноябрь»;«11»;«12»)))))))))));".";D4);2)=4;«четверг»; ЕСЛИ(ДЕНЬНЕД(СЦЕПИТЬ(ЕСЛИ(ИЛИ(И(C4=«апрель»;B4>30); И(C4=«июнь»;B4>30); И(C4=«сентябрь»;B4>30); И(C4=«ноябрь»;B4>30));30; ЕСЛИ(И(C4=«февраль»;B4>28;D4/4=ЦЕЛОЕ(D4/4));29; ЕСЛИ(И(C4=«февраль»;B4>28);28;B4)));"."; ЕСЛИ(C4=«январь»;«01»; ЕСЛИ(C4=«февраль»;«02»; ЕСЛИ(C4=«март»;«03»; ЕСЛИ(C4=«апрель»;«04»; ЕСЛИ(C4=«май»;«05»; ЕСЛИ(C4=«июнь»;«06»; ЕСЛИ(C4=«июль»;«07»; ЕСЛИ(C4=«август»;«08»; ЕСЛИ(C4=«сентябрь»;«09»; ЕСЛИ(C4=«октябрь»;«10»; ЕСЛИ(C4=«ноябрь»;«11»;«12»)))))))))));".";D4);2)=5;«пятница»; ЕСЛИ(ДЕНЬНЕД(СЦЕПИТЬ(ЕСЛИ(ИЛИ(И(C4=«апрель»;B4>30); И(C4=«июнь»;B4>30); И(C4=«сентябрь»;B4>30); И(C4=«ноябрь»;B4>30));30; ЕСЛИ(И(C4=«февраль»;B4>28;D4/4=ЦЕЛОЕ(D4/4));29; ЕСЛИ(И(C4=«февраль»;B4>28);28;B4)));"."; ЕСЛИ(C4=«январь»;«01»; ЕСЛИ(C4=«февраль»;«02»; ЕСЛИ(C4=«март»;«03»; ЕСЛИ(C4=«апрель»;«04»; ЕСЛИ(C4=«май»;«05»; ЕСЛИ(C4=«июнь»;«06»; ЕСЛИ(C4=«июль»;«07»; ЕСЛИ(C4=«август»;«08»; ЕСЛИ(C4=«сентябрь»;«09»; ЕСЛИ(C4=«октябрь»;«10»; ЕСЛИ(C4=«ноябрь»;«11»;«12»)))))))))));".";D4);2)=6;«суббота»;«воскресенье»)))))); ЕСЛИ(ДАТА(D4; ЕСЛИ(C4=«январь»;1; ЕСЛИ(C4=«февраль»;2; ЕСЛИ(C4=«март»;3; ЕСЛИ(C4=«апрель»;4; ЕСЛИ(C4=«май»;5; ЕСЛИ(C4=«июнь»;6; ЕСЛИ(C4=«июль»;7; ЕСЛИ(C4=«август»;8; ЕСЛИ(C4=«сентябрь»;9; ЕСЛИ(C4=«октябрь»;10; ЕСЛИ(C4=«ноябрь»;11;12))))))))))); ЕСЛИ(ИЛИ(И(C4=«апрель»;B4>30); И(C4=«июнь»;B4>30); И(C4=«сентябрь»;B4>30); И(C4=«ноябрь»;B4>30));30; ЕСЛИ(И(C4=«февраль»;B4>28;D4/4=ЦЕЛОЕ(D4/4));29; ЕСЛИ(И(C4=«февраль»;B4>28);28;B4))))>СЕГОДНЯ();" (только будет)"; ЕСЛИ(ДАТА(D4; ЕСЛИ(C4=«январь»;1; ЕСЛИ(C4=«февраль»;2; ЕСЛИ(C4=«март»;3; ЕСЛИ(C4=«апрель»;4; ЕСЛИ(C4=«май»;5; ЕСЛИ(C4=«июнь»;6; ЕСЛИ(C4=«июль»;7; ЕСЛИ(C4=«август»;8; ЕСЛИ(C4=«сентябрь»;9; ЕСЛИ(C4=«октябрь»;10; ЕСЛИ(C4=«ноябрь»;11;12))))))))))); ЕСЛИ(ИЛИ(И(C4=«апрель»;B4>30); И(C4=«июнь»;B4>30); И(C4=«сентябрь»;B4>30); И(C4=«ноябрь»;B4>30));30; ЕСЛИ(И(C4=«февраль»;B4>28;D4/4=ЦЕЛОЕ(D4/4));29; ЕСЛИ(И(C4=«февраль»;B4>28);28;B4))))<СЕГОДНЯ();" (уже было)";" (сегодня)"))))tim2018 Автор
02.05.2018 18:25Это чудо сокращается в разы с помощью волшебной функции ВПР и вспомогательных таблиц
erudebu
02.05.2018 22:36впр не нужен. если нужны вспомогательные таблицы, то лучше использовать ИНДЕКС (намного гибче, и при использовании диапазонов читабельнее, ну и предсказуемее, тк при изменении таблицы данных не нужно лезть в формулу для изменения номера столбца). а конкретно здесь интереснее смотрелась формула ПОИСКПОЗ (т.к. нам нужен номер месяца). и доптаблицы не нужны. ПОИСКПОЗ(C4;{«январь»;«февраль»;«март»;«апрель»;«май»;«июнь»;«июль»;«август»;«сентябрь»;«октябрь»;«ноябрь»;«декабрь»};0)
Boriska007
02.05.2018 23:53Да, вы несомненно правы, я прекрасно знаю и много использую и ВПР, и ИНДЕКС в связке с ПОИСКПОЗ, и СУММЕСЛИМН с кучей условий, а также дополнительные вычисления на скрываемых листах… В общем много с экселем работаю.
Тут как раз была идея засунуть кучу различных вычислений именно в одну ячейку, просто ради забавы и собственно длинны всей этой формулы. Ну и чтобы это коррекетно работало в итоге.
matacob
02.05.2018 18:16+1Таких формул можно вагон и маленькую тележку нагуглить. Или не таких. Кто ж его знает, если просто вывалены куски кода без пояснений, как оно всё в Экселе работает.
bro-dev0
02.05.2018 18:17+1Для всех кто пишет, что проще было на чем то другом сделать, скажу, что сам с таким сталкивался, когда делаешь для людей которые знают только эксель, а не аксесс или другие чудо базы.
Dek4nice
02.05.2018 18:17Открывая статью, ожидал увидеть новые методы для уменьшения размеров этих формул, что-то вроде лайфхаков при работе с ними.
tim2018 Автор
02.05.2018 18:20Есть такое выражение Математика это язык. Вот Эксель это тоже язык. Если бы офисные работники побольше бы работали в нём, а не в специально созданном ПО, их количество было бы меньше.
striver
02.05.2018 18:59Эксель это круто, особенно с постоянным пересчетом данных. Но когда файл за 1 месяц растет на 2,5мб, то через 3 месяца, через сетевой диск — там просто не реально работать. Приходилось тупо руками убивать инфу за 1 месяц, а архив создавать в другом файле без формул.
tim2018 Автор
02.05.2018 19:57Трудно что-то сказать. Но мешать формулы и данные — не самая хорошая идея. Форматирование (цвет шрифта, заливка, границы) кушает объем. Очень часто прилетает мусор при использовании Ctrl+C & Ctrl+V из сторонних источников.
Но Мелкомягкие сделали очень хорошие умные таблицы и Power query
alexoron
02.05.2018 19:59Когда-то в 2006 году увидел «внутренние» sql-запросы в оракле на несколько десятков строк, такие длинные запросы с тех пор больше нигде не видел)))
Serge78rus
Idot
И самое стрёмное, что «ЕСЛИ(B1=0; СУММ(...» не работает на компьютерах иностранцев.
Тот кто придумал в Excel пихать формулы на русском — является диверсантом.
immaculate
Теперь в LibreOffice то же самое. :(
tyomitch
Слава еврейскому б-гу и Аллаху, что в ивритской и арабской локализациях формулы оставили на английском. А то там была бы не только мешанина кириллицы с латиницей, но ещё и RTL/LTR.
idiv
Не сработает при «копировать-вставить». Нужно или искать сайт с переводом (или вручную заменить формулы), или взять экселевский файл с этой формулой.
Это сделано для доступности для широких масс (очень широких масс). Ну и все таки использование его редко выходит за пределы одного языка, потому и формулы вполне можно использовать на одном языке.
tyomitch
Когда возникает ситуация «нужно автоматизировать рабочее место продавщицы/телефонистки, Офис уже стоит, сторонний софт устанавливать нельзя, админских прав нет, USB-разъёмы выдраны админом при сборке компа», то приходится городить в Экселе именно такие формулы.
Serge78rus
tyomitch
В данном случае это означает, что непосредственному начальнику нужно, чтобы всё работало и не важно как, а глобальные IT-политики принимаются на много уровней выше и не касаются конкретных ситуаций на местах.
Serge78rus
Конечно, бывают разные жизненные ситуации, но вообще то нужно бежать из мест, где «нужно, чтобы всё работало и не важно как».
tyomitch
Скажем так, иногда компенсация оправдывает неудобства :-)
Serge78rus
Главное, чтоб у нас всегда оставалась возможность выбора.
alexoron
Обычно такие задания дают эникеям.
tim2018 Автор
В статье формулы совсем не кошмарные.
Просто экселисты делятся на макрописцев и формулистов. Построение таких формул это и развлечение, и соревнование. Учитывается остроумность алгоритма, не стандартное применение функций, их количество, аргументы и скорость обработки
sincosxy
Позвольте с вами не согласиться.
Макросы и формулы решают разные задачи, которые пересекаются очень редко.
blackarrow
Вместо построения громоздкой и трудночитаемой формулы «макропрописец» напишет свою собственную функцию для использования в формуле. Здесь они вполне пересекаются. Если понятно названы переменные, сделаны по-человечески именованые диапазоны, тогда, с учетом высокой читабельности VBA, люди со знанием английского с минимальными усилиями вполне смогут понять, что делает относительно несложный код, особенно если он с нормальными комментариями.
tim2018 Автор
программистов мало, а эксель почти у всех
drive.google.com/file/d/1-yuzAfD2kRNhSVT7lEg_Z-ZWvub0uEFy/view?usp=sharing
по указанной ссылке болванка склада, которой будет достаточно очень многим небольшим фирмочкам. С помощью экселя можно решать очень большое количество задач, и для этого не надо быть программистом. Форумов, где помогут (бесплатно) — очень много, там же куча готовых решений. Было бы желание