Разность множеств в excel
Среди всех арифметических действий можно выделить четыре основных: сложение, умножение, деление и вычитание. О последнем и пойдет в речь в данной статье. Давайте рассмотрим, какими методами можно выполнять данное действие в Excel.
Процедура вычитания
В вычитании в программе Эксель могут участвовать как конкретные числа, так и ячейки, содержащие числовые значения.
Само действие можно выполнить с помощью формулы, которая начинается со знака “равно” (“=”). Затем, согласно законам арифметики, пишем уменьшаемое, после него ставим знак “минус” (“-“) и в конце указываем вычитаемое. В сложных формулах вычитаемых можно быть несколько, и в этом случае, они идут следом, а между ними ставится ставится “-“. Таким образом, мы получаем результат в виде разности чисел.
Для большей наглядности давайте рассмотрим, как выполнять вычитание на конкретных примерах ниже.
Пример 1: разность конкретных чисел
Допустим, нам нужно найти разность между конкретными числами: 396 и 264. Выполнить вычитание можно с помощью простой формулы:
Примечание: безусловно, программа Эксель умеет работать и с отрицательными числами, поэтому, вычитание можно выполнить в обратном порядке. В этом случае формула выглядит так: =264-365 .
Пример 2: вычитание числа из ячейки
Теперь, когда мы разобрали принцип и самый простой пример вычитания в Excel, давайте посмотрим, как вычесть конкретное число из ячейки.
- Как и в первом методе, сначала выбираем свободную ячейку, куда хотим вывести результат вычисления. В ней:
- пишем знак “=”.
- указываем адрес ячейки, в которой находится уменьшаемое. Сделать это можно вручную, прописав координаты с помощью клавиш на клавиатуре. Либо можно выбрать нужную ячейку, кликну по ней левой кнопкой мыши.
- добавляем в формулу знак вычитания (“-“).
- пишем вычитаемое (если вычитаемых несколько, добавляем их через символ “-“).
- После нажатия клавиши Enter, получаем результат в выбранной ячейке.
Примечание: данный пример работает и обратном порядке, т.е. когда уменьшаемое – это конкретное число, а вычитаемое – это числовое значение в ячейке.
Пример 3: разность между числами в ячейках
Так как в Эксель мы, прежде всего, работаем со значениями в ячейках, то и вычитание, чаще всего, приходиться проводить между числовыми данными в них. Действия практически идентичны вышеописанным.
Пример 4: Вычитание одного столбца из другого
Таблицы, как мы знаем, содержат данные как по горизонтали (столбцы), так и по вертикали (строки). И довольно часто требуется найти разность между числовыми данными, содержащимися в разных столбцах (двух и более). Причем, желательно автоматизировать данный процесс, чтобы не тратить на выполнение этой задачи много времени.
Программа предоставляет пользователю такую возможность, и вот как ее можно реализовать:
Пример 5: Вычитание конкретного числа из столбца
В некоторых случаях требуется вычесть из всех ячеек столбца одно и то же конкретное число.
Данное число можно просто указать в формуле. Допустим, мы хотим вычесть из первого столбца нашей таблицы число 65.
Теперь давайте предположим, что мы хотим вычесть конкретное число из всех ячеек столбца, но оно не просто будет указано в формуле, а будет также записано в определенной ячейке.
Несомненный плюс данного метода в том, если мы захотим изменить это число, нам достаточно будет изменить его в одном месте – в содержащей его ячейке (в нашем случае – D2).
Алгоритм действий в данном случае следующий:
- Переходим в самую верхнюю ячейку столбца для вычислений. Пишем в нем привычную формулу вычитания между двумя ячейками.
- Когда формула готова, не спешим нажимать клавишу Enter. Чтобы при растягивании формулы зафиксировать адрес ячейки с вычитаемым, необходимо напротив ее координат вставить символы “$” (другими словами, сделать адрес ячейки абсолютными, так как по умолчанию ссылки в программе относительные). Сделать это можно вручную, прописав в формуле нужные символы, или же, при ее редактировании переместить курсор на адрес ячейки с вычитаемым и один раз нажать клавишу F4. В итоге формула (в нашем случае) должна выглядеть так:
- После того, как формула полностью готова, жмем Enter для получения результата.
- С помощью маркера заполнения производим аналогичные вычисления в остальных ячейках столбца.
Примечание: рассмотренный выше пример можно рассмотреть в обратном порядке. Т.е. вычесть из одной и той же ячейки данные из другого столбца.
Заключение
Вычислить разницу между двумя значениями или временами может быть легко для большинства пользователей Excel, но пытались ли вы рассчитать абсолютную разницу между значениями, как показано на скриншоте ниже? В этой статье я расскажу о некоторых хитростях, которые помогут быстро определить разницу между значениями в Excel.
Вычислить абсолютные различия с помощью Kutools for Excel
Вычислить абсолютные разницы по формуле
Чтобы вычислить абсолютные различия, вам просто нужна одна из формул ниже, вставьте одну из формул ниже в пустую ячейку, в которую вы хотите поместить результат, нажмите Enter и перетащите маркер заполнения над ячейками, в которых нужна эта формула.
=IF(B2>=A2,B2-A2,A2-B2)
Примечание: если вы хотите рассчитать абсолютную разницу между временами, вы можете применить только = ABS (A2-B2), а затем отформатировать значения как время. Смотрите скриншот:
Вычислить абсолютные различия с помощью Kutools for Excel
Если у вас есть Kutools for Excel установлен, его Изменить знак ценностей Утилита также может быстро рассчитать абсолютную разницу между двумя числами.
После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите ячейку, в которую вы хотите поместить различия, введите = F2-G2 , и перетащите маркер заполнения вниз, чтобы заполнить эту формулу ячейками.
2. Нажмите Kutools > Content > Изменить знак ценностей, В Изменить знак ценностей диалог, проверьте Измените все отрицательные значения на положительные вариант. Смотрите скриншот:
3. Нажмите Ok or Применить, и появится диалоговое окно, напоминающее вам некоторую информацию, нажмите Да продолжать. Смотрите скриншот:
Теперь все отрицательные разницы преобразованы в абсолютные.
Операции над множествами элементов списка кажутся простыми с SQL или Python. Но как это сделать в xls?
Примечание: это должна быть автоматизация с минимальным количеством копий-вставок и кликов. Например, я не хочу копировать-вставлять A ниже B, а затем "исключать дубликаты", чтобы получить A B.
1 ответ
Пересечение (в A & B): =IFNA(VLOOKUP(B2,$A$2:$B$42,1,FALSE),"")
Союз (в A или B): =IFS(A2,A2,B2,B2) Обратите внимание, что IFS только в последних (по состоянию на 2018) версиях.
A - B (только в A): =IF(NOT(IFNA(MATCH(A2,$B$2:$B$42,0),FALSE)),IF(A2,A2,""),"")
B - A (только в B): =IF(NOT(IFNA(MATCH(B2,$A$2:$A$42,0),FALSE)),IF(B2,B2,""),"") (Поменяйте местами буквы)
Ну, Microsoft Excel не обрабатывает встроенные операции над множествами. Но вы можете эмулировать VBA, используя функцию MATCH и обработку ошибок.
Вот код, который работал для меня (я предполагаю, что у вас заголовок в первой строке):
Вы можете просто использовать формулу ниже, чтобы получить результат
= ЕСЛИ (НИЖНИЙ (A4)= НИЖНИЙ (B4); ""; A4)
Я удивлен на нескольких уровнях:
(1) 2020 год. и все еще нет установленных функций в Excel
(2) Ответ, получивший наибольшее количество голосов (с 2018 года), очень непрактичен: в реальном времени наборы данных не поступают аккуратно со вставленными пустыми строками, в которых отсутствует значение, по сравнению с другим набором данных; что является предварительным условием для этого решения.
Наиболее практичным решением (хотя до сих пор неловко, ты слышишь нас, Microsoft. ) является работа вокруг с помощью сводной таблицы:
- Добавьте столбец в набор A, с именем столбца "set_name" и всеми значениями в столбце, установленными на "A" -> поворотный ввод A
- Добавьте столбец в набор B, с именем столбца "set_name" и всеми значениями в столбце, установленными на "B" -> поворотный ввод B
- скопировать сводный вход B (без имен столбцов;-)) под сводный вход A, чтобы сформировать объединенный диапазон -> объединенный диапазон
- создать сводную таблицу из объединенного диапазона с
--- "имена наборов", используемые для формирования столбцов сводной таблицы
--- функция сводной таблицы установлена на "count()"
Результатом является своего рода сводная таблица с горячим кодированием:
- 1-й столбец: объединенный набор A и B (также известный как ВСЕ возникающие значения)
- 2-й столбец: 1-значения только для элементов, входящих в набор A
(предостережение: предполагается, что A содержит только УНИКАЛЬНЫЕ элементы. В противном случае возможны
значения> 1) - 3-й столбец: 1-значения только для элементов, входящих в набор B
(применяется то же предостережение, что и для набора A) - Столбец "Итого": значения, показывающие "2", существуют в обоих наборах
Результирующую сводную таблицу можно легко отфильтровать по различным наборам и пересечениям с помощью значений в столбцах 2 (он же "набор A"),3 (он же "набор B") и 4 (он же "Набор A И набор B").
Читайте также: