Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Светлый
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 17 След.
Избушка формулистов-3, не для вопросов, но для формульных задач
 
#
23.03.2022 17:08:26
Можно ещё так:
Скрытый текст
 
Перейти    
Решение математических буквенно-числовых ребусов в Excel, криптоарифметическая задача
 
#
16.01.2022 17:18:51
Упрощённая формула массива (не универсальная):
Код
=МАКС(СТРОКА(5000:9999)*(ПСТР(СТРОКА(5000:9999)*2;5;1)=ПСТР(СТРОКА(5000:9999);3;1))*(ПСТР(СТРОКА(5000:9999)*2;3;1)=ПСТР(СТРОКА(5000:9999);3;1))*(ПСТР(СТРОКА(5000:9999)*2;2;1)=ПСТР(СТРОКА(5000:9999);4;1))*(ПСТР(СТРОКА(5000:9999)*2;1;1)=ПСТР(СТРОКА(5000:9999);2;1)))
 
Перейти    
График с неравномерным шагом по оси абсцисс
 
#
04.12.2021 17:52:54
Может, так?
 
Перейти    
Внести в заявку товары, у которых вписано количество в списке товаров
 
#
04.12.2021 13:28:26
Массивными формулами для неновых офисов.
Код
=ЕСЛИОШИБКА(ИНДЕКС(Фурнитура!H:H;НАИМЕНЬШИЙ(ЕСЛИ(Фурнитура!$D$9:$D$29>0;СТРОКА(Фурнитура!$9:$29));СТРОКА(B1)));"")
 
Перейти    
Распределение числовых значений по интервалам
 
#
03.12.2021 09:25:34
Как всегда опаздываю. Ещё вариант:
Код
=ВЫБОР(($C5>=G4)+2*($C5<=G4+$B5);МАКС(;F4-$C5+$B5);;МИН(5;$C5-G4))

*Немного короче, но не работает, если горизонт кратен пяти. Массивная:

Код
=СУММ(ОСТАТ({-1;1}*$C5;5)*{-1:1}*(F4:G4<$C5-$B5:$B6))
=ОКРУГЛ(СУММ(ОСТАТ({-1;1}*($C5-1%%);5)*{-1:1}*(F4:G4<$C5-$B5:$B6));3) 'Работает
Изменено: Светлый - 03.12.2021 14:33:42
 
Перейти    
Вытащить только цифры после определенного символа
 
#
02.12.2021 23:03:53
Можно так:
Код
=АГРЕГАТ(14;6;--ПСТР(A2;ПОИСК("pk";A2)+2;{1;2;3;4});1)
=-ПРОСМОТР(;-ПСТР(A2;ПОИСК("pk";A2)+2;{1;2;3;4}))

до 4 цифр и с любым хвостом после них.

Изменено: Светлый - 02.12.2021 23:06:39
 
Перейти    
Как вычислить произвольный элемент треугольника Паскаля, одной формулой
 
#
30.11.2021 19:58:44
Формула массива:
Код
=ПРОИЗВЕД(СТРОКА(ДВССЫЛ(124-7+1&":"&124-1)))/ФАКТР(7-1)
 
Перейти    
Как сделать, чтобы даты отпуска сотрудника не пересекались с датами отпуска другого сотрудника
 
#
25.11.2021 09:23:05
Можно условное форматирование задать формулой массива (исправил):
Код
=СУММ((ЕСЛИОШИБКА(--ЛЕВБ(C$10:C$21;10);)<=--ПРАВБ(C10;10))*(--ЛЕВБ(C10;10)<=ЕСЛИОШИБКА(--ПРАВБ(C$10:C$21;10);))*(C10>0))>1
Изменено: Светлый - 25.11.2021 14:11:51
 
Перейти    
Есть ли в архивах форума кредитный калькулятор с произвольными платежами
 
#
22.11.2021 22:11:32
Аннуитетный кредит. График платежей и остаток.
В жёлтой ячейке формула последнего платежа. Не стал делать универсальную формулу.
Дату начальную ставите свою, остальные формула вычислит. Сумму кредита и процент тоже вводите свои.
Стандартная сумма оплаты вычисляется по формуле.
В зелёных ячейках вручную вносим оплату, отличающуюся от стандартной.
Проверьте свои платежи и сравните с расчётными.
 
Перейти    
Сумма строк с ограничением диапазона по граничной сумме
 
#
22.11.2021 08:36:13
Можно ещё так:
Код
=ОКРВНИЗ(СУММЕСЛИ(A$2:A2;"<>итого:";B$2:B2)-СУММ(C$1:C1);350000)
но "0" придётся скрыть форматом.
 
Перейти    
Рассчитать формулой дату окончания отпуска так, чтобы не учитывались дни из списка
 
