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

Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 54 След.
Подсчет итога по критериям других ячеек
 
#
14.06.2025 23:47:17
Как вариант:
=SUMPRODUCT(COUNTIF($E4:$AI4;{8\12\24})*$B4:$D4)
 
Перейти    
Поиск нескольких значений с выводом определенной ячейки, Поиск нескольких значений с выводом определенной ячейки
 
#
05.06.2025 19:26:14
Формулами, правда довольно таки громоздкими (массивными естественно).
Фрукты:
=IFERROR(INDEX($B$3:$B$8;MATCH(0;INDEX(--(COUNTIF($L$2:L2;$B$3:$B$8)=MMULT(--(ISNUMBER($C$3:$G$8));{1;1;1;1;1}));0);0));"")

Недели:
=IFERROR(INDEX($C$2:$G$2;SMALL(IF((IF(MATCH($B$3:$B$8;$B$3:$B$8;0)=(ROW($B$3:$B$8)-ROW($B$3)+1);$B$3:$B$8)=$L3)*($C$3:$G$8<>"");(COLUMN($C$3:$G$8)-COLUMN(C$3)+1));COUNTIF(L$3:L3;L3)));"")
 
Перейти    
формула среднего значения только заполненных ячеек, не учитывая нули в заполненных ячейках
 
#
24.05.2025 10:52:44
=SUM(D3:O3)/COUNTIF(D3:O3;">0")
=AVERAGEIF(D3:O3;">0";D3:O3)
Массивно:
=AVERAGE(IF(D3:O3;D3:O3))
Изменено: memo - 24.05.2025 10:55:45
 
Перейти    
Уникальное значение из столбца перенести вместе со значениями в соответствующий столбец
 
#
22.05.2025 20:19:17
Можно и без СМЕЩ. Простые варианты в файле.
 
Перейти    
Вывести минимальные и максимальные значения из промежутков последовательных чисел
 
#
17.05.2025 16:55:05
BodkhiSatva,
Все отлично. Только дома у меня по-прежнему Excel 2013-2016, где нет функции TEXTJOIN().
Сейчас пытаюсь разобраться, почему не работает последний вариант от Павел \Ʌ/,

UPDATE: Кажется разобрался. Дело в том, что ИНДЕКС в Excel 2013-2016 (не знаю, как в 2019) по-умолчанию не поддерживает массив строк (в качестве второго аргумента). Но, тем не менее есть некий трюк, который позволяет это сделать.

Вот такой вариант работает:
Код
=IFERROR(INDEX(B$2:B$22;AGGREGATE(15;6;ROW(B$2:B$22)/(B$2:B$22-INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(Q$2);)))<>1);ROW(Q1))-ROW(Q$2)+1)&IF(IFERROR(SUM(AGGREGATE(15;6;ROW(B$2:B$22)/(B$2:B$22-INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(Q$2);)))<>1);ROW(Q1:Q2))*{-1;1})-1;1);"_"&INDEX(B$2:B$22;AGGREGATE(15;6;ROW(B$2:B$22)/(IFERROR(INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(Q$2)+2;)));B$2:B$22)-B$2:B$22<>1);ROW(Q1))-ROW(Q$2)+1);"");"")


или так:
Код
=IFERROR(INDEX(B$2:B$22;SMALL(IF((B$2:B$22-INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(R$2);)))<>1);ROW(B$2:B$22));ROW(R1))-ROW(R$2)+1)&IF(IFERROR(SUM(SMALL(IF((B$2:B$22-INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(R$2);)))<>1);ROW(B$2:B$22));ROW(R1:R2))*{-1;1})-1;1);"_"&INDEX(B$2:B$22;SMALL(IF((IFERROR(INDEX(B$2:B$22;N(INDEX(ROW(B$2:B$22)-ROW(R$2)+2;)));B$2:B$22)-B$2:B$22<>1);ROW(B$2:B$22));ROW(R1))-ROW(R$2)+1);"");"")


