Как найти совпадающие суммы в Excel и банковских выписках

Практика / данные

Как применять материал

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

Практический порядок

1. привести даты, суммы и номера счетов к единому формату
2. отделить точные совпадения от комбинаций нескольких операций
3. проверить результат фильтрами, сводной таблицей или отдельной контрольной формулой
4. сохранить методику, чтобы ее можно было повторить на следующей выгрузке

=СУММЕСЛИМН(Суммы; Даты; ДАТА(2024;11;29); Счета; "123456")

"""
Поиск банковских операций и комбинаций на заданную сумму.

Ожидаемые колонки Excel:
- Дата
- Счет
- Сумма
- Контрагент (необязательно)

Скрипт не изменяет исходный файл. Результат сохраняется в отдельный Excel-файл.
"""

from __future__ import annotations

from itertools import combinations
from pathlib import Path

import pandas as pd

SOURCE_FILE = Path("transactions.xlsx")
RESULT_FILE = Path("transactions_check_result.xlsx")

TARGET_DATE = "2024-11-29"
TARGET_AMOUNT = 1000.00
MAX_COMBINATION_SIZE = 4
MAX_ROWS_PER_ACCOUNT = 80


def normalize_money(series: pd.Series) -> pd.Series:
    return (
        series.astype(str)
        .str.replace(" ", "", regex=False)
        .str.replace(",", ".", regex=False)
        .pipe(pd.to_numeric, errors="coerce")
        .round(2)
    )


def load_transactions(path: Path) -> pd.DataFrame:
    if not path.exists():
        raise FileNotFoundError(f"Файл не найден: {path.resolve()}")

    df = pd.read_excel(path)
    required = {"Дата", "Счет", "Сумма"}
    missing = required - set(df.columns)
    if missing:
        raise ValueError(f"В таблице нет обязательных колонок: {', '.join(sorted(missing))}")

    df = df.copy()
    df["Дата"] = pd.to_datetime(df["Дата"], dayfirst=True, errors="coerce").dt.date
    df["Сумма"] = normalize_money(df["Сумма"])
    df["Счет"] = df["Счет"].astype(str).str.strip()
    return df.dropna(subset=["Дата", "Сумма", "Счет"])


def find_exact_matches(df: pd.DataFrame, target_date: str, target_amount: float) -> pd.DataFrame:
    date_value = pd.to_datetime(target_date).date()
    return df[(df["Дата"] == date_value) & (df["Сумма"].round(2) == round(target_amount, 2))]


def find_combinations(df: pd.DataFrame, target_date: str, target_amount: float) -> pd.DataFrame:
    date_value = pd.to_datetime(target_date).date()
    day_rows = df[df["Дата"] == date_value].reset_index(drop=True)
    results: list[dict[str, object]] = []

    for account, group in day_rows.groupby("Счет", dropna=False):
        records = group.reset_index().to_dict("records")
        if len(records) > MAX_ROWS_PER_ACCOUNT:
            raise ValueError(
                f"Для счета {account} найдено {len(records)} операций за день. "
                f"Сузьте выборку до {MAX_ROWS_PER_ACCOUNT} строк или используйте "
                "специализированный алгоритм вместо полного перебора комбинаций."
            )
        for size in range(2, min(MAX_COMBINATION_SIZE, len(records)) + 1):
            for combo in combinations(records, size):
                total = round(sum(float(item["Сумма"]) for item in combo), 2)
                if total == round(target_amount, 2):
                    results.append({
                        "Счет": account,
                        "Дата": date_value,
                        "Сумма комбинации": total,
                        "Количество операций": size,
                        "Строки": ", ".join(str(item["index"] + 2) for item in combo),
                    })

    return pd.DataFrame(results)


def main() -> None:
    df = load_transactions(SOURCE_FILE)
    exact = find_exact_matches(df, TARGET_DATE, TARGET_AMOUNT)
    combos = find_combinations(df, TARGET_DATE, TARGET_AMOUNT)

    with pd.ExcelWriter(RESULT_FILE, engine="openpyxl") as writer:
        exact.to_excel(writer, sheet_name="Точные совпадения", index=False)
        combos.to_excel(writer, sheet_name="Комбинации", index=False)

    print(f"Готово: {RESULT_FILE.resolve()}")
    print(f"Точных совпадений: {len(exact)}")
    print(f"Комбинаций: {len(combos)}")


if __name__ == "__main__":
    main()

-- Пример для таблицы в базе данных.
-- Названия таблицы и колонок адаптируйте под вашу схему.

SELECT
    id,
    transfer_date,
    account_number,
    amount,
    counterparty
FROM transactions
WHERE transfer_date = DATE '2024-11-29'
  AND account_number = '123456'
  AND amount = 1000.00;

[CmdletBinding()]
param(
    [string]$SourceFile = ".\transactions.xlsx",
    [datetime]$TargetDate = "2024-11-29",
    [decimal]$TargetAmount = 1000,
    [string]$TargetAccount = "123456"
)

if (-not (Get-Module -ListAvailable -Name ImportExcel)) {
    throw "Не найден модуль ImportExcel. Установите его отдельно и проверьте политику выполнения скриптов в вашей организации."
}

if (-not (Test-Path -LiteralPath $SourceFile)) {
    throw "Файл не найден: $SourceFile"
}

$rows = Import-Excel -Path $SourceFile

$matches = $rows | Where-Object {
    $rowDate = [datetime]$_.'Дата'
    $rowAmount = [decimal]$_.'Сумма'
    $rowAccount = [string]$_.'Счет'

    $rowDate.Date -eq $TargetDate.Date -and
        $rowAmount -eq $TargetAmount -and
        $rowAccount -eq $TargetAccount
}

if (-not $matches) {
    Write-Host "Совпадения не найдены."
    return
}

$matches | Format-Table -AutoSize

Что проверить перед внедрением

• суммы не смешивают рубли, копейки, комиссии и возвраты
• даты операций и даты списания не перепутаны между собой
• исключения описаны отдельно, а не удалены из таблицы молча
• исходный файл сохранен без ручной правки

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