Вопрос или проблема
У меня есть таблица, которую я использую для проверки, балансируют ли обе стороны транзакции, она работает следующим образом:
| Компании | A-Corp | Bee Ltd | Cee Inc |
|---|---|---|---|
| A-Corp | XXX | -10,000 | -50,000 |
| Bee Ltd | 10,000 | XXX | 0 |
| Cee Inc | 47,000 | 0 | XXX |
Первая колонка — это компании, которые должны деньги, заголовок строки — это кому должны деньги.
A-Corp считает, что ему должны 10,000 от Bee Ltd и 50,000 от Cee Inc
Bee Ltd согласен, что он должен 10,000 A-Corp
Cee Inc считает, что он должен только 47,000 A-Corp
Есть ли простой способ выделить совпадающие наборы цифр?
Единственный способ, который я могу придумать, — использовать формулы условного форматирования, как это:
=$B$3=(0-$C$2) применимо к =$B$3,$C$2
=$B$4=(0-$D$2) применимо к =$B$4,$D$2 и т.д.
Но учитывая размер таблицы, которую я использую, это потребует 169 отдельных правил, так что я надеюсь, что есть более эффективный способ сделать это.
Задача состоит в том, чтобы построить формулу, которая отвечает на этот вопрос:
Совпадает ли значение в текущей ячейке с заголовком строки R и заголовком столбца C со значением в ячейке с заголовком строки C и заголовком столбца R?
В форме сетевой формулы:
=LET(
currentCell,C3,
colHeader,C$2,
rowHeader,$B3,
currentCell+INDEX($C$3:$E$5,XMATCH(colHeader,$B$3:$B$5),XMATCH(rowHeader,$C$2:$E$2))=0
)
Что, безусловно, эквивалентно этой более короткой, но, возможно, менее читаемой формуле:
=C3+INDEX($C$3:$E$5,XMATCH(C$2,$B$3:$B$5),XMATCH($B3,$C$2:$E$2))=0
И применяется как одно правило условного форматирования:
Если вы затем хотите выделить несовпадения в другом цвете, вы можете добавить второе правило и изменить оператор равенства в последней строке с = на оператор неравенства <>.
Вот очень грубо нарисованное решение: вам в основном нужно инвертировать строки и столбцы. Вы можете сделать это через INDIRECT(ADDRESS(...)) [я не уверен, разрешает ли последняя версия Excel использовать формулы внутри нотации RC].
TopLeftCell = INDEX(array, COL()-offsety, ROW()-offsetx)
Где offsety и offsetx — это строка и столбец верхнего левого угла вашей ячейки.
Ответ или решение
Условное форматирование сетки в Excel для проверки баланса транзакций
В современном бизнесе управление финансами и балансировка счетов играют ключевую роль в успешной деятельности. Excel является мощным инструментом для этого, особенно когда речь идет о проверке транзакций между различными сторонами. В данной статье мы рассмотрим, как с помощью условного форматирования выделить совпадающие и несовпадающие суммы в таблице, которая отслеживает, кто кому должен деньги.
Понимание таблицы
Ваша таблица структурирована следующим образом:
- Первая колонка содержит названия компаний, которые должны деньги.
- Заголовки столбцов соответствуют компаниям, которым эти деньги должны.
- Ячейки внутри таблицы указывают суммы, которые должны быть отданы.
Для примера, предположим, у нас три компании: A-Corp, Bee Ltd и Cee Inc. Посмотрим на их согласованные суммы и параметры.
Формулирование задачи
Мы хотим определить, совпадают ли значения в ячейках. Это подразумевает следующее условие: значение в ячейке с заголовком строки R и заголовком столбца C должно совпадать со значением в ячейке с заголовком строки C и заголовком столбца R. В Excel это можно реализовать с помощью формул и условного форматирования.
Решение с использованием функции LET и INDEX
Для достижения этой цели мы можем воспользоваться функцией LET, которая позволяет работать с переменными внутри формулы, что делает код более читаемым и управляемым. Условное форматирование можно задать следующим образом:
=LET(
currentCell, C3,
colHeader, C$2,
rowHeader, $B3,
currentCell + INDEX($C$3:$E$5, XMATCH(colHeader, $B$3:$B$5), XMATCH(rowHeader, $C$2:$E$2)) = 0
)Эта формула выполняет следующие действия:
currentCell— это значение текущей ячейки.colHeader— заголовок текущего столбца.rowHeader— заголовок текущей строки.- Функция
INDEXиспользуется для получения значения из матрицы на основе результатовXMATCH, который находит позицию заголовков.
Если сумма совпадает, то значение выражения будет равно нулю, что означает, что обе компании согласны по транзакции.
Применение условного форматирования
Для применения данного условия к диапазону ячеек в таблице:
- Выделите весь диапазон, который вы хотите отформатировать.
- Перейдите в вкладку "Главная" и выберите «Условное форматирование».
- Нажмите на «Создать правило», выберите «Использовать формулу для определения форматируемых ячеек».
- Вставьте формулу, как указано выше.
- Установите желаемый формат, например, заполнение ячейки цветом, чтобы выделить совпадающие суммы.
Обработка несовпадений
Если вы также хотите выделить несовпадающие суммы, вы можете создать второе правило, изменив оператор равенства на неравенство (<>). Это позволит вам визуально различать согласованные и несогласованные суммы, что, в свою очередь, упростит процесс анализа и принятия решений.
Заключение
Условное форматирование предоставляет мощный способ визуализировать данные в Excel, делая процесс проверки баланса транзакций более эффективным и менее затратным по времени. Используя предложенные формулы и методики, вы сможете автоматически отслеживать иManage визуально выделять как согласованные, так и несогласованные транзакции между вашими сторонами. Это не только ускоряет работу с таблицами, но и существенно уменьшает риск ошибок, связанных с обработкой финансовых данных.
Таким образом, Excel становится еще более незаменимым инструментом для бухгалтеров и финансовых аналитиков.