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

Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 39 След.
Преобразование множества значений из ячейки в строки с повторением соседних столбцов, Преобразование таблицы
 
#
29.09.2022 08:30:09
БМВ, Ну, по идее, кроме точек с запятой там ничего не должно быть, хотя что там будет на практике один автор знает.
 
Перейти    
Преобразование множества значений из ячейки в строки с повторением соседних столбцов, Преобразование таблицы
 
#
29.09.2022 08:12:53
БМВ, Не перемудрено, скорее перестрахованно. К примеру если у букв поставить лишние точки с запятой ваша формула посчитает их и будет ненужный сдвиг.
 
Перейти    
Преобразование множества значений из ячейки в строки с повторением соседних столбцов, Преобразование таблицы
 
#
28.09.2022 19:19:20
Формульный вариант.
Первый столбец.
Код
=IF(ROWS(D$1:D1)>SUM(1+LEN(TRIM(SUBSTITUTE($B$1:$B$2&";";"; ";" ")))-LEN(SUBSTITUTE($B$1:$B$2&";";"; ";"")));"";INDEX(A$1:A$2;MATCH(ROWS(D$1:D1)-1;MMULT(--(ROW(A$1:A$2)>TRANSPOSE(ROW(A$1:A$2)));1+LEN(TRIM(SUBSTITUTE($B$1:$B$2&";";"; ";" ")))-LEN(SUBSTITUTE($B$1:$B$2&";";"; ";""))))))

Второй.
Код
=IFERROR(TRIM(MID(SUBSTITUTE(INDEX($B$1:$B$2;MATCH(ROWS(E$1:E1)-1;MMULT(--(ROW($B$1:$B$2)>TRANSPOSE(ROW($B$1:$B$2)));1+LEN(TRIM(SUBSTITUTE($B$1:$B$2&";";"; ";" ")))-LEN(SUBSTITUTE($B$1:$B$2&";";"; ";"")))));"; ";REPT(" ";99));IFERROR(MOD(SMALL(IF(LEN($B$1:$B$2&0)-LEN(SUBSTITUTE($B$1:$B$2;";";))>=COLUMN($A$1:$Z$1);ROW($B$1:$B$2)*100+COLUMN($A$1:$Z$1));ROWS(E$1:E1));100);"")*99-98;99));"")

Обе массивные.
 
Перейти    
Выпадающий список из умной таблицы по условию столбца, Понимаю что через ДВссыл, но никак прописать не получается
 
#
27.09.2022 07:13:23
Еще.
Код
=INDEX(АССОРТИМЕНТ[ЦВЕТ];MATCH(Лист1!$C40;АССОРТИМЕНТ[Артикул];0)):INDEX(АССОРТИМЕНТ[ЦВЕТ];MATCH(Лист1!C40;АССОРТИМЕНТ[Артикул];0)-1+COUNTIF(АССОРТИМЕНТ[Артикул];Лист1!$C40))

Имя в менеджере имен - ddlist
 
Перейти    
Копирование столбца с данными N раз, в каждом новом строки не совпадают друг с другом
 
#
26.09.2022 18:54:10
Вот такой вариант. Для начала на Листе3 с малым кол-вом данных.
Код
=IF(((ROWS(A$1:A1)-1)>=$B$2);"";IFERROR(INDEX($A$2:$A$9999;IF(COLUMNS($C$2:C2)>$B$4;"";MOD((ROW(C1)-1)+(COLUMN(C$1)-3)*$B$2;COUNTA($A$2:$A$9999)-1)+2));""))

В ячейке В2 задаем кол-во строк в столбце, в В4 кол-во столбцов.
 
Перейти    
Копирование столбца с данными N раз, в каждом новом строки не совпадают друг с другом
 
#
26.09.2022 17:50:24
_Boroda_, Я думаю, ТС нужно как на Листе3. Пока сделал вручную, чтобы ТС подтвердил что это то самое.
 
Перейти    
Копирование столбца с данными N раз, в каждом новом строки не совпадают друг с другом
 
#
26.09.2022 16:03:50
Т.е. число нужных столбцов все таки надо указывать?
Вообще, предполагаю, что макросом это проще сделать чем формулой, может кто возьмется. А я подумаю над формулой.
 
Перейти    
Копирование столбца с данными N раз, в каждом новом строки не совпадают друг с другом
 
#
26.09.2022 14:51:07
Дмитрий С, Хорошо, давайте все уточним.
Вы указываете нужное кол-во строк, как в ячейке В2 и по указанному числу при копировании в другой столбец должна происходить отсечка данных. Так?
Затем, вы пишите.
Цитата
Дмитрий С написал:
Нужно указать кол-во столбцов
, хотя в примере у вас такого не было. Вы просто упомянули, что данные должны скопироваться на 74 столбца.
У нас уже есть формула, которая это делает, но когда в столбце А кончаются данные, то она естественно начинает возвращать нули.
Но как это совместить с этим?
Цитата
Дмитрий С написал:
Можно ли сделать чтобы не копировать данные ( в столбце А) по 40-50 раз чтобы их хватило на нужное кол-во столбцов ?  
Т.е. формула должна рассчитать на сколько столбцов хватит данных из столбца А? Потому что из вашего примера на 74 столбца их точно не хватает.
 
