Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
В "таблице с выплатами" найти строки/ячейки, что не учитываюься по критериям в формулах =суммеслимн.
 
#1
07.12.2023 17:53:22
Добрый день, уважаемые программисты! Долго не мог найти на форуме решение для моей проблемы. Поэтому решил обратиться за помощью напрямую по следующему вопросу: "как можно найти строки (ячейки) в таблице с данными которые не задействовались формулами суммеслимн."  
Пояснение с упрощенным примером с задачей в конце:  

1. У нас есть "таблица с выплатами" за месяц по столбцам "получатель", "сумма по дебету", "назначение платежа" (в примере всего 3 столбца с данными и 19 строк для простоты восприятия)
2. Нам нужно распределить данные из таблицы по отдельным категориям для анализа данных.
3. Поэтому в этом же листе мы создали "таблицу критериев", где суммируем данные из столбца "суммы по дебету" формулой =СУММЕСЛИМН по критериям взятым из столбцов "назначение платежа" и "получатель". Диапазон ячеек для критериев и суммы на всю таблицу. (критерии текстовые)
4. И распределяем критерии по категориям (в приложенном файле всё наглядно видно)
5. После того, как мы это сделали, в "таблице критериев" у нас получается итоговая сумма критериев и сумма каждой категории.
6. Но в "таблице с выплатами" остаются неучтённые ячейки, не попавшие ни под один критерий. (Для понимания: в "таблицу с выплатами" каждый месяц могут попадать небольшой процент данных, встречающиеся редко или впервые.)
7. Задача: есть ли способ, функция, чтобы автоматически или очень быстро отыскать и наглядно увидеть все неучтённые в "таблице критериев" данные? Другими словами: Можно ли как-то изменить или дополнить формулу =СУММЕСЛИМН для решения проблемы?

*Фильтром, расширенным фильтром, сортировкой и сводными таблицами, конкретно у меня не получилось эффективно решить вопрос, тоже самое с функцией "влияющие ячейки".

*Если я правильно понял формулы =СУММЕСЛИМН уже определили в себе все учтённые критериями ячейки. Может есть способ как-то увидеть учтённые наглядно и так найти неучтённые.
 
Спасибо за уделенное время
Изменено: Fi1on - 08.12.2023 04:49:44
 
 
 
#2
08.12.2023 02:42:23
По хорошему, нужно где-то в исходной таблице сразу проставлять категорию или, хотя-бы, унифицировать названия назначения платежей. То, что Вы пытаетесь сделать с 'критериями' это костыль, когда нибудь он подведет
Согласие есть продукт при полном непротивлении сторон
 
 
 
#3
08.12.2023 04:45:59
Цитата
написал:
По хорошему, нужно где-то в исходной таблице сразу проставлять категорию или, хотя-бы, унифицировать названия назначения платежей. То, что Вы пытаетесь сделать с 'критериями' это костыль, когда нибудь он подведет
Всё верно говорите. Но я не могу повлиять на исходную таблицу, при имеющихся обстоятельствах это кажется оптимальным вариантом. Также по этой причине нет задачи 100% автоматизации и данные все равно будут анализироваться лично, только значительно быстрее. И сильно тормозит процесс поиска неучтённых ячеек, поэтому ищу решения по ускорению процесса.
 
 
 
#4
08.12.2023 06:27:14
Добрый день! Если конечно я правильно понял задачу:)

В файле выделил желтым, в первом столбце функция, во втором желтом столбце то, как должны быть записаны критерии (через перенос строки), функция ищет каждый критерий в тексте и при нахождении всех критериев возвращает значение категории (тут можно подредактировать, в зависимости что нужно вывести, при не нахождении хотя бы одного из указанных критериев возвращает 0, 0 - значит платеж не попал не под одну категорию. Файл во вложении.

Код
Option Explicit
Option Compare Text

Public Function fCat(a As Variant, rng As Range)
    
    Dim r As Range
    Dim arr
    Dim i As Integer
    Dim j As Integer

    For Each r In rng
        Debug.Print r
    
        arr = Split(r, Chr(10))
        
        j = 0
        
        For i = 0 To UBound(arr)
            If InStr(1, a, arr(i)) > 0 Then j = j + 1
        Next i
        
        If j = UBound(arr) + 1 Then fCat = r.Value

    Next r

End Function
 
 
 
#5
08.12.2023 06:48:07
Чуток подправил, чтобы лишнее не проверял:

Код
Public Function fCat(a As Variant, rng As Range)
    
    Dim r As Range
    Dim arr
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    For k = 1 To rng.Count
    
        arr = Split(rng.Item(k), Chr(10))
            
        j = 0
        
        For i = 0 To UBound(arr)
            If InStr(1, a, arr(i)) > 0 Then j = j + 1
        Next i
        
        If j = UBound(arr) + 1 Then
            fCat = rng.Item(k).Value
            k = rng.Count
        End If
    
    Next k

End Function
Изменено: mimoprohodil - 08.12.2023 07:08:56
 
 
 
#6
08.12.2023 07:13:45
Подправил критерии для определения категории Банк, нужно вместо Сбер написать банк, тогда определяет все категории.
 
 
 
#7
08.12.2023 10:24:50
Цитата
написал:
В файле выделил желтым, в первом столбце функция, во втором желтом столбце то, как должны быть записаны критерии (через перенос строки), функция ищет каждый критерий в тексте и при нахождении всех критериев возвращает значение категории (тут можно подредактировать, в зависимости что нужно вывести, при не нахождении хотя бы одного из указанных критериев возвращает 0, 0 - значит платеж не попал не под одну категорию. Файл во вложении.
Добрый день! Да верно, теперь наглядно видно неучтённые ячейки. Буду разбираться. Спасибо!
 
 
 
#8
08.12.2023 10:31:36
Цитата
написал:
Чуток подправил, чтобы лишнее не проверял:
Благодарю! С критериям "сбер" тоже верно подмечено.)  
 
 
 
#9
08.12.2023 12:20:24
День добрый
еще вам для анализу:
 
 
 
#10
08.12.2023 12:47:59
При прочтении исходной задачи (без анализа предложенных решений) мне пришло в голову использовать условное форматирование с дубликатами всех условий, задействованных в СУММЕСЛИМН()
 
 
 
#11
09.12.2023 12:03:44
Павел \Ʌ/, Добрый день. Развернутое решение, буду всматриваться и вникать, спасибо!
 
 
 
#12
09.12.2023 12:17:41
Цитата
написал:
При прочтении исходной задачи (без анализа предложенных решений) мне пришло в голову использовать условное форматирование с дубликатами всех условий, задействованных в СУММЕСЛИМН()
Возможно это хорошее решение. Но сейчас я разбираюсь с двумя решениями выше, одно удалось применить на практике, но еще надо протестировать. Так что думаю правильно пока закрыть тему. И открыть снова если возникнет проблема. Спасибо.
 
 
 
Страницы: 1
Наверх