#
17.11.2021 20:59:36
Вариант формулой до 60 дней отпуска:
Код
=ПОИСКПОЗ(F2;МУМНОЖ(Ч(СТРОКА(3:62)>СТОЛБЕЦ(A:BH));1-СЧЁТЕСЛИ(A:A;СТРОКА(ИНДЕКС(A:A;E2):ИНДЕКС(A:A;E2+59))));)+E2
=ПОИСКПОЗ(F2;МУМНОЖ(Ч(СТРОКА(3:62)>СТОЛБЕЦ(A:BH));1-СЧЁТЕСЛИ(A:A;СТРОКА(СМЕЩ(A1;E2;;60))));)+E2
 
Перейти    
Расположить строки с нужными числами по убыванию их количества
 
#
17.11.2021 10:55:16
В столбец A заносите свои данные. В столбец B искомые числа. И в столбце C результат сортировки.
Формула на 200000 строк.
Код
=ЕСЛИОШИБКА(ИНДЕКС(A:A;ПРАВБ(НАИБОЛЬШИЙ(МУМНОЖ(--ЕЧИСЛО(ПОИСК(ТРАНСП(" "&Ч(+B$2:B$16)&" ");" "&A$2:A$200000&" "));1^B2:B16)*10^6+СТРОКА($2:$200000);СТРОКА(A1));6))&"";"")
Изменено: Светлый - 17.11.2021 21:07:46
 
Перейти    
Поиск значений массива в другом массиве
 
#
17.11.2021 09:58:50
Формула массива:
Код
=ИНДЕКС(B$2:B$36;ПОИСКПОЗ("#"&A2&".*";"#"&B$2:B$36;))

*И обратный поиск:

=ПРОСМОТР(2;1/(ПОИСКПОЗ("#"&A$2:A$6&".*";"#"&B$1:B$36;)=СТРОКА());A$2:A$6)

**Так все ищет:

Код
=ИНДЕКС(A:A;АГРЕГАТ(14;6;(ПОИСК(ТРАНСП("#"&A$2:A$6&".");"#"&B$1:B$30)*СТРОКА($1:$30)=СТРОКА())*ТРАНСП(СТРОКА($2:$6));1))

***А лучше так:

Код
=ИНДЕКС(A:A;ПОИСКПОЗ(ЛЕВБ(B2;ПОИСК("#";ПОДСТАВИТЬ(B2;".";"#";3))-1);A:A;))
Изменено: Светлый - 17.11.2021 10:34:47
 
Перейти    
Расположить строки с нужными числами по убыванию их количества
 
#
16.11.2021 09:57:09
Отдельно задаём массив нужных чисел.
Формула массива:
Код
=ЕСЛИОШИБКА(ИНДЕКС(A:A;ПРАВБ(НАИБОЛЬШИЙ(МУМНОЖ(--ЕЧИСЛО(ПОИСК(ТРАНСП(" "&Ч(+B$2:B$16)&" ");" "&A$2:A$6&" "));1^B2:B16)*100+СТРОКА($2:$6);СТРОКА(A1));2))&"";"")
 
Перейти    
Три магазина, в которых клиент тратит больше всего
 
#
14.11.2021 18:37:13
Ещё вариант:
Код
=ЕСЛИ($F2>0;ИНДЕКС($C:$C;ПРАВБ(НАИБОЛЬШИЙ(($A$2:$A$20=$F2)*($D$2:$D$20>10000)*($D$2:$D$20*100+СТРОКА($2:$20));СТОЛБЕЦ(A2));2));)

*А если меньше трёх:

Код
=ЕСЛИОШИБКА(ИНДЕКС($C:$C;ПРАВБ(НАИБОЛЬШИЙ(ЕСЛИ(($A$2:$A$20=$F2)*($D$2:$D$20>10000);($D$2:$D$20*100+СТРОКА($2:$20)));СТОЛБЕЦ(A2));2));)
Изменено: Светлый - 14.11.2021 18:45:51
 
Перейти    
Разделить сотрудников по категориям возрастным
 
#
13.11.2021 13:13:12
Трудоспособный возраст:
Код
=Ч(T7<(D7="мужской")*5+55)
Старше:
Код
=Ч(T7>(D7="мужской")*5+54)

С категорией:

Код
=(T7<(D7="мужской")*5+55)*H7
 
Перейти    
Как склеить несколько несмежных диапазонов данных в один одномерный массив (список)., Без VBA. Без PQ. Без сводных таблиц. Без удаления дубликатов. Одной формулой. На лету.
 
#
13.11.2021 12:49:57
А чем не устроит?
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(A$2:C$22;СТРОКА(E1));"")

На приведённом примере работает.

Или виртуальный массив:

Код
=НАИМЕНЬШИЙ(A2:C20;СТРОКА(СМЕЩ(A1;;;СЧЁТ(A2:C20))))
 
