Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Поиск ячеек с числами в строках с определенным количеством столбцов
 
#1
09.09.2019 14:45:37
Добрый день! Спасибо всем гуру этого форума, которые помогают в простом решении непростых задач. Дай бог здоровья вашим мозгам.
Требуется еще помощь.

Есть определенное количество строк по шесть столбцов каждая. В ячейках проставлены числа, например 1,2,3,4,5,6. Необходимо настроить поиск последовательных чисел по всем строкам. Например найти ячейки с числами 1,2,3, либо 3,4,5,6...и т.д.
И второе. Можно ли сделать так, чтобы кол-во совпадений проставлялось в столбце справа: например 1,2,3 - 3 совпадения, 3,4,5,6 - 4 совпадения
Изменено: Cupon - 09.09.2019 16:35:19
 
 
 
#2
09.09.2019 14:48:40
пример в файле где что есть что надо
Лень двигатель прогресса, доказано!!!
 
 
 
#3
09.09.2019 15:53:12
Сергей,пример сбросил. Извиняюсь, если неправильно ставлю задачу
 
 
 
#4
09.09.2019 16:36:20
Cupon, ну, если без изысков, без массивов и по пролетарски в лоб, то вот так можно. А если нужно именно в одном столбце, то ждите формулистов.
Не сложно задачка решается еще и в Power Query. Причем в PQ её можно решить для произвольного количества столбцов. Интересует?
Изменено: PooHkrd - 09.09.2019 16:36:33
Вот горшок пустой, он предмет простой...
 
 
 
#5
09.09.2019 16:49:16
на формулиста не претендую, но вариант без допов:
Код
 =СУММ(--((B2:F2-A2:E2)=1))+(СУММ(--((B2:F2-A2:E2)=1))>0)
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#6
09.09.2019 17:00:12
PooHkrd,
Цитата
Интересует?
да, потому-что число столбцов будет меняться в будущем и у меня все строки именно в PQ или PP. Вы мне там и помогали, составляли таблицу
Изменено: Cupon - 09.09.2019 17:06:20
 
 
 
#7
09.09.2019 17:02:05
buchlotnik,да, именно. Спасибо большое
 
 
 
#8
09.09.2019 17:42:31
Cupon, вариант решения на PQ:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    by = List.Count(Table.ColumnNames(Source)),
    Custom1 = Table.AddColumn(Source, "Совпадения", each [
        a = List.Buffer(Record.ToList(_)), 
        b = List.Transform(a, each -_), 
        c = List.Zip({ List.FirstN(b, by-1), List.LastN(a, by-1) }), 
        d = List.Transform(c, each List.Sum(_)),
        e = List.Sum( List.Select(d, each _ = 1) ) + 1][e])
in
    Custom1

И вот тут файлик перезалил, добавил вам там в генератор формирование этого столбца. В модели PowerPivot такое, можно сделать, но оно будет не динамически рассчитываемо. Но сводная на большом массиве будет обновляться несколько дольше.
Вот горшок пустой, он предмет простой...
 
 
 
#9
09.09.2019 22:13:45
PooHkrd,  :idea: мегамозг )
огромное спасибо!

начал разбираться, увидел одно НО
если видно из файла....код  считает как две последовательные пары за 3 и последовательность трех чисел тоже 3
тоже самое и с 4 и 5
можно это как-то исправить. Например через запятую итог
2;2
3;2
Или может будут получше мысли у вас...
Изменено: Cupon - 09.09.2019 22:55:04
 
 
 
#10
09.09.2019 22:59:02
Если я правильно понял, нужно найти максимальное число последовательных чисел в строке. Без промежутка.
Код
=МАКС(ЧАСТОТА(СТОЛБЕЦ(A:F);СТОЛБЕЦ(A:E)*(1-(B2:F2-A2:E2=1))))

*Чуть упростил:

Код
=МАКС(ЧАСТОТА(СТОЛБЕЦ(A:F);СТОЛБЕЦ(A:E)*(B2:F2-A2:E2<>1)))
Изменено: Светлый - 10.09.2019 06:55:57
 
 
 
#11
10.09.2019 06:43:50
Цитата
buchlotnik написал:
но вариант без допов
Если в строчке встретятся две пары последовательных чисел Ваша формула выдаст 3, а должно быть 2.
 
 
 
#12
10.09.2019 07:06:56
Светлый, я сделал без допов вариант от PooHkrd, про несколько пар в исходном примере ни слова - см  #1 и учтите, что мое сообщение #5, а про несколько пар возникло в #9
я знаю ограничения своей формулы, спасибо
Изменено: buchlotnik - 10.09.2019 07:14:10
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#13
10.09.2019 07:20:33
Off
Цитата
buchlotnik написал:
сообщение #5
ой, да ладно.
! Прям как я в макросах :-).
По вопросам из тем форума, личку не читаю.
 
 
 
#14
10.09.2019 08:37:56
Доброе время суток
Цитата
Cupon написал:
код  считает как две последовательные пары за 3 и последовательность трех чисел тоже 3
Кто что считает. Код Алексея
Цитата
e = List.Sum( List.Select(d, each _ = 1) ) + 1][e])
считает сколько есть чисел в строке с расстоянием в 1 с просмотром слева на право по строке :)  Хотя где то сбой на строке
{111, 112, 113, 117, 121, 122} находит 4, хотя их пять :)  Но, если переставить {122, 121, 117, 113, 112, 111}, то будет 0
А вам что нужно? Может попытаетесь объяснить... А то коллеги - кто в лес кто по дрова.
 
 
 