(добавлен блок N(INDEX(ROW(B$2:B$22)-ROW(R$2))

Еще раз спасибо вам за этот вариант!
Изменено: memo - 17.05.2025 22:38:18
 
Перейти    
Вывести минимальные и максимальные значения из промежутков последовательных чисел
 
#
17.05.2025 14:15:00
Павел \Ʌ/, Странно, но почему-то на 2013-ом офисе не взлетело.
Скрин
 
Перейти    
Вывести минимальные и максимальные значения из промежутков последовательных чисел
 
#
16.05.2025 18:46:35
Павел \Ʌ/, Metanor,
Спасибо за варианты!  

Павел, понравился ваш вариант с отрицательными числами для старых версий.

БМВ, Благодарствую!
Изменено: memo - 16.05.2025 18:51:44
 
Перейти    
Вывести минимальные и максимальные значения из промежутков последовательных чисел
 
#
16.05.2025 12:58:31
Всем доброго времени суток.
Собственно сабж. В столбце А имеется список из последовательных чисел: 120,121,122 далее следует разрыв и идет число 124, означающее нарушение последовательности и завершение промежутка. С него же и начинается новый 124,125,126 и т.д. пока последовательность снова не нарушится.
В каждом промежутке надо найти начало и конец и прописать через тире. Например для первого это будет 120-122.
В приложенном файле два моих варианта.
Вопрос: можно ли сделать вариант покороче?
 
Перейти    
Вычислить формулу из цифр и букв, Преобразовать цифры и буквы в формулу и вычислить результат
 
#
09.05.2025 22:29:13
Формула массива. Хотя, думаю, вполне можно сообразить покороче и без массива.
=PRODUCT(IFERROR(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"д";);"х";REPT(" ";15));".";MID(1/2;2;1));{1;15;30};15));0>1))
Перевод формулы на русский: ТЫЦ
Изменено: memo - 09.05.2025 22:31:23
 
Перейти    
Как для таблицы сделать сортировку из нескольких условий ?
 
#
09.05.2025 22:26:50
Не знаю, правильно или нет.
Update: теперь вроде правильно
=IFERROR(INDEX(P:P;ROUND(MOD(LARGE(IF((($U$2:$U$148>0)*($W$2:$W$148>0)*($Y$2:$Y$148>0)*($AA$2:$AA$148>0))*((MATCH($P$2:$P$148;$P$2:$P$148;)=ROW($P$2:$P$148)-ROW($P$1)));$S$2:$S$148*10000+ROW($P$2:$P$148)/10000);ROW(P1));1)*10000;));"")
Изменено: memo - 10.05.2025 11:57:45 (Невнимательность)
 
Перейти    
Формула подбирающая не каждое значение
 
#
03.05.2025 01:59:10
Как вариант (с выпадающим списком в ячейках I2 и I3):
Код
=SUMIF(INDEX(B2:B50;MATCH(I2&"*";B2:B50;)):INDEX(B2:B50;MATCH(I2&"*";B2:B50;)+AGGREGATE(15;6;ROW(B2:B50)-1/(B2:B50=I3);COUNTIF(B2:B50;I2&"*"))+1);I3;INDEX(C2:C50;MATCH(I2&"*";B2:B50;)):INDEX(C2:C50;MATCH(I2&"*";B2:B50;)+AGGREGATE(15;6;ROW(B2:B50)-1/(B2:B50=I3);COUNTIF(B2:B50;I2&"*"))+1))


....ну или чуть короче,но летуче:
Код
=SUMIF(OFFSET(B1;MATCH(I2&"*";B2:B50;);;AGGREGATE(15;6;ROW(B2:B50)-1/(B2:B50=I3);COUNTIF(B2:B50;I2&"*"))+1);I3;OFFSET(C1;MATCH(I2&"*";B2:B50;);;AGGREGATE(15;6;ROW(B2:B50)-1/(B2:B50=I3);COUNTIF(B2:B50;I2&"*"))+1))
Изменено: memo - 03.05.2025 03:24:46 (Доработка формул)
 
Перейти    
Кнопка выкл и вкл линии строку и столбцу VBA
 
#
08.12.2024 00:37:11
Надо отписываться в своих темах.
 
Перейти    
Вывод уникальных значений из диапазона по условию
 
