Видеонаблюдение

Анализ таблицы Excel на поиск совпадающих чисел и их сумм

1 декабря 2024

Задача: проанализировать таблицу с банковскими транзакциями, чтобы найти счета, даты и суммы переводов, где сумма переводов совпадает с заданной, включая возможные комбинации нескольких операций в одну дату.

Данные в таблице:

  • Номера счетов
  • Даты переводов
  • Суммы переводов
  • Контрагенты (отправитель/получатель)

Цель:
Выявить транзакции (или их комбинации) на указанную сумму в заданную дату.


Для анализа такой таблицы и поиска переводов с указанными параметрами можно использовать следующие методы:

1. Поиск и фильтрация с использованием Excel:

  • Фильтры: Используйте фильтры на колонках для выбора строк, соответствующих указанным датам, суммам или счетам.
  • Функция "Автофильтр": Поможет быстро выделить строки с совпадением по интересующим значениям.
  • Условное форматирование: Для подсветки строк, где сумма переводов совпадает с искомой.

2. Ручной анализ с использованием формул в Excel:

  • Формулы "СУММЕСЛИ" и "СУММЕСЛИМН": Вычисляют суммы переводов по заданным условиям. 
    =СУММЕСЛИМН(Суммы; Даты; "2024-11-29"; Счета; "123456")
  • Формула "ЕСЛИ": Для пометки строк с совпадением параметров.
  • Связь через "ВПР": Для поиска конкретных значений в другой части таблицы.

3. Анализ с помощью Python (для больших таблиц):

  • Использование библиотек:
    • pandas: для обработки и анализа таблиц.
    • openpyxl/xlsxwriter: для работы с файлами Excel.
    • numpy: для численных вычислений.

Пример анализа в Python:

import pandas as pd

# Загрузка файла Excel
file_path = 'transactions.xlsx'
df = pd.read_excel(file_path)

# Фильтрация данных по дате, сумме и счетам
target_date = '2024-11-29'
target_sum = 1000  # искомая сумма
filtered_data = df[(df['Дата'] == target_date) & (df['Сумма'] == target_sum)]

# Вывод строк, соответствующих условиям
print(filtered_data)

# Группировка по счетам для проверки совпадений
group_check = df.groupby(['Счет'])['Сумма'].sum()
print(group_check)
  • Этот код находит строки, где сумма и дата совпадают с заданными параметрами.

4. Использование SQL (для работы с БД):

Если данные хранятся в базе, используйте SQL-запросы:

SELECT *
FROM transactions
WHERE date = '2024-11-29' AND amount = 1000;

5. Использование BI-инструментов:

  • Power BI или Tableau: Подключитесь к Excel-файлу и создайте визуализации/фильтры для анализа.
  • Преимущество: быстрый интерактивный поиск закономерностей.

6. Сложный перебор возможных комбинаций:

Если задача требует анализа всех комбинаций переводов для поиска заданной суммы:

  • Используйте алгоритм "поиск подмножества" (Subset Sum Problem):
    • Реализация возможна через Python:
    from itertools import combinations
 # Все суммы переводов amounts = df['Сумма'].tolist()  # Целевая сумма target_sum = 1000  # Поиск всех комбинаций, дающих целевую сумму for r in range(1, len(amounts) + 1):     for combo in combinations(amounts, r):         if sum(combo) == target_sum:             print("Найдена комбинация:", combo)

7. Автоматизация через PowerShell:

Если вы работаете в Windows:

Import-Excel -Path "transactions.xlsx" | 
Where-Object { $_.Дата -eq '2024-11-29' -and $_.Сумма -eq 1000 } | 
Format-Table

Выбор подхода

  • Маленькие данные: Используйте Excel (ручные фильтры или формулы).
  • Средние данные: Python для автоматизации анализа.
  • Большие данные: BI-инструменты или SQL-запросы.
  • Сложные случаи (переборы): Алгоритмические методы (Subset Sum).