Перейти    
Копирование столбца с данными N раз, в каждом новом строки не совпадают друг с другом
 
#
26.09.2022 06:46:50
Это какая-то шутка?
Вами заполненный лист1

Результат работы формулы из сообщения №5 на листе2
Изменено: memo - 26.09.2022 06:47:20
 
Перейти    
Копирование столбца с данными N раз, в каждом новом строки не совпадают друг с другом
 
#
25.09.2022 16:16:09
Вы последний файл из сообщения №5 (лист2) смотрели? Все в точности с вашим примером. Или я чего-то не понимаю?
 
Перейти    
Копирование столбца с данными N раз, в каждом новом строки не совпадают друг с другом
 
#
25.09.2022 15:23:44
Дмитрий С, Я же приложил файл. Вставляете в ячейку С2 и тянете вправо и вниз.
Код
=IF((ROWS(A$1:A1)-1)>=$B$2;"";INDEX($A$2:$A$9999;ROW()+(COLUMN()-3)*$B$2))
 
Перейти    
Копирование столбца с данными N раз, в каждом новом строки не совпадают друг с другом
 
#
25.09.2022 14:11:25
Так?
Код
=IF((ROWS(A$1:A1)-1)>=$B$2;"";INDEX($A$2:$A$9999;ROWS(A$1:A1)+(COLUMN()-3)*$B$2))
Изменено: memo - 25.09.2022 14:17:49
 
Перейти    
Соответствующие значения столбцам
 
#
23.09.2022 20:04:30
Цитата
Net99 написал:
но кажется слишком замудрённая формула.
Ну, тут, как говорится, Вашей фантазии да карты в руки. Сделайте лучше.
 
Перейти    
Соответствующие значения столбцам
 
#
23.09.2022 19:26:57
Как вариант:
Код
=IFERROR(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE($A2;", ";",");",";"</m><m>")&"</m></k>";"//m[contains(.,'"&TRIM(SUBSTITUTE(SUBSTITUTE(B$1;"«";);"»";))&"')][1]");"")
 
Перейти    
Подсчет количества столбцов с условием., Подсчет количества столбцов с условием
 
#
23.09.2022 17:30:27
Если правильно понял. Формула массива.
Код
=SUM(N(MMULT(TRANSPOSE(N(ISNUMBER($A$3:$AE$7)));ROW($A$3:$AE$7)^0)>1))
 
Перейти    
Поиск всех значений (текстовых) в одной ячейке из диапазона с возвратом суммы присвоенных параметров
 
#
22.09.2022 07:32:11
Попробуйте так:
=SUMPRODUCT(SUMIFS(Таблица1[Личные баллы];Таблица1[Фамилия участника];TRIM(MID(SUBSTITUTE(F5;", ";REPT(" ";99));ROW($1:$15)*99-98;99))))
Изменено: memo - 22.09.2022 07:34:23
 
Перейти    
Заполнить ячейку в зависимости от числа похожего текста в диапазоне строк
 
#
20.09.2022 22:54:59
БМВ, Да, споткнется, в виду отсутствия в меню CHOOSE() третьего фрукта, но и с грушами не расстанется.  8)
 
Перейти    
Заполнить ячейку в зависимости от числа похожего текста в диапазоне строк
 
#
20.09.2022 22:26:10
Вариант на основе формулы от БМВ,
=IFERROR(CHOOSE(("Дерево"=A2)*COUNTIF(INDEX(A:A;LOOKUP(2;1/($C$1:C2<>"");ROW($C$1:C2))):A2;"Дерево");"Яблоня";"Груша");"")
Изменено: memo - 20.09.2022 22:27:19
 
Перейти    
результат СУММПРОИЗВ в том же массиве, что и слагаемые.
 
#
20.09.2022 15:59:02
Мм..могу ошибаться, но может ТС хочет что-то вроде этого?
Код
=IFERROR(1/(1/(SUMIFS($G$20:$G$33;$B$20:$B$33;B19;$C$20:$C$33;C19;$D$20:$D$33;D19)*(LEFT(E20;5)="итого")));"")
 
Перейти    
Выборка из ячейки по критерию
 
#
20.09.2022 10:29:37
Можно так:
Код
=INDEX($A$3:$D$6;AGGREGATE(15;6;(ROW($A$3:$D$6)-ROW($A$3)+1)/(ISNUMBER(SEARCH($G$11;$B$3:$B$6)));ROW(A1));MATCH(H$10;$A$2:$D$2;))