#
07.12.2024 00:08:33
Ок, такой формульный вариант.
=IFERROR(INDEX(B$11:B$23;MATCH(1;INDEX((COUNTIFS(K$10:K10;B$11:B$23;$H$10:$H10;$A$11:$A$23)=0)*($A$11:$A$23=$H11);0);0));"")
Предположительное название темы: вывод уникальных значенией из диапазона по условию
Изменено: memo - 07.12.2024 03:22:22
 
Перейти    
протягивание двух ячеек с функцией "индекс", при протягивании вправо двух ячеек с функцией "индекс" значения из массива заполняются с шагом +1
 
#
06.12.2024 21:27:47
Вариант.
=INDEX($B34:$I1084;1+INT((COLUMN(A1)-1)/2)*30;1+MOD((COLUMN(A1)-1);2)*7)
 
Перейти    
Получить все строки для массива столбцов функцией ИНДЕКС()
 
#
05.12.2024 14:39:12
Не хочу выглядеть капитаном очевидностью, но думаю, можно подытожить, что при нулевом номере строки и наличии какого-то намека на массив для столбцов, вернуть все номера строк никак не получится.
 
Перейти    
Получить все строки для массива столбцов функцией ИНДЕКС()
 
#
05.12.2024 13:01:30
При такой конструкции (см. спойлер) массивно введенная функция последовательно вернет столбцы и строки.
INDEX(отчет!A2:K100;0;0)


Ну а конструкция типа:
INDEX(отчет!A2:K100;{1;2;3;4;5};{1\6\3\5\7\8\11\10})

вернет нужные столбцы и первые 5 строк. Если массив {1;2;3;4;5}
 заменить на ROW(1:5) то результат будет тот же самый.
Экспериментировал с Excel 2013.
 
Перейти    
Вывод из массива по одному критерию массива и далее столбцы в другом порядке., Вывод из массива по одному критерию массив, а далее поменять столбики в другом порядке.
 
#
05.12.2024 11:56:38
Цитата
agnes написал:
хочется чтобы формировалось динамически. (((
Можно просто увеличить диапазон (сделать с запасом насколько надо), ну а если данные не отсортированны, то здесь уже набросали варианты.
 
Перейти    
Вывод из массива по одному критерию массива и далее столбцы в другом порядке., Вывод из массива по одному критерию массив, а далее поменять столбики в другом порядке.
 
#
04.12.2024 21:52:56
Если данные в исходной таблице отсортированны в таком же порядке как в файле примере, то можно так:
=IFERROR(INDEX(отчет!$B$2:$J$11;MATCH($A$2;отчет!$A$2:$A$11;0)+ROW(A1)-1;MATCH(B$1;отчет!$B$1:$J$1;));"-")
 
Перейти    
Поиск внутри формулы из чисел
 
#
03.12.2024 13:18:16
Может так?
Код
=IFERROR(IF(AGGREGATE(14;6;SEARCH(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(B3);"+";"</s><s>");"*";"</s><s>");"=";)&"</s></t>";"//s");C3);1);D3);"Пусто")
 
Перейти    
Подсчет уникальных значений в видимом диапозоне при установке фильтра
 
#
02.12.2024 12:36:33
Хорошо, а так?
=COUNT(IF(FREQUENCY(IF(SUBTOTAL(103;OFFSET($B$2;ROW($B$2:$B$16)-2;));MATCH("~"&$B$2:$B$16;$B$2:$B$16&"";0));ROW($B$2:$B$16)-ROW($B$2)+1)>0;ROW($B$2:$B$16)^0))

Ну и небольшая модификация первой формулы:
=COUNT(1/(MATCH($B$2:$B$16&"_1";$B$2:$B$16&"_"&SUBTOTAL(103;OFFSET($B$2;ROW($B$2:$B$16)-2; )); )=(ROW($B$2:$B$16)-1)))
Изменено: memo - 02.12.2024 12:51:22
 
Перейти    
Подсчет уникальных значений в видимом диапозоне при установке фильтра
 