Перейти    
Как найти три самые ранние даты из столбца с датами, не меняя формат ячеек в столбце(текстовый)
 
#
31.10.2021 18:34:52
Цитата
БМВ написал:
потом превращается в текст
Поставлена галочка в настройках - показывать формулы, а не значения.
 
Перейти    
Списание сырья по калькуляционной карточке, списание по производству
 
#
30.10.2021 23:12:42
Можете попробовать эту формулу массива (вводится одновременным нажатием Ctrl+Shift+Enter):
Код
=СУММПРОИЗВ(ИНДЕКС(Кальк!E$4:H$8;ПОИСКПОЗ(B11;Кальк!B$4:B$8;);Ч(ИНДЕКС(ПОИСКПОЗ(B$4:B$7;Кальк!E$2:H$2;);)))*E$4:E$7)
У Вас в разном порядке идут заголовки, поэтому перемножение данных даст неправильный результат, даже если размерность массива в формуле привести в порядок.
K-57K-54K-55K-56
*Допустил ошибку. Не всё проверил. Исправил, файл перезалил.
Изменено: Светлый - 31.10.2021 13:51:11
 
Перейти    
Замена букв слова в одной ячейке на другие символы, Замена букв слова в одной ячейке на другие символы
 
#
22.10.2021 19:02:43
Вариант:
Код
=СУММПРОИЗВ(МУМНОЖ(--ЕЧИСЛО(ПОИСК(ЛЕВБ(ПРАВБ(0&C1;СТРОКА($1:$15)));{"abc";"def";"ghi";"jkl";"mno";"pqr";"stu";"vwx";"zy"}));СТРОКА($1:$9))*10^(СТРОКА($1:$15)-1))
=СУММПРОИЗВ(ЕСЛИОШИБКА(ПОИСКПОЗ("*"&ЛЕВБ(ПРАВБ(0&C1;СТРОКА($1:$15)))&"*";{"abc";"def";"ghi";"jkl";"mno";"pqr";"stu";"vwx";"zy"};);)*10^(СТРОКА($1:$15)-1))
Буквы в кавычках заменятся на порядковый номер кавычек. Например, буквы "def" заменится на 2, а "mno" на 5
Изменено: Светлый - 22.10.2021 19:18:09
 
Перейти    
Замена букв слова в одной ячейке на другие символы, Замена букв слова в одной ячейке на другие символы
 
#
22.10.2021 11:56:55
А какая максимальная длина слова?
Какое максимальное число, которое надо подставлять вместо символа? Есть ли двухзначные числа? Или только цифры от 0 до 9?
 
Перейти    
UDF. Увеличивать значение на единицу при автозаполнение каждой последующей ячейки
 
#
21.10.2021 09:26:05
И Вам, здравствуйте.
В качестве аргумента можно взять СТРОКА(A1). При копировании формулы A1 будет нарастать 2,3,...
 
Перейти    
Формула распределения стоимости по цехам
 
#
21.10.2021 09:20:11
Подсмотрел у mechanix 85. Не сообразил про столбец A.
Формулу в G2 и протянуть вниз:
Код
=ЕСЛИ(И(D2<>D1;D2<>7;D2<>" ";E2>0);СУММЕСЛИМН(E:E;A:A;A2;D:D;D2)*СУММЕСЛИ(A:A;A2;F:F)/СУММЕСЛИМН(E:E;A:A;A2;D:D;"<> ";D:D;"<>7");"")
=ЕСЛИ(ИЛИ(D2=D1;D2=7;D2=" ";E2=0);"";СУММЕСЛИМН(E:E;A:A;A2;D:D;D2)*СУММЕСЛИ(A:A;A2;F:F)/СУММЕСЛИМН(E:E;A:A;A2;D:D;"<> ";D:D;"<>7"))
 
Перейти    
Формула распределения стоимости по цехам
 
#
20.10.2021 14:19:26
У меня получилась такая массивная формула (в G2 и протянуть вниз):
Код
=ЕСЛИОШИБКА(ЕСЛИ(И(D2<>D1;D2<>7;D2<>" ");СУММ((ИНДЕКС(D:D;МАКС(СТРОКА(F$1:F2)*(F$1:F2>0))):ИНДЕКС(D:D;МИН(ЕСЛИ(F3:F$200>0;СТРОКА(F2:F$199))))=D2)*ИНДЕКС(E:E;МАКС(СТРОКА(F$1:F2)*(F$1:F2>0))):ИНДЕКС(E:E;МИН(ЕСЛИ(F3:F$200>0;СТРОКА(F2:F$199)))))/(ИНДЕКС(E:E;МИН(ЕСЛИ(F3:F$200>0;СТРОКА(F2:F$199))))-СУММ((ИНДЕКС(D:D;МАКС(СТРОКА(F$1:F2)*(F$1:F2>0))):ИНДЕКС(D:D;МИН(ЕСЛИ(F3:F$200>0;СТРОКА(F2:F$199))))=7)*ИНДЕКС(E:E;МАКС(СТРОКА(F$1:F2)*(F$1:F2>0))):ИНДЕКС(E:E;МИН(ЕСЛИ(F3:F$200>0;СТРОКА(F2:F$199))))))*ИНДЕКС(F:F;МАКС(СТРОКА(F$1:F2)*(F$1:F2>0)));"");)
 