#15
10.09.2019 09:14:16
Андрей VG, не учел, что в ряду может быть более одной последовательности. Тут мой косяк, нужно считать не голую сумму списка, а разобрать на локальные группы единичек и считать суммы этих групп прибавляя к каждой сумме +1. Вот поправил:
Скрытый текст

Цитата
Андрей VG написал:
Но, если переставить {122, 121, 117, 113, 112, 111}, то будет 0
Там такого не будет, в каждой строке списки всегда по возрастанию. Генератор строчек для данной задачи я (и не только) для ТС делал в другой теме. Ссылку приводил выше.
Но в свете последних заявлений ТС, мне уже самому не понятно чего именно он хочет. То ли посчитать количество элементов, которые составляют последовательности, то ли количество самих последовательностей.
Cupon, уважаемый, прошу определиться с условиями задачи.
Изменено: PooHkrd - 10.09.2019 10:41:37
Вот горшок пустой, он предмет простой...
 
 
 
#16
10.09.2019 10:46:57
Цитата
Cupon написал:
можно это как-то исправить. Например через запятую итог 2;2
Формула массива:
Код
=ПОДСТАВИТЬ(ТЕКСТ(ПОДСТАВИТЬ(СУММ(ЧАСТОТА(СТОЛБЕЦ(A:F);СТОЛБЕЦ(A:E)*(B2:F2-A2:E2<>1))*10^{5:4:3:2:1:0});1;);"0-0-0-0-0");"0-";)
Изменено: Светлый - 10.09.2019 12:45:43
 
 
 
#17
10.09.2019 10:59:07
Цитата
Светлый написал:
=МАКС(ЧАСТОТА(СТОЛБЕЦ(A:F);СТОЛБЕЦ(A:E)*(B2:F2-A2:E2<>1)))
как вариант, спасибо

Андрей VG, у меня числа будут идти всегда в порядке возрастания. Я и сам не учел что будут парные последовательности) сейчас ломаю голову...как мне лучше будет
PooHkrd, мне конечно лучше количество элементов, которые составляют последовательности (т.е. 1,2,3,4  - 4; 1,2,3 - 3 и т.д.)
а можно изменить формулу в вашем варианте. Может сделать как посоветовал Светлый
 
 
 
#18
10.09.2019 11:31:02
Цитата
Cupon написал:
Может сделать как посоветовал  Светлый
Ну, в принципе, можно и как Светлый.
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    by = List.Count(Table.ColumnNames(Source)),
    Custom1 = Table.AddColumn(Source, "Совпадения", each [
        a = List.Buffer(Record.ToList(_)), 
        b = List.Transform(a, each -_), 
        c = List.Zip({ List.FirstN(b, by-1), List.LastN(a, by-1) }), 
        d = List.Transform(c, each if List.Sum(_) = 1 then 1 else null),
        e = Table.Group( Table.FromColumns({ d }, {"1"}), {"1"}, {"2", each List.Sum([1]) + 1 }, GroupKind.Local )[2],
        f = Text.Combine( List.Transform(e, Text.From), "-")][f])
in
    Custom1

Странно вы в свою лотерею выигрываете.  :D  Ну, да вам решать.
Вот горшок пустой, он предмет простой...
 
 
 
#19
10.09.2019 11:43:32
PooHkrd,тут понимаете идет подбор с включением фантазии) сразу трудно сказать что именно надо. Будем спотыкаться, падать и подниматься
PooHkrd,знаете, а вот 2-2, 2-3 мне нравится. Я думаю это оптимальный вариант
только я имел ввиду как в PQ вставить новую формулу
Изменено: Cupon - 10.09.2019 11:56:16
 
 
 
#20
10.09.2019 12:15:08
Цитата
Cupon написал:
только я имел ввиду как в PQ вставить новую формулу
Если вы про ваш файлик из предыдущей темы, то вам туда я формулу перезалил. Но давайте вы все таки уже будете сами учиться втыкать готовые куски решений в ваш инструмент, поверьте, это совсем не сложно. Постоянно такой ерундой я заниматься не буду, это тогда уже техническая поддержка называется.
Вот горшок пустой, он предмет простой...
 
 
 
#21
10.09.2019 12:17:57
Цитата
Cupon написал:
сейчас ломаю голову...как мне лучше будет
Стачала нужно думать над тем, что есть и может быть, а уж потом над тем, что лучше ;)
Как в таком случае лучше?
1, 2, 3, 4, 5, 6
1, 2, 3, 4, 5, 11
1, 2, 3, 4, 11, 12
1, 2, 3, 11, 12, 13
1, 2, 11, 12, 13, 14
или
1, 2, 11, 12, 21, 22
1, 2, 31, 32, 41, 42
11, 12, 31, 32, 51, 52
21, 22, 41, 42, 51, 52
Изменено: Андрей VG - 10.09.2019 12:18:20
 
 
 
#22
10.09.2019 13:34:59
Андрей VG,ну вот выше мне понравилась идея PooHkrd через тире:
1, 2, 3, 4, 5, 6 - 6
1, 2, 3, 4, 5, 11 - 5-1
1, 2, 3, 4, 11, 12 - 4-2
1, 2, 3, 11, 12, 13 - 3 -3
1, 2, 11, 12, 13, 14 - 2-2-2

PooHkrd, приношу извинения, впредь буду додумывать
 
 
 
Страницы: 1
Наверх