#
02.12.2024 11:13:07
Может так?
Код
=SUM(--IFERROR(SUBTOTAL(3;OFFSET($B$2;ROW($2:$16)-2;))*(ROW($2:$16)=MATCH($B$2:$B$16;$B$1:$B$16;0));))

Формула массива вводится через CTRL+SHIFT+ENTER
Изменено: memo - 02.12.2024 11:14:20
 
Перейти    
Способы разделения текста и числового значения, находящихся в одной ячейке
 
#
30.11.2024 22:41:55
molinari, У меня на 2013-ом тоже не сработала. Попробуйте так:
=--TRIM(MID(SUBSTITUTE(SUBSTITUTE(";"&$A1&";";";";REPT(" ";LEN($A1)+4);COLUMN(A1));";";REPT(" ";LEN($A1)+4);COLUMN(A1));LEN($A1)+4;LEN($A1)+4))
Не забываем о переводе.
 
Перейти    
Аналоги формул ФИЛЬТР/СОРТ/ВЫБОР в старых версиях Excel
 
#
23.11.2024 23:52:01
Вариант.
 
Перейти    
Работа с объединенными ячейками (сумма по нескольким критериям)
 
#
19.11.2024 12:54:22
Такой вариант с нудной формулой:
=SUMPRODUCT($C$3:$G$13*(LOOKUP(COLUMN($C$2:$G$2);(COLUMN($C$2:$G$2)/($C$2:$G$2>0));$C$2:$G$2=$R$5))*($B$3:$B$13=$R$4)*(LOOKUP(ROW($A$3:$A$13);ROW($A$3:$A$13)/($A$3:$A$13>0);$A$3:$A$13=$R$3)))
Изменено: memo - 19.11.2024 12:56:09
 
Перейти    
связать данные по городам и округам и вычислить сумму
 
#
19.11.2024 10:00:38
nilske,
Вторую можно еще так записать, но тогда ее придется вводить массивно.
=SUM(SUMIFS(C$17:C$136;B$17:B$136;IF(SUBSTITUTE($F$16:$F$112;"Округ "; )=TRIM(B4);E$16:E$112)))
Изменено: memo - 19.11.2024 10:01:12
 
Перейти    
связать данные по городам и округам и вычислить сумму
 
#
19.11.2024 09:44:55
Цитата
lulikos написал:
все формулы работают. Спасибо
Если вы про файл из первого поста, не измененный естественно, то не должны ни разу.
Вот так пожалуй сработает.
=SUMPRODUCT(ISNUMBER(FIND(B4;F$16:F$112))*SUMIF(B$17:B$136;E$16:E$112;C$17:C$136))
или так.
=SUMPRODUCT(SUMIFS(C$17:C$136;B$17:B$136;E$16:E$112)*(SUBSTITUTE($F$16:$F$112;"Округ " ;)=TRIM(B4)))
 
Перейти    
связать данные по городам и округам и вычислить сумму
 
#
18.11.2024 22:56:40
Цитата
lulikos написал:
Подскажите, пожалуйста, что не так делаю?
Вы пытаетесь заставить работать с массивом условий функцию, у которой нет такого функционала.
 
Перейти    
Функция ВПР не подтягивает данные, Функция ВПР не подтягивает данные
 
#
10.11.2024 23:12:17
=VLOOKUP($B1;погр!$A:$K;CHOOSE(COLUMN(A:A);1;5;9;11);0)
 
Перейти    
Подсчет по нескольким значениям в строках и столбцах с помощью формул
 
#
08.11.2024 20:00:29
Globarius,
Так пойдет?
=SUMIFS(INDEX($J$4:$U$8;;MATCH($B4;$J$3:$U$3;));$H$4:$H$8;$A4;$I$4:$I$8;$C4)
 
Перейти    
Поиск количества уникальных значений по условию в изменяющемся диапазоне
 
#
08.11.2024 19:46:35
Цитата
Кирилл Иванов написал:
Формула excel не срабатывает в яндекс таблицах (онлайн документы).
Ничем не могу помочь, увы. Никогда не работал в яндекс таблицах. Могу лишь предположить, что алгоритм работы функций там несколько другой.
 
Перейти    
Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 54 След.
Наверх