или попроще
Код
=INDEX($A$3:$D$6;MATCH(TRUE;INDEX(ISNUMBER(SEARCH($G$11;$B$3:$B$6)););)+ROW(A1)-1;MATCH(H$10;$A$2:$D$2;))
 
Перейти    
Последовательная нумерация повторящихся значений столбца, со сбросом номера при появлении нового значения в столбце
 
#
18.09.2022 16:10:39
=COUNTIF($A$1:A1;A1)
 
Перейти    
Определить категрию по частичному совпадению текста
 
#
17.09.2022 22:33:58
Так?
Код
=LOOKUP(2;1/(ISNUMBER(SEARCH($G$1:$G$5;A1)));$H$1:$H$5)

Кстати, в этой комбинации ISNUMBER() можно убрать, потому что LOOKUP, то бишь ПРОСМОТР() игнорирует ошибки, выдаваемые функцией SEARCH (ПОИСК).
 
Перейти    
Перенос данных дублирующихся строк в отдельные столбцы".
 
#
17.09.2022 14:58:37
Да, сейчас все ОК)
 
Перейти    
Перенос данных дублирующихся строк в отдельные столбцы".
 
#
17.09.2022 14:40:54
whateverlover, Взял обновленный файл, добавил Леонидовичам телефонов, но показываются не все. Взгляните).
 
Перейти    
Перенос данных дублирующихся строк в отдельные столбцы".
 
#
17.09.2022 14:12:20
Цитата
whateverlover написал:
исправил
По-моему все же нет :)
Если добавить новый номер для Иванова, то он не появляется. Код Антона отрабатывает правильно.
 
Перейти    
ВПР по фильтрованным данным, Как сделать впр по фильтрованным данным
 
#
17.09.2022 02:22:33
Ігор Гончаренко, Это понятно, но спрашивалось про метод при котором ВПР это делать будет.
 
Перейти    
ВПР по фильтрованным данным, Как сделать впр по фильтрованным данным
 
#
17.09.2022 00:59:51
Без примера, навскидку и именно с ВПР как-то так:
=VLOOKUP($E$1;IF(SUBTOTAL(3;OFFSET($A$2;ROW($A$2:$A$20)-ROW($A$2);0;1))>0;$A$2:$B$20);2;0)
Где: E1 - условие из первого столбца диапазона $A$2:$B$20
Формула с функцией СМЕЩ: OFFSET($A$2;ROW($A$2:$A$20)-ROW($A$2);0;1) - выдает массив текущих значений и передает его функции ПРОМ.ИТОГИ (SUBTOTAL), которая отсеивает отфильтрованные значения. Далее все это передается функции ЕСЛИ (IF) которая оставляет значения больше нуля, т.е. единицы, которые "выжили" после работы фильтра. Затем ВПР ищет значение по условию и если оно скрыто фильтром, то выдаст ошибку (#NA).
Диапазоны подставите свои. И если офис русский, то формулу нужно будет перевести.
Изменено: memo - 17.09.2022 01:02:54
 
Перейти    
Выборка нескольких наибольших значений по сложному условию
 
#
16.09.2022 22:41:21
Выкладываю решение.
Цитата
Medvedoc написал:
Надо найти каждый наибольший пик таких возрастаний.
Для подсветки использовал УФ правило от БМВ, из #11.
Формула для извлечения значений:
Код
=MAX(INDEX(B:B;AGGREGATE(15;6;ROW($C$2:$C$100)/(($C$1:$C$75)<>($C$2:$C$76));INT((ROWS($E$2:E2)-1)*2)+1)):INDEX(B:B;AGGREGATE(15;6;ROW($C$2:$C$100)/(($C$1:$C$75)<>($C$2:$C$76));INT((ROWS($E$2:E2)-1)*2)+3)-1))

ВАЖНО! Для корректной работы формулы в конце диапазона, С2:C75, в данном случае в ячейке С76 должен стоять какой-нибудь символ, можно просто пробел.
 
Перейти    
Транспортная задача, Решение транспортной задачи
 
#
15.09.2022 19:17:00
Наверное так?
По вертикали: =SUMPRODUCT(A2*B2:F2)
По горизонтали: =SUMPRODUCT($A2:$A13;B2:B13)
 
Перейти    
посчитать промежуточные итоги по нескольким условиям
 
#
15.09.2022 11:19:17
Добавим в кучу :-)
Сумма:
=SUM(SUMIF(E$3:E23;{"дополнительно""основной"};F$3:F23))
Общая:
=SUMPRODUCT(F$3:F4*(B$3:B4=B5)*COUNTIFS(D$28:D$36;"добавляем в итог";C$28:C$36;E$3:E4))
 
Перейти    
Страницы: 1 2 3 4 5 6 7 8 9 10 11 ... 39 След.
Наверх