Перейти    
подсчёт количества разных событий в одном определённом месяце
 
#
20.10.2021 10:28:42
Цитата
Ігор Гончаренко написал:
или 1, которое случилось 3 раза
тогда так. Год берём из массива или СЕГОДНЯ(). Формула массива:

Код
=СУММ(ЕСЛИОШИБКА(1/СЧЁТЕСЛИМН(C$1:C$15;">="&--("1-"&D23&-ГОД(C$1));C$1:C$15;"<="&КОНМЕСЯЦА(--("1-"&D23&-ГОД(C$1));0);B$1:B$15;B$1:B$15);))

*Конечно, ещё бы учитывать, подряд ли (каждый день) идут одинаковые события или через день.

**Год рядом с месяцами.

Изменено: Светлый - 20.10.2021 10:43:36
 
Перейти    
подсчёт количества разных событий в одном определённом месяце
 
#
20.10.2021 08:26:18
Можно такой формулой:
Код
=СУММПРОИЗВ((ТЕКСТ(C$1:C$15;"ММММ")=D23)*(B$1:B$15>0))
 
Перейти    
Выбор из таблицы данных по трём параметрам
 
#
19.10.2021 09:48:47
Цитата
Евгений Владимирович написал:
подскажите что означает
1%%=0,0001 Это небольшое вычитание, чтобы целое значение 60 не попадало в диапазон функции ВПР
 
Перейти    
Выбор из таблицы данных по трём параметрам
 
#
19.10.2021 07:59:06
Цитата
Евгений Владимирович написал:
должна считать как для 51-60 (H148:K148)
Тогда небольшая корректировка:
Код
=ВПР(N16-1%%;ЕСЛИ({1;0};ЛЕВБ(G$143:G$156;ПОИСК("-";G$143:G$156)-1)-1;ИНДЕКС(H$143:K$156;;ПОИСКПОЗ(M$5&" "&I16&"ст";H$142:K$142)));2)
 
Перейти    
Выбор из таблицы данных по трём параметрам
 
#
18.10.2021 19:51:07
Цитата
Евгений Владимирович написал:
если я ставлю ровное кол-во тонн, например в ячейке N16 ставлю 60, то формула считает неверно
Сколько она должна давать? Как при 59 или при 61?
 
Перейти    
Выбор из таблицы данных по трём параметрам
 
#
18.10.2021 11:12:58
ЕСЛИ({1;0} - формирует массив из двух столбцов для функции ВПР. Первый столбец значения 0, 10, 20,..., 170, второй столбец - один из четырёх столбцов Вашей таблицы в зависимости от условий Лето-Зима и 1ст-2ст.
Цитата
Евгений Владимирович написал:
2. Что делает формула в этом примере?
Код
ЛЕВБ(G$143:G$156;ПОИСК("-";G$143:G$156)-1)-1
Делает Первый столбец значения: -1, 10, 20,..., 170,
Цитата
Евгений Владимирович написал:
3. После часть формулы тоже непонял
Код
ИНДЕКС(H$143:K$156;;ПОИСКПОЗ(M$5&" "&ПРАВБ(H16;3);H$142:K$142))
Ищет в заголовках Зима(Лето) 1ст(2ст) и берёт один из четырёх столбцов Вашей таблицы в зависимости от условий Лето-Зима и 1ст-2ст.
Цитата
Евгений Владимирович написал:
Стадий может быть до 20, не больше.
Вы будете добавлять столбцы в свою таблицу Зима 1ст, Зима 2ст, Зима 3ст,... Зима 20ст и т.д.? Или СКВ не имеет отношения к стадии?
Если не имеет, то берём номер стадии из столбца Оплата как стадия и тогда формула (массивная) будет такая:
Код
=ВПР(N16;ЕСЛИ({1;0};ЛЕВБ(G$143:G$156;ПОИСК("-";G$143:G$156)-1)-1;ИНДЕКС(H$143:K$156;;ПОИСКПОЗ(M$5&" "&I16&"ст";H$142:K$142)));2)
Изменено: Светлый - 18.10.2021 11:13:53
 
Перейти    
Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 17 След.
Наверх