При нахождении разницы может возникнуть ситуация, когда получится отрицательное число.
Если вам не нужны отрицательные значения в ячейках, то можно сделать так:
1) В формулу добавить функцию ABS – это модуль числа
2) Записать сложную формулу, чтобы в ячейку с результатом записывался поясняющий текст.
После этого можно использовать условное форматирование зелёным/красным цветом.
Пример
Исходная таблица такая:
В ячейку D3 можно записать формулу =C3-B3, а затем протянуть её на весь столбец. Тогда бы в ячейке D15 мы бы получили искомую разницу столбцов.
Но нагляднее использовать более сложную формулу, которая в зависимости от разницы между доходами и расходами, выводит прибыль/убыток и на сколько именно.
Вот она:
=СЦЕПИТЬ(ЕСЛИ(B3>C3;”прибыль”;”убыток”);” “;ABS(B3-C3);” €”)
С помощью функции ЕСЛИ проверяется разность между доходом и расходом. Если доход больше, то пишем “прибыль”, если меньше – “убыток”. А затем с помощью функции СЦЕПИТЬ добавляем в запись числовое значение.
Теперь наводим курсор на нижний правый угол этой ячейки и перетаскиваем появившийся маркер в нижестоящие ячейки, чтобы скопировать формулу.
Чтобы было красивее, сделаем условное форматирование. Выделяем весь диапазон с результатами и нажимаем на кнопку “Условное форматирование” -> “Правила выделения ячеек” -> “Текст содержит”.
Создаём правила:
Получится такая итоговая таблица:
Если вас не интересуют отдельные ячейки в столбцах, а только нужно найти разницу между итоговыми значениями, то можно немного схитрить.
1) Выделите сначала первый столбец – в строке состояния будет записана сумма всех чисел в нем (вместе с количеством и средним значением).
Запишите эту цифру в отдельную ячейку.
2) Также поступите и со вторым столбцом.
3) А затем просто пропишите формулу, в которой из одной ячейки отнимается другая. Например, “=D10-D11”.
Это и будет разница столбцов.
Вычисление разницы в Microsoft Excel
Вычисление разности является одним из самых популярных действий в математике. Но данное вычисление применяется не только в науке. Мы его постоянно выполняем, даже не задумываясь, и в повседневной жизни. Например, для того, чтобы посчитать сдачу от покупки в магазине также применяется расчет нахождения разницы между суммой, которую дал продавцу покупатель, и стоимостью товара. Давайте посмотрим, как высчитать разницу в Excel при использовании различных форматов данных.
Вычисление разницы
Учитывая, что Эксель работает с различными форматами данных, при вычитании одного значения из другого применяются различные варианты формул. Но в целом их все можно свести к единому типу:
А теперь давайте рассмотрим, как производится вычитание значений различных форматов: числового, денежного, даты и времени.
Способ 1: вычитание чисел
Сразу давайте рассмотрим наиболее часто применимый вариант подсчета разности, а именно вычитание числовых значений. Для этих целей в Экселе можно применить обычную математическую формулу со знаком «-».
- Если вам нужно произвести обычное вычитание чисел, воспользовавшись Excel, как калькулятором, то установите в ячейку символ «=». Затем сразу после этого символа следует записать уменьшаемое число с клавиатуры, поставить символ «-», а потом записать вычитаемое. Если вычитаемых несколько, то нужно опять поставить символ «-» и записать требуемое число. Процедуру чередования математического знака и чисел следует проводить до тех пор, пока не будут введены все вычитаемые. Например, чтобы из числа 10 вычесть 5 и 3, нужно в элемент листа Excel записать следующую формулу:
Но значительно чаще процесс вычитания в Экселе применяется между числами, размещенными в ячейках. При этом алгоритм самого математического действия практически не меняется, только теперь вместо конкретных числовых выражений применяются ссылки на ячейки, где они расположены. Результат же выводится в отдельный элемент листа, где установлен символ «=».
Посмотрим, как рассчитать разницу между числами 59 и 26, расположенными соответственно в элементах листа с координатами A3 и С3.
- Выделяем пустой элемент книги, в который планируем выводить результат подсчета разности. Ставим в ней символ «=». После этого кликаем по ячейке A3. Ставим символ «-». Далее выполняем клик по элементу листа С3. В элементе листа для вывода результата должна появиться формула следующего вида:
Но на самом деле в некоторых случаях требуется произвести вычитание, в котором будут принимать участие, как непосредственно числовые значения, так и ссылки на ячейки, где они расположены. Поэтому вполне вероятно встретить и выражение, например, следующего вида:
Способ 2: денежный формат
Вычисление величин в денежном формате практически ничем не отличается от числового. Применяются те же приёмы, так как, по большому счету, данный формат является одним из вариантов числового. Разница состоит лишь в том, что в конце величин, принимающих участие в расчетах, установлен денежный символ конкретной валюты.
- Собственно можно провести операцию, как обычное вычитание чисел, и только потом отформатировать итоговый результат под денежный формат. Итак, производим вычисление. Например, вычтем из 15 число 3.
Существует ещё один вариант отформатировать полученный итог вычитания под денежный формат. Для этого нужно на ленте во вкладке «Главная» кликнуть по треугольнику, находящемуся справа от поля отображения действующего формата ячейки в группе инструментов «Число». Из открывшегося списка следует выбрать вариант «Денежный». Числовые значения будут преобразованы в денежные. Правда в этом случае отсутствует возможность выбора валюты и количества десятичных знаков. Будет применен вариант, который выставлен в системе по умолчанию, или настроен через окно форматирования, описанное нами выше.
Если же вы высчитываете разность между значениями, находящимися в ячейках, которые уже отформатированы под денежный формат, то форматировать элемент листа для вывода результата даже не обязательно. Он будет автоматически отформатирован под соответствующий формат после того, как будет введена формула со ссылками на элементы, содержащие уменьшаемое и вычитаемые числа, а также произведен щелчок по клавише Enter.
Способ 3: даты
А вот вычисление разности дат имеет существенные нюансы, отличные от предыдущих вариантов.
- Если нам нужно вычесть определенное количество дней от даты, указанной в одном из элементов на листе, то прежде всего устанавливаем символ «=» в элемент, где будет отображен итоговый результат. После этого кликаем по элементу листа, где содержится дата. Его адрес отобразится в элементе вывода и в строке формул. Далее ставим символ «-» и вбиваем с клавиатуры численность дней, которую нужно отнять. Для того, чтобы совершить подсчет клацаем по Enter.
Существует и обратная ситуация, когда требуется из одной даты вычесть другую и определить разность между ними в днях.
- Устанавливаем символ «=» в ячейку, где будет отображен результат. После этого клацаем по элементу листа, где содержится более поздняя дата. После того, как её адрес отобразился в формуле, ставим символ «-». Клацаем по ячейке, содержащей раннюю дату. Затем клацаем по Enter.
Также разность между датами можно вычислить при помощи функции РАЗНДАТ. Она хороша тем, что позволяет настроить с помощью дополнительного аргумента, в каких именно единицах измерения будет выводиться разница: месяцы, дни и т.д. Недостаток данного способа заключается в том, что работа с функциями все-таки сложнее, чем с обычными формулами. К тому же, оператор РАЗНДАТ отсутствует в списке Мастера функций, а поэтому его придется вводить вручную, применив следующий синтаксис:
«Начальная дата» — аргумент, представляющий собой раннюю дату или ссылку на неё, расположенную в элементе на листе.
«Конечная дата» — это аргумент в виде более поздней даты или ссылки на неё.
Самый интересный аргумент «Единица». С его помощью можно выбрать вариант, как именно будет отображаться результат. Его можно регулировать при помощи следующих значений:
- «d» — результат отображается в днях;
- «m» — в полных месяцах;
- «y» — в полных годах;
- «YD» — разность в днях (без учета годов);
- «MD» — разность в днях (без учета месяцев и годов);
- «YM» — разница в месяцах.
Итак, в нашем случае требуется вычислить разницу в днях между 27 мая и 14 марта 2017 года. Эти даты расположены в ячейках с координатами B4 и D4, соответственно. Устанавливаем курсор в любой пустой элемент листа, где хотим видеть итоги расчета, и записываем следующую формулу:
Жмем на Enter и получаем итоговый результат подсчета разности 74. Действительно, между этими датами лежит 74 дня.
Если же требуется произвести вычитание этих же дат, но не вписывая их в ячейки листа, то в этом случае применяем следующую формулу:
Опять жмем кнопку Enter. Как видим, результат закономерно тот же, только полученный немного другим способом.
Способ 4: время
Теперь мы подошли к изучению алгоритма процедуры вычитания времени в Экселе. Основной принцип при этом остается тот же, что и при вычитании дат. Нужно из более позднего времени отнять раннее.
- Итак, перед нами стоит задача узнать, сколько минут прошло с 15:13 по 22:55. Записываем эти значения времени в отдельные ячейки на листе. Что интересно, после ввода данных элементы листа будут автоматически отформатированы под содержимое, если они до этого не форматировались. В обратном случае их придется отформатировать под дату вручную. В ту ячейку, в которой будет выводиться итог вычитания, ставим символ «=». Затем клацаем по элементу, содержащему более позднее время (22:55). После того, как адрес отобразился в формуле, вводим символ «-». Теперь клацаем по элементу на листе, в котором расположилось более раннее время (15:13). В нашем случае получилась формула вида:
Как видим, нюансы подсчета разности в Excel зависят от того, с данными какого формата пользователь работает. Но, тем не менее, общий принцип подхода к данному математическому действию остается неизменным. Нужно из одного числа вычесть другое. Это удается достичь при помощи математических формул, которые применяются с учетом специального синтаксиса Excel, а также при помощи встроенных функций.
Мы рады, что смогли помочь Вам в решении проблемы. Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Как в Эксель посчитать разницу столбцов (вычесть столбец из столбца)?
Как в Экселе (Excel) найти разницу между всеми ячейками столбцов и разницу между столбцами в целом?
Рассмотрим как посчитать разницу между столбцами в Эксель на конкретном примере.
В таблице имеются данные о квартальной прибыли организации за 2017 и 2018 год.
Нужно узнать разницу в прибыли за каждый квартал 2017 и 2018 года и разницу в прибыли между двумя годами в целом.
Сделать это очень просто:
1) Создаём специальный столбец, в котором будут производиться вычисления.
2) Ставим курсор в 1 ячейку столбца (то есть E3) и в строке формул пишем:
3) Затем копируем эту формулу в нижестоящие ячейки, адреса ячеек из столбцов C и D будут меняться:
Таким образом, мы нашли разницу между всеми ячейками столбца.
Положительно значение говорит о том, что прибыль в 2018 году по сравнению с 2017 годом упала, а отрицательное значение — о том, что прибыль в 2018 году наоборот увеличилась.
Для наглядного отображения данных можно применить условное форматирование:
Если значение в ячейке > 0, то прибыль упала — выделяем красным цветом.
Если значение в ячейке < 0, то прибыль возросла — выделяем зелёным цветом.
1) Выделяем столбец E.
2) Нажимаем на кнопку «Условное форматирование» (панель инструментов «Главная») -> «Правила выделения ячеек» -> «Больше».
3) В открывшемся окне ставим значение 0 и красный тип заливки.
4) Теперь также нажимаем на «Условное форматирование» ) -> «Правила выделения ячеек» и выбираем «Меньше».
5) В итоге получим следующее оформление:
Также замечу, что можно было бы сделать и наоборот — вычитать из столбца 2018 года столбец 2017 года (например, D3 — C3).
Тогда бы получилось всё наоборот — значение > 0 означало бы увеличение прибыли, а значение < 0 — уменьшение прибыли.
Для того чтобы найти разницу между всеми ячейками нескольких столбцов в Excel, нужно в выбранной ячейке ввести формулу, по которой можно вычислить разность, то есть из уменьшаемого вычесть вычитаемое. Пример приведен ниже, формулу можно видеть в строке формул:
Чтобы применить формулу для всех ячеек в данном случае, ее нужно выделить и скопировать путем протягивания за маленький зеленый квадратик на все ячейки, в которых нужно вычислить разность.
Для того, чтобы рассчитать разность итогов, нужно сначала вычислить итоговую сумму по каждому из столбцов, а затем применить все ту же формулу.
Чтобы вычислить разницу столбцов лучше всего не протягивать формулу разницы ячеек на весь диапазон, а просто выделить диапазон для записи результатов и ввести единую формулу массива (через CTRL+Shift+Enter) указав входящие диапазоны данных.
Например чтобы посчитать столбец «Прибыль», сначала выделяем диапазон для записи результатов:
затем в редакторе формул указываем формулу для диапазонов (выручка-затраты-налоги), каждый диапазон выделяем мышкой (или прописываем руками):
Завершаем ввод формулы нажатием CTRL+Shift+Enter :
И задача решена, а в каждой ячейке столбца «Прибыль» записана одна формула, которую изменить для одной или нескольких ячеек не получится (что иногда бывает в процессе эксплуатации таблицы, для учебных примеров это неважно), сразу выскочит сообщение: «Нельзя изменить часть массива»
При редактировании формулы всего диапазона каждая колонка данных выделяется своим цветом — это удобно при проверке таблицы.
Это проще всего сделать с помощью прописывания формулы. Прежде всего нужно создать место где будут отображаться результаты — разница. Как правило это созданный новый столбик соседствующий с теми, разницу между которыми надо отобразить.
Выделите первую ячейку в этом новом столбике, и в строке команд введите формулу =C3-D3 (это номера ячеек первого и второго столбцов между которыми мы узнаем разницу (у вас эти координаты могут отличаться) ну и знак математического действия «минус»
Введя единую формулу массива при помощи CTRL+Shift+Enter и указав входящие диапазоны данные вы можете выделить для записи результатов нужное. При наличии столбца Прибыль выделите диапазон для результатов. После, укажите формулу для диапазонов в редакторе формул и выделите каждый диапазон мышкой (выручка-затраты-налоги). Заверите ввод формулы при помощи: CTRL+Shift+Enter.
Для того, чтобы рассчитать в экселе разницу между столбцами необходимо воспользоваться формулой. Для разницы необходимо создать отдельный столбец. Нажимаем равно и вводим данные первого столбика, затем нажимаем знак минус и вводим данные второго столбца. Конечно же предварительно нужно выбрать диапазон ячеек.
Ссылки в excel бывают следующих типов:
Абсолютная ссылка — это ссылка, которая позволяет сделать ячейку фиксированной. Так, чтобы в формуле была ссылка на определенную строку/столбец.
Относительная ссылка в excel используется чаще. Это такие ссылки, в которых при копировании формулы адрес/координаты ячейки будут изменены.
Смешанная ссылка — это наполовину абсолютная и наполовину относительная ссылка. В ней зафиксирован либо столбец, либо строка.
Есть ещё вид ссылок, который появляется вследствие ошибки пользователя при работе в excel. Это циклическая ссылка, когда ячейка в формуле ссылается сама на себя.
Не совсем поняла Ваш вопрос, но постараюсь ответить (насколько поняла).
В пустой ячейке ставите знак «=», затем мышкой щелкаете на ту ячейку, где стоит «общая сумма процентов» (как Вы выразились), ставите знак деления (/) и необходимую цифру процента. Нажимаете Enter.
Выглядеть будет примерно так: =А10/4, где А10 — ячейка с «общей суммой процентов», 4 — тот процент, который Вам нужен.
После этого формулу Вы уведите в строке формул, а в ячейке будет итоговая цифра.
Поставить можно, нужно чтобы версия Андроида была не ниже 1.5, У меня работает нормально (Android 2.3), конечно большинство функций нет, это не компьютер, но для печати текста и его редактирования пойдёт. Лучше Worda и Exelя пока ничего не придумали. Так что ставь с чистой душой. Вот ссылка на торрент http://www.rutor.org/torrent/273815/smart-office-1.9.2_2.1.4-2013-android и вот http://www.rutor.org/torrent/262547/officesuite-pro-7.1.1237-2013-android
Запустите Excel Идите Сервис>Параметры>Вычисления> Переключатель должен стоять в положении автоматически. Если у Вас стоит офис 97-2003
В 2007-2010 вкладка Формулы, категория Параметры вычислений, установить переключатель в положение Автоматически.
Время, в Excel является числом, точнее, десятичной дробью меньше единицы. (Соответственно дата — число, больше единицы. А дата с временем — это сумма этих чисел.).
Что бы данное число смотрелось в клетке как Дата, или Время, или Дата с Временем — для этого необходимо задать определенный формат с свойствах ячейки (Втор. кл. мыши по ячейке — пункт «Формат Ячейки»). Следственно, над любой датой и над любым временем можно производить операции сложения и вычитания. Так же надо помнить, что в Excel есть функция (ВРЕМЯ()), которая преобразовывает три значения (часы,минуты,секунды) в специальную десятичную дробь, которая, по сути, является временем «чч:мм:сс», если изменить формат ячейки на «Время-13:30:55».
- Пусть в ячейке A1 у нас есть время «23:23:59«, тогда
- Пусть, клетках B1,C1,D1 у нас будет количество часов,минут,секунд, (целые числа) которое мы хотим добавить к нашему времени.
- Для получения результата, запишем в клетке E1 формулу: =A1+ВРЕМЯ(B1;C1;D1)
Как посчитать разницу в Excel
В программе Эксель очень удобно делать различного рода расчеты, как простые (суммирование, умножение, деление), так и с использованием формул. Я уже рассказывала, как рассчитать сумму в Эксель, и статью можете прочесть на сайте. Разбирали мы функции СУММЕСЛИ и СУММЕСЛИМН.
Сейчас давайте рассмотрим еще один простой вид расчета – вычитание. Покажу, как его применить к обычным числам, или к тем значениям, формат которых изменен на денежный, даты или время.
Обычные числа
Начнем с самого простого – использование программы в качестве калькулятора. Для этого выделите любую ячейку, поставьте в нее знак «=» (он вводится с клавиатуры), наберите первое число, ставьте «-» (он тоже на клавиатуре), набирайте второе и так все значения, которые необходимо отнять.
В конце нажмите «Enter» и расчет будет выполнен.
Если же у Вас на рабочем листе есть заполненные данные, которые требуется вычесть, тогда переходим на ячейку, в которой будет результат ( А4 ). Поставьте в нее знак «=» , а потом выделите сначала число от которого вычитаете ( А3 ), ставьте «-» , затем выделяем второе значение ( В3 ).
Нажимайте «Enter» и появится результат. В строке формул можно посмотреть, из каких ячеек берутся данные для расчета.
Если с вычитанием чисел понятно, тогда перейдем к датам. Они должны быть вписаны в блоки и для каждой нужно указать числовой формат «Дата» .
Давайте отнимем от нее определенное количество дней. Выберите любую ячейку ( Е3 ), впишите в нее «=» . Потом кликните по дате ( Е1 ), поставьте минус и впишите нужное число дней.
В результате формат ячейки изменится, и в ней отобразится та дата, которая меньше изначальной на то значение, которое Вы указали.
Для того чтобы отнять между собой две даты и узнать между ними разницу в днях, ставим в пустом блоке равно ( Е4 ), выделяем позднее значение ( Е1 ), потом раннее ( D1 ).
Нажимаем «Enter» и получаем результат. То есть, между датами 5 октября и 12 сентября 23 дня.
Также для расчета разницы дат можно использовать формулу РАЗНДАТ() . У нее в скобках указываются следующие аргументы: сначала раннее потом позднее значение и в чем хотим получить результат (день, месяц, год).
Подробнее про последний аргумент. Если хотите получить результат в днях, укажите «d» , месяцах – «m» , годах – «y» . Если нужны только дни, и не хотите учитывать года, тогда «yd» (например, разница 5 лет 23 дня, останется только 23), дни без учета месяцев – «md» , только месяцы – «ym» .
Отнимем для примера 12.09.2016 и 05.10.2017. Поставьте в ячейку «=» и наберите «РАЗНДАТ» со скобками. Внутри них ставим курсив и выделяем сначала раннюю ( D1 ), затем позднюю ( Е1 ) дату. В конце укажите нужную букву. В примере считаем разницу в полных месяцах. Между аргументами ставьте «;» без пробелов. Получилась формула:
Нажмем «Enter» и получим результат. Между указанными значениями у нас ровно 12 месяцев.
Воспользоваться формулой можно и иначе: сразу в строке формул укажите нужные значения, взяв их в двойные кавычки.
Время
Для расчета разницы во времени, нужно чтобы для них был выбран числовой формат «Время» . У меня это ячейки G1 и H1 . Та, в которой будет расчет ( Н3 ), может быть и общего формата. Ставим в нее равно и вычитаем от большего значения времени меньшее.
После нажатия «Enter» отобразится разница и у ячейки изменится формат.
Если нужна разница в минутах, тогда ее можно рассчитать. Получившееся количество часов (10) умножьте на 60 и прибавьте минуты (23).
Получилось 623 минуты.
Денежные единицы
Чтобы отнять между собой значения в ячейках, где выбран денежный формат, используется самый простой способ. Выберите пустой блок, поставьте в нем знак равенства и отнимете значения, выделяя их по очереди.
После нажатия уже знакомой кнопки, получим значение, после которого также будет стоять символ выбранной денежной единицы.
Вот такими способами получится посчитать разницу между числами, датами, временем, или денежными единицами в Эксель.
Skip to content
На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные совпадения либо уникальные значения. Ладно, обратный отсчет начался!
Мы все время от времени делаем сравнение данных в Excel. Microsoft Excel предлагает ряд опций для сравнения и сопоставления данных, но большинство из них ориентированы на поиск в одной колонке. Встроенный инструмент удаления дубликатов, доступный в Excel 2019-2010, не может справиться с этой задачей, поскольку он не умеет сравнивать данные между двумя столбиками. Кроме того, он может только удалять дубликаты. Других возможностей — таких как выделение или раскраска, увы, нет :-(.
В этом руководстве мы рассмотрим несколько методов сравнения двух столбцов в Excel и нахождения совпадений и различий между ними.
- Как сравнить 2 столбца построчно?
- Построчное сравнение нескольких столбцов.
- Ищем совпадения и различия в двух столбцах.
- Как извлечь данные для совпадающих значений.
- Выделяем цветом совпадения и различия
- Как выделить цветом уникальные значения и дубликаты в нескольких столбцах сразу?
- Как сопоставить два значения в разных столбцах?
- Быстрый способ сравнить два столбца или списка без формул.
Как сравнить 2 столбца в Excel по строкам.
Когда вы выполняете анализ данных в Excel, одной из наиболее частых задач является сравнение данных нескольких колонок в каждой отдельной их строке. Эту задачу можно выполнить с помощью функции ЕСЛИ , как показано в следующих примерах.
1. Проверяем совпадения или различия в одной строке.
Чтобы выполнить такое построчное сравнение, используйте популярную функцию ЕСЛИ, которая сравнивает первые две ячейки каждого из них. Введите её в какой-либо другой столбик той же строки, а затем скопируйте ее вниз, перетащив маркер заполнения (маленький квадрат в правом нижнем углу). При этом курсор изменится на знак плюса:
Чтобы найти в соответствующей строке позиции с одинаковым содержимым, A2 и B2 в этом примере, запишите:
=ЕСЛИ(A2=B2; “Совпадает”; “”)
Чтобы найти позиции в одной строке с разным содержимым, просто замените “=” знаком неравенства:
=ЕСЛИ(A2<>B2; “НЕ совпадает”;””)
И, конечно же, ничто не мешает найти совпадения и различия с помощью одной формулы:
=ЕСЛИ(A2=B2; “Совпадает”; “НЕ совпадает”)
Результат может выглядеть примерно так:
Как видите, одинаково хорошо обрабатываются числа, даты, время и текст.
2. Сравниваем построчно с учетом регистра.
Как вы, наверное, заметили, формулы из предыдущего примера игнорируют регистр при сравнении текстовых значений, как в строке 10 на скриншоте выше. Если вы хотите найти совпадения с учетом регистра, используйте функцию СОВПАД (EXACT в английской версии):
=ЕСЛИ(СОВПАД(A2; B2); “Одинаковый”; “”)
Чтобы найти различия с учетом регистра в одной строке, введите соответствующий текст («Уникальный» например) в третий аргумент функции ЕСЛИ:
=ЕСЛИ(СОВПАД(A2; B2); “Одинаковый”; “Уникальный”)
Сравните несколько столбцов построчно
Мы можем ставить перед собой следующие цели:
- Найти строки с одинаковыми значениями во всех из них.
- Найти строки с одинаковыми значениями в любых двух.
Пример 1. Найдите полное совпадение по одной строке.
Если в вашей таблице три или более колонки, и вы хотите найти строки с одинаковыми записями во всех из них, функция ЕСЛИ с оператором И подойдет для вас:
=ЕСЛИ(И(A2=B2; A2=C2); “Полное совпадение”; “”)
Если в вашей таблице очень много колонок, более элегантным решением будет использование функции СЧЁТЕСЛИ :
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2; $A2)=3; “Полное совпадение”; “”)
где 3 – количество сравниваемых колонок.
Или можно использовать —
=ЕСЛИ(СЧЁТЕСЛИ($A2:$C2; $A2)=СЧЁТЗ(A2:C2); “Полное совпадение”; “”)
Пример 2. Найдите хотя бы 2 совпадения в данных.
Если вы ищете способ сравнить данные на предмет наличия любых двух или более ячеек с одинаковыми значениями в одной строке, используйте функцию ЕСЛИ с оператором ИЛИ:
=ЕСЛИ(ИЛИ(A2=B2; B2=C2; A2=C2); “Найдены одинаковые”; “”)
Если есть много данных для сравнения, ваша конструкция с оператором ИЛИ может стать слишком громоздкой. В этом случае лучшим решением было бы добавить несколько функций СЧЁТЕСЛИ. Первый СЧЁТЕСЛИ подсчитывает, сколько раз текущее значение из первой колонки встречается во всех данных, находящихся правее него, второй СЧЁТЕСЛИ определяет то же самое для значения из второй колонки, и так далее. Если счетчик равен 0, возвращается надпись «Все уникальные», в противном случае – «Найдены одинаковые». Например:
=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0;”Все уникальные”;”Найдены одинаковые”)
Могу предложить также более компактный вариант выявления совпадений — формула массива:
{=ЕСЛИ(СУММ(СЧЁТЕСЛИ(A2:D2;A2:D2))>4;”Совпадения”;””)}
или
{=ЕСЛИ(СУММ(СЧЁТЕСЛИ(A2:D2;A2:D2))>СЧЁТЗ(A2:D2);”Совпадения”;””)}
Попробуйте — получите тот же результат. Также не забудьте нажать Ctrl + Shift + Enter, чтобы ввести всё правильно.
Как сравнить два столбца в Excel на совпадения и различия?
Предположим, у вас есть 2 списка данных в Excel, и вы хотите найти все значения (числа, даты или текстовые записи), которые находятся в колонке A, но их нет в B. То есть, исходные данные из А мы сравниваем с В.
Для этого вы можете встроить функцию СЧЁТЕСЛИ($B:$B;$A2)=0 в логический тест ЕСЛИ и проверить, возвращает ли она ноль (совпадение не найдено) или любое другое число (найдено хотя бы 1 совпадение).
Например, следующая формула ЕСЛИ/СЧЁТЕСЛИ выполняет поиск значения из A2 по всему столбцу B. Если совпадений не найдено, возвращается «Нет совпадений в B», в противном случае – пустую строку:
=ЕСЛИ(СЧЁТЕСЛИ($B:$B; $A2)=0; “Нет совпадений в В”; “”)
Тот же результат может быть достигнут при использовании функции ЕСЛИ всесте с ЕОШИБКА и ПОИСКПОЗ:
=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ($A2;$B$2:$B$10;0));”Уникальное”; ” Найдено в B”)
Или, используя следующую формулу массива (не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно):
=ЕСЛИ(СУММ(–($B$2:$B$10=$A2))=0; “”;” Найдено в B”)
Если вы хотите, чтобы одно выражение определяло как дубликаты, так и уникальные значения, поместите текст совпадений в пустые двойные кавычки («») в любой из приведенных выше формул. Например:
=ЕСЛИ(СЧЁТЕСЛИ($B:$B; $A2)=0; “Уникальное”; “Дубликат”)
Думаю, вы понимаете, что точно таким же образом можно наоборот сравнивать В с А.
Как сравнить два списка в Excel и извлечь совпадающие данные?
Иногда вам может потребоваться не только сопоставить две колонки в двух разных таблицах, но и извлечь соответствующие записи из второй таблицы. В Microsoft Excel предусмотрена специальная функция для этих целей – функция ВПР.
Кроме того, в отдельной статье мы подробно рассмотрели 4 способа, как сравнить таблицы при помощи формулы ВПР.
В качестве альтернативы вы можете использовать более мощную и универсальную комбинацию ИНДЕКС и ПОИСКПОЗ.
Например, следующее выражение сравнивает названия продуктов в колонках D и A, и если совпадение найдено, соответствующая цифра продаж извлекается из B. Если совпадения не найдено, возвращается ошибка #Н/Д.
=ИНДЕКС($B$2:$B$6;ПОИСКПОЗ($D2;$A$2:$A$6;0))
Сообщение об ошибке в таблице выглядит не слишком красиво. Поэтому обработаем это выражение при помощи ЕОШИБКА:
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$6;ПОИСКПОЗ($D2;$A$2:$A$6;0));””)
Теперь мы видим либо число, либо пустое значение. Никаких ошибок.
Как выделить совпадения и различия в 2 столбцах.
Когда вы сравниваете наборы данных в Excel, вы можете захотеть «визуализировать» элементы, которые присутствуют в одном, но отсутствуют в другом. Вы можете закрасить такие позиции любым цветом по вашему выбору с помощью формул. И вот несколько примеров с подробными инструкциями.
1. Выделите совпадения и различия построчно.
Чтобы сравнить два столбца в Excel и выделить те позиции в первом, которые имеют идентичные записи во втором по той же строке, выполните следующие действия:
- Выберите область, в которой вы хотите выделить.
- Щелкните Условное форматирование> Новое правило…> Используйте формулу.
- Создайте правило с простой формулой, например =$B2=$A2 (при условии, что строка 2 является первой строкой с данными, не включая заголовок таблицы). Пожалуйста, дважды проверьте, что вы используете относительную ссылку на строку (без знака $), как записано выше.
Чтобы выделить различия между колонками A и B, создайте правило с формулой =$B2<>$A2
Если вы новичок в условном форматировании Excel, смотрите пошаговые инструкции в статье Как закрасить строку или столбец по условию.
2. Выделите уникальные записи в каждом столбце.
Когда вы сравниваете два списка в Excel, вы можете выделить 3 типа элементов:
- Предметы только в первом списке (уникальные)
- Предметы только во втором списке (уникальные)
- Элементы, которые есть в обоих списках (дубликаты).
О выделении дубликатов — смотрите пример выше. А сейчас рассмотрим, как выделить неповторяющиеся элементы в каждом из списков.
Предположим, что ваш список 1 находится в колонке A (A2:A8), а список 2 – в колонке C (C2:C8). Вы создаете правила условного форматирования с помощью следующих формул:
Выделите уникальные значения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)=0
Выделите уникальные значения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)=0
И получите следующий результат:
3. Выделите дубликаты в 2 столбцах.
Если вы внимательно следовали предыдущему примеру, у вас не возникнет трудностей с настройкой СЧЁТЕСЛИ, чтобы она находила совпадения, а не различия. Все, что вам нужно сделать, это установить счетчик больше нуля:
Вновь используем условное форматирование при помощи формулы.
Выделите совпадения в списке 1 (столбик A): =СЧЁТЕСЛИ($A$2:$A$8;C$2)>0
Выделите совпадения в списке 2 (столбик C): =СЧЁТЕСЛИ($C$2:$C$8;$A2)>0
Выделите цветом различия и совпадения в нескольких столбцах
При сравнении значений в нескольких наборах данных построчно, самый быстрый способ выделить одинаковые — создать правило условного форматирования. А самый быстрый способ скрыть различия — воспользоваться инструментом «Выделить группу ячеек», как показано в следующих примерах.
1. Как выделить совпадения.
Чтобы выделить строки, которые имеют одинаковые значения по всей длине, создайте правило условного форматирования на основе одного из следующих выражений:
=И($A2=$B2; $A2=$C2)
или
=СЧЁТЕСЛИ($A2:$C2; $A2)=3
Где A2, B2 и C2 – самые верхние в вашем диапазоне, а 3 – количество колонок для сравнения.
Конечно, можно не ограничиваться сравнением только 3 колонок. Вы можете использовать аналогичные формулы для выделения строк с одинаковыми значениями в 4, 5, 6 или более столбиках.
И еще один способ выделения цветом повторяющихся значений в нескольких столбцах. Снова используем условное форматирование. Выделяем нужную область, затем на ленте в меню условного форматирования выбираем Правила выделения ячеек — Повторяющиеся значения. Определяем желаемое оформление, получаем картину подобную той, что вы видите ниже.
Кстати, на последнем этапе вы можете выбрать не повторяющиеся, а уникальные значения. Способ, конечно, незамысловатый, но, возможно, он вам будет полезен.
2. Как выделить различия.
Чтобы быстро выделить позиции с разными значениями в каждой отдельной строке, вы можете использовать функцию Excel «Выделить группу ячеек».
- Выберите диапазон ячеек, который вы хотите сравнить. В этом примере я выбрал диапазон от A2 до C10.
По умолчанию самая верхняя координата выбранного диапазона является активной ячейкой, и все значения в той же строке будут сравниваться с нею. Она при выделении области имеет белый цвет, а все остальные ячейки выбранного диапазона выделены серым. В этом примере активной является A2, поэтому столбец сравнения – A.
Чтобы изменить столбец сравнения, используйте клавишу TAB для перемещения по диапазону слева направо или клавишу Enter для перемещения сверху вниз. Если нужно перемещаться снизу вверх, то нажмите и удерживайте SHIFT, и вновь используйте ТАВ — будете двигаться не вниз, а вверх. Вы увидите, как ваше белое пятно перемещается, и соответственно изменяется активный столбец.
- На вкладке «Главная» нажмите «Найти и выделить» > « Выделить группу ячеек». Затем выберите «Отличия по строкам» и нажмите «ОК» .
- Позиции, значения которых отличаются от ячеек сравнения в каждой строке, выделяются. Если вы хотите закрасить выделенные ячейки каким-либо цветом, просто щелкните значок «Цвет заливки» на ленте и выберите нужный цвет.
Как сравнить два значения в отдельных столбцах.
Фактически, сравнение двух ячеек — частный случай сравнения двух колонок в Excel построчно, за исключением того, что вам не нужно копировать формулы.
Например, для сравнения ячеек A1 и C1 можно использовать:
Для совпадений: =ЕСЛИ(A1=C1; “Совпадает”; “”)
Для различий: =ЕСЛИ(A1<>C1; “Уникальные”; “”)
Чтобы узнать о некоторых других способах сравнения ячеек в Excel, см. Как сравнивать значения в ячейках Excel .
Для более эффективного анализа данных вам могут потребоваться более сложные формулы, и вы можете найти несколько хороших идей в следующих руководствах:
- Использование функции ЕСЛИ в Excel
- Функция ЕСЛИ: проверяем условия с текстом
Быстрый способ сравнения двух столбцов или списков без формул.
Теперь, когда вы знаете, что предлагает Excel для сравнения и сопоставления столбцов, позвольте мне продемонстрировать вам альтернативное решение, которое может сравнить 2 списка с разным количеством столбцов на предмет дубликатов (совпадений) и уникальных значений (различий).
Надстройка Ultimate Suite умеет искать идентичные и уникальные записи в одной таблице, а также сравнивать две таблицы, находящиеся на одном листе или в двух разных листах или даже в разных книгах.
В рамках этой статьи мы сосредоточимся на функции под названием «Сравнить таблицы (Compare Tables)» , которая специально разработана для сравнения двух списков по любым указанным вами столбцам. Сравнение двух наборов данных по нескольким столбцам является реальной проблемой как для формул Excel, так и для условного форматирования, но этот инструмент легко справляется с этим.
Для начала рассмотрим самый простой случай – сравним два столбца на совпадения и различия.
Предположим, у нас имеется два списка товаров. Нужно сравнить их между собой, как ранее мы делали при помощи формул.
Запускаем инструмент сравнения таблиц и выбираем первый столбец. При необходимости активируем создание резервной копии листа.
На втором шаге выбираем второй столбец для сравнения.
На третьем шаге нужно указать, что именно мы ищем – дубликаты либо уникальные значения.
Далее указываем столбцы для сравнения. Поскольку столбцов всего два, то здесь все достаточно просто:
На пятом шаге выберите, что нужно сделать с найденными значениями – удалить, выбрать, закрасить цветом, скопировать либо переместить. Можно добавить столбец статуса подобно тому, как мы это делали ранее при помощи функции ЕСЛИ. С использованием формул вы кроме того сможете разве что закрасить ячейки. Здесь же диапазон возможностей гораздо шире. Но мы выберем простой и наглядный вариант – заливку ячеек цветом.
Ячейки списка 1, дубликаты которых имеются в списке 2, будут закрашены цветом.
А теперь повторим все описанные выше шаги, только будем сравнивать список 2 с первым. И вот что мы в итоге получим:
Не закрашенные цветом ячейки содержат уникальные значения. Красиво и наглядно.
А теперь давайте попробуем сравнить сразу несколько столбцов. Допустим, у нас есть два экземпляра отчёта о продажах. Они расположены на разных листах нашей книги Excel. Список товаров совершенно одинаков, а вот сами цифры продаж отличаются кое-где.
Действуя совершенно аналогичным образом, как это было описано выше, выбираем эти две таблицы для сравнения. На третьем шаге выбираем поиск уникальных значений, чтобы можно было выбрать и выделить именно несовпадения в данных.
Устанавливаем соответствие столбцов, как это показано на рисунке ниже.
Для наглядности вновь выбираем заливку цветом для несовпадающих значений.
И вот результат. Несовпадающие строки закрашены цветом.
Если вы хотите попробовать этот инструмент, вы можете загрузить его как часть надстройки Ultimate Suite for Excel.
Вот какими способами вы можете сравнить столбцы в Excel на наличие дубликатов и уникальных значений.
Если у вас есть вопросы или что-то осталось неясным, напишите мне комментарий, и я с радостью уточню это подробнее. Спасибо за чтение!
Функция ЕСЛИОШИБКА – примеры формул – В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального… 
9 способов сравнить две таблицы в Excel и найти разницу – В этом руководстве вы познакомитесь с различными методами сравнения таблиц Excel и определения различий между ними. Узнайте, как просматривать две таблицы рядом, как использовать формулы для создания отчета о различиях, выделить… 
Сравнение ячеек в Excel – Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или без учета регистра. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству… 
Вычисление номера столбца для извлечения данных в ВПР – Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.… 
Как проверить правильность ввода данных в Excel? – Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность ввода в соответствии с имеющимся списком допустимых значений. В случае правильного ввода в отдельном столбце ставить… 
Функция ЕСЛИ: проверяем условия с текстом – Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. СодержаниеПроверяем условие для полного совпадения текста.ЕСЛИ + СОВПАДИспользование функции ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСКЕСЛИ… 
Визуализация данных при помощи функции ЕСЛИ – Функцию ЕСЛИ можно использовать для вставки в таблицу символов, которые наглядно показывают происходящие с данными изменения. К примеру, мы хотим показать в отдельной колонке таблицы, происходит рост или снижение продаж.… 
3 примера, как функция ЕСЛИ работает с датами. – На первый взгляд может показаться, что функцию ЕСЛИ для работы с датами можно применять так же, как для числовых и текстовых значений, которые мы только что обсудили. К сожалению, это…
Содержание
- Как посчитать разницу в Excel
- Обычные числа
- Время
- Денежные единицы
- Формула разницы в excel
- Cложение, вычитание, умножение, деление в Excel.
- Вычисление разницы во времени
- Представление результата в стандартном формате времени
- Пример таблицы 1 — Отображения результата в стандартный формат времени
- Как сделать формулу разности в excel?
- Вычисление разницы
- Способ 1: вычитание чисел
- Способ 2: денежный формат
- Способ 3: даты
- Способ 4: время
- Помогла ли вам эта статья?
- Формулы в Excel для чайников
- Как в формуле Excel обозначить постоянную ячейку
- Как составить таблицу в Excel с формулами
- Сложение и вычитание
- Умножение
- Деление
- Сложные формулы
Как посчитать разницу в Excel
В программе Эксель очень удобно делать различного рода расчеты, как простые (суммирование, умножение, деление), так и с использованием формул. Я уже рассказывала, как рассчитать сумму в Эксель, и статью можете прочесть на сайте. Разбирали мы функции СУММЕСЛИ и СУММЕСЛИМН.
Сейчас давайте рассмотрим еще один простой вид расчета – вычитание. Покажу, как его применить к обычным числам, или к тем значениям, формат которых изменен на денежный, даты или время.
Обычные числа
Начнем с самого простого – использование программы в качестве калькулятора. Для этого выделите любую ячейку, поставьте в нее знак «=» (он вводится с клавиатуры), наберите первое число, ставьте «-» (он тоже на клавиатуре), набирайте второе и так все значения, которые необходимо отнять.
В конце нажмите «Enter» и расчет будет выполнен.
Если же у Вас на рабочем листе есть заполненные данные, которые требуется вычесть, тогда переходим на ячейку, в которой будет результат ( А4 ). Поставьте в нее знак «=» , а потом выделите сначала число от которого вычитаете ( А3 ), ставьте «-» , затем выделяем второе значение ( В3 ).
Нажимайте «Enter» и появится результат. В строке формул можно посмотреть, из каких ячеек берутся данные для расчета.
Если с вычитанием чисел понятно, тогда перейдем к датам. Они должны быть вписаны в блоки и для каждой нужно указать числовой формат «Дата» .
Давайте отнимем от нее определенное количество дней. Выберите любую ячейку ( Е3 ), впишите в нее «=» . Потом кликните по дате ( Е1 ), поставьте минус и впишите нужное число дней.
В результате формат ячейки изменится, и в ней отобразится та дата, которая меньше изначальной на то значение, которое Вы указали.
Для того чтобы отнять между собой две даты и узнать между ними разницу в днях, ставим в пустом блоке равно ( Е4 ), выделяем позднее значение ( Е1 ), потом раннее ( D1 ).
Нажимаем «Enter» и получаем результат. То есть, между датами 5 октября и 12 сентября 23 дня.
Также для расчета разницы дат можно использовать формулу РАЗНДАТ() . У нее в скобках указываются следующие аргументы: сначала раннее потом позднее значение и в чем хотим получить результат (день, месяц, год).
Подробнее про последний аргумент. Если хотите получить результат в днях, укажите «d» , месяцах – «m» , годах – «y» . Если нужны только дни, и не хотите учитывать года, тогда «yd» (например, разница 5 лет 23 дня, останется только 23), дни без учета месяцев – «md» , только месяцы – «ym» .
Отнимем для примера 12.09.2016 и 05.10.2017. Поставьте в ячейку «=» и наберите «РАЗНДАТ» со скобками. Внутри них ставим курсив и выделяем сначала раннюю ( D1 ), затем позднюю ( Е1 ) дату. В конце укажите нужную букву. В примере считаем разницу в полных месяцах. Между аргументами ставьте «;» без пробелов. Получилась формула:
Нажмем «Enter» и получим результат. Между указанными значениями у нас ровно 12 месяцев.
Воспользоваться формулой можно и иначе: сразу в строке формул укажите нужные значения, взяв их в двойные кавычки.
Время
Для расчета разницы во времени, нужно чтобы для них был выбран числовой формат «Время» . У меня это ячейки G1 и H1 . Та, в которой будет расчет ( Н3 ), может быть и общего формата. Ставим в нее равно и вычитаем от большего значения времени меньшее.
После нажатия «Enter» отобразится разница и у ячейки изменится формат.
Если нужна разница в минутах, тогда ее можно рассчитать. Получившееся количество часов (10) умножьте на 60 и прибавьте минуты (23).
Получилось 623 минуты.
Денежные единицы
Чтобы отнять между собой значения в ячейках, где выбран денежный формат, используется самый простой способ. Выберите пустой блок, поставьте в нем знак равенства и отнимете значения, выделяя их по очереди.
После нажатия уже знакомой кнопки, получим значение, после которого также будет стоять символ выбранной денежной единицы.
Вот такими способами получится посчитать разницу между числами, датами, временем, или денежными единицами в Эксель.
Источник
Формула разницы в excel
Cложение, вычитание, умножение, деление в Excel.
Вычисление разницы во времени
смотрите в статьеВсе то же раза левой мышкой написать название листа, «Специальная вставка». Подробнее Активная ячейка будет будем рассматривать более ABS, возвращающая абсолютное: я не совсем № 11 () нулей формулу чтобы потребность странички, а там=ЦЕЛОЕ((B2-A2)*24) «ч:мм:сс» с помощью7 можно предпринять, чтобы «Функция «СУММЕСЛИ» в самое, но знак по ячейке с поставить восклицательный знак об этом способе
обведена жирным прямоугольником. сложные формулы. значение (модуль числа). то имел в Файл — Параметры_Boroda_ на заказ высчитывалась пишут. вы не можетеОбщее количество часов между функции8
Представление результата в стандартном формате времени
представить результаты в Excel». Excel может деления (/) на формулой, то ячейки, и указать адрес посчитать данные таблицы,Теперь ставим знакВспомним, что каждая Так что если виду. нужно что — Дополнительно -
: И правда. Что-то с учетом остатка, отвечать 3 часа
двумя значениями времени
ТЕКСТ9 стандартный формат времени производить не только кнопке над клавишей входящие в формулу, ячейки (диапазона). Формула читайте в статье равно «=». В ячейка имеет свой
от содержимого ячейки бы в итоге Параметры для следующего я погорячился и проставлялись в на вопросы. аж (4)(4:55:00).A
(часы: минуты: секунды). простые математические действия, «Enter», тоже сначала будут обведены разными будет такая. =A6+Лист11!H7 «Суммирование в Excel». ячейке пульсирует курсор. адрес, который мы В отнимать содержимое
Пример таблицы 1 — Отображения результата в стандартный формат времени
выводилась точно такая листа — поставьтеЦитата ячейки заказ на
Источник
Как сделать формулу разности в excel?
Вычисление разности является одним из самых популярных действий в математике. Но данное вычисление применяется не только в науке. Мы его постоянно выполняем, даже не задумываясь, и в повседневной жизни. Например, для того, чтобы посчитать сдачу от покупки в магазине также применяется расчет нахождения разницы между суммой, которую дал продавцу покупатель, и стоимостью товара. Давайте посмотрим, как высчитать разницу в Excel при использовании различных форматов данных.
Вычисление разницы
Учитывая, что Эксель работает с различными форматами данных, при вычитании одного значения из другого применяются различные варианты формул. Но в целом их все можно свести к единому типу:
А теперь давайте рассмотрим, как производится вычитание значений различных форматов: числового, денежного, даты и времени.
Способ 1: вычитание чисел
Сразу давайте рассмотрим наиболее часто применимый вариант подсчета разности, а именно вычитание числовых значений. Для этих целей в Экселе можно применить обычную математическую формулу со знаком «-».
- Если вам нужно произвести обычное вычитание чисел, воспользовавшись Excel, как калькулятором, то установите в ячейку символ «=». Затем сразу после этого символа следует записать уменьшаемое число с клавиатуры, поставить символ «-», а потом записать вычитаемое. Если вычитаемых несколько, то нужно опять поставить символ «-» и записать требуемое число. Процедуру чередования математического знака и чисел следует проводить до тех пор, пока не будут введены все вычитаемые. Например, чтобы из числа 10 вычесть 5 и 3, нужно в элемент листа Excel записать следующую формулу:
После записи выражения, для выведения результата подсчета, следует кликнуть по клавише Enter.
Но значительно чаще процесс вычитания в Экселе применяется между числами, размещенными в ячейках. При этом алгоритм самого математического действия практически не меняется, только теперь вместо конкретных числовых выражений применяются ссылки на ячейки, где они расположены. Результат же выводится в отдельный элемент листа, где установлен символ «=».
Посмотрим, как рассчитать разницу между числами 59 и 26, расположенными соответственно в элементах листа с координатами A3 и С3.
- Выделяем пустой элемент книги, в который планируем выводить результат подсчета разности. Ставим в ней символ «=». После этого кликаем по ячейке A3. Ставим символ «-». Далее выполняем клик по элементу листа С3. В элементе листа для вывода результата должна появиться формула следующего вида:
Как и в предыдущем случае для вывода результата на экран щелкаем по клавише Enter.
Но на самом деле в некоторых случаях требуется произвести вычитание, в котором будут принимать участие, как непосредственно числовые значения, так и ссылки на ячейки, где они расположены. Поэтому вполне вероятно встретить и выражение, например, следующего вида:
Урок: Как вычесть число из числа в Экселе
Способ 2: денежный формат
Вычисление величин в денежном формате практически ничем не отличается от числового. Применяются те же приёмы, так как, по большому счету, данный формат является одним из вариантов числового. Разница состоит лишь в том, что в конце величин, принимающих участие в расчетах, установлен денежный символ конкретной валюты.
- Собственно можно провести операцию, как обычное вычитание чисел, и только потом отформатировать итоговый результат под денежный формат. Итак, производим вычисление. Например, вычтем из 15 число 3.
- После этого кликаем по элементу листа, который содержит результат. В меню выбираем значение «Формат ячеек…». Вместо вызова контекстного меню можно применить после выделения нажатие клавиш Ctrl+1.
- При любом из двух указанных вариантов производится запуск окна форматирования. Перемещаемся в раздел «Число». В группе «Числовые форматы» следует отметить вариант «Денежный». При этом в правой части интерфейса окна появятся специальные поля, в которых можно выбрать вид валюты и число десятичных знаков. Если у вас Windows в целом и Microsoft Office в частности локализованы под Россию, то по умолчанию должны стоять в графе «Обозначение» символ рубля, а в поле десятичных знаков число «2». В подавляющем большинстве случаев эти настройки изменять не нужно. Но, если вам все-таки нужно будет произвести расчет в долларах или без десятичных знаков, то требуется внести необходимые коррективы.
Вслед за тем, как все необходимые изменения сделаны, клацаем по «OK».
Существует ещё один вариант отформатировать полученный итог вычитания под денежный формат. Для этого нужно на ленте во вкладке «Главная» кликнуть по треугольнику, находящемуся справа от поля отображения действующего формата ячейки в группе инструментов «Число». Из открывшегося списка следует выбрать вариант «Денежный». Числовые значения будут преобразованы в денежные. Правда в этом случае отсутствует возможность выбора валюты и количества десятичных знаков. Будет применен вариант, который выставлен в системе по умолчанию, или настроен через окно форматирования, описанное нами выше.
Если же вы высчитываете разность между значениями, находящимися в ячейках, которые уже отформатированы под денежный формат, то форматировать элемент листа для вывода результата даже не обязательно. Он будет автоматически отформатирован под соответствующий формат после того, как будет введена формула со ссылками на элементы, содержащие уменьшаемое и вычитаемые числа, а также произведен щелчок по клавише Enter.
Урок: Как изменить формат ячейки в Экселе
Способ 3: даты
А вот вычисление разности дат имеет существенные нюансы, отличные от предыдущих вариантов.
- Если нам нужно вычесть определенное количество дней от даты, указанной в одном из элементов на листе, то прежде всего устанавливаем символ «=» в элемент, где будет отображен итоговый результат. После этого кликаем по элементу листа, где содержится дата. Его адрес отобразится в элементе вывода и в строке формул. Далее ставим символ «-» и вбиваем с клавиатуры численность дней, которую нужно отнять. Для того, чтобы совершить подсчет клацаем по Enter.
- Результат выводится в обозначенную нами ячейку. При этом её формат автоматически преобразуется в формат даты. Таким образом, мы получаем полноценно отображаемую дату.
Существует и обратная ситуация, когда требуется из одной даты вычесть другую и определить разность между ними в днях.
- Устанавливаем символ «=» в ячейку, где будет отображен результат. После этого клацаем по элементу листа, где содержится более поздняя дата. После того, как её адрес отобразился в формуле, ставим символ «-». Клацаем по ячейке, содержащей раннюю дату. Затем клацаем по Enter.
- Как видим, программа точно вычислила количество дней между указанными датами.
Также разность между датами можно вычислить при помощи функции РАЗНДАТ. Она хороша тем, что позволяет настроить с помощью дополнительного аргумента, в каких именно единицах измерения будет выводиться разница: месяцы, дни и т.д. Недостаток данного способа заключается в том, что работа с функциями все-таки сложнее, чем с обычными формулами. К тому же, оператор РАЗНДАТ отсутствует в списке Мастера функций, а поэтому его придется вводить вручную, применив следующий синтаксис:
«Начальная дата» — аргумент, представляющий собой раннюю дату или ссылку на неё, расположенную в элементе на листе.
«Конечная дата» — это аргумент в виде более поздней даты или ссылки на неё.
Самый интересный аргумент «Единица». С его помощью можно выбрать вариант, как именно будет отображаться результат. Его можно регулировать при помощи следующих значений:
- «d» — результат отображается в днях;
- «m» — в полных месяцах;
- «y» — в полных годах;
- «YD» — разность в днях (без учета годов);
- «MD» — разность в днях (без учета месяцев и годов);
- «YM» — разница в месяцах.
Итак, в нашем случае требуется вычислить разницу в днях между 27 мая и 14 марта 2017 года. Эти даты расположены в ячейках с координатами B4 и D4, соответственно. Устанавливаем курсор в любой пустой элемент листа, где хотим видеть итоги расчета, и записываем следующую формулу:
Жмем на Enter и получаем итоговый результат подсчета разности 74. Действительно, между этими датами лежит 74 дня.
Если же требуется произвести вычитание этих же дат, но не вписывая их в ячейки листа, то в этом случае применяем следующую формулу:
Опять жмем кнопку Enter. Как видим, результат закономерно тот же, только полученный немного другим способом.
Урок: Количество дней между датами в Экселе
Способ 4: время
Теперь мы подошли к изучению алгоритма процедуры вычитания времени в Экселе. Основной принцип при этом остается тот же, что и при вычитании дат. Нужно из более позднего времени отнять раннее.
- Итак, перед нами стоит задача узнать, сколько минут прошло с 15:13 по 22:55. Записываем эти значения времени в отдельные ячейки на листе. Что интересно, после ввода данных элементы листа будут автоматически отформатированы под содержимое, если они до этого не форматировались. В обратном случае их придется отформатировать под дату вручную. В ту ячейку, в которой будет выводиться итог вычитания, ставим символ «=». Затем клацаем по элементу, содержащему более позднее время (22:55). После того, как адрес отобразился в формуле, вводим символ «-». Теперь клацаем по элементу на листе, в котором расположилось более раннее время (15:13). В нашем случае получилась формула вида:
Для проведения подсчета клацаем по Enter.
Но, как видим, результат отобразился немного не в том виде, в котором мы того желали. Нам нужна была разность только в минутах, а отобразилось 7 часов 42 минуты.
Для того, чтобы получить минуты, нам следует предыдущий результат умножить на коэффициент 1440. Этот коэффициент получается путем умножения количества минут в часе (60) и часов в сутках (24).
Можно поступить и по-другому. Выделяем указанный элемент листа и производим нажатие клавиш Ctrl+1. Запускается окно форматирования, с которым мы уже имели дело ранее. Перемещаемся во вкладку «Число» и в списке числовых форматов выбираем вариант «Общий». Клацаем по «OK».
Урок: Как перевести часы в минуты в Экселе
Как видим, нюансы подсчета разности в Excel зависят от того, с данными какого формата пользователь работает. Но, тем не менее, общий принцип подхода к данному математическому действию остается неизменным. Нужно из одного числа вычесть другое. Это удается достичь при помощи математических формул, которые применяются с учетом специального синтаксиса Excel, а также при помощи встроенных функций.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.
Конструкция формулы включает в себя: константы, операторы, ссылки, функции, имена диапазонов, круглые скобки содержащие аргументы и другие формулы. На примере разберем практическое применение формул для начинающих пользователей.
Формулы в Excel для чайников
Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.
В Excel применяются стандартные математические операторы:
| Оператор | Операция | Пример |
| + (плюс) | Сложение | =В4+7 |
| — (минус) | Вычитание | =А9-100 |
| * (звездочка) | Умножение | =А3*2 |
| / (наклонная черта) | Деление | =А7/А8 |
| ^ (циркумфлекс) | Степень | =6^2 |
| = (знак равенства) | Равно | |
| Меньше | ||
| > | Больше | |
| Меньше или равно | ||
| >= | Больше или равно | |
| Не равно |
Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.
Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.
Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.
При изменении значений в ячейках формула автоматически пересчитывает результат.
Ссылки можно комбинировать в рамках одной формулы с простыми числами.
Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.
В нашем примере:
- Поставили курсор в ячейку В3 и ввели =.
- Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
- Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.
Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:
Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках.
Как в формуле Excel обозначить постоянную ячейку
Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.
Все ссылки на ячейки программа считает относительными, если пользователем не задано другое условие. С помощью относительных ссылок можно размножить одну и ту же формулу на несколько строк или столбцов.
- Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
- Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
- Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.
Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.
Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.
Ссылки в ячейке соотнесены со строкой.
Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).
Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.
- Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
- Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
- После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.
Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:
- Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
- Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
- Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.
При создании формул используются следующие форматы абсолютных ссылок:
- $В$2 – при копировании остаются постоянными столбец и строка;
- B$2 – при копировании неизменна строка;
- $B2 – столбец не изменяется.
Как составить таблицу в Excel с формулами
Чтобы сэкономить время при введении однотипных формул в ячейки таблицы, применяются маркеры автозаполнения. Если нужно закрепить ссылку, делаем ее абсолютной. Для изменения значений при копировании относительной ссылки.
Простейшие формулы заполнения таблиц в Excel:
- Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
- Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
- По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
- Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.
Чтобы проверить правильность вставленной формулы, дважды щелкните по ячейке с результатом.
Создавая математические формулы в Excel мы указываем какие действия нужно сделать и где должен отображаться результат. С помощью использования формул в Excel можно производить различные расчёты такие как сложение, вычитание, умножение, деление, а также сложные комбинированные расчёты. Составление формул в Excel производится вручную и состоит из адресов конкретных ячеек или из ряда ячеек или столбца ячеек.
Имеют некоторые секреты эксель формулы
Записывать формулы в Excel можно прямо в ячейке или в строке формул, а также можно в Excel записать формулу прямо в ячейке с помощью функций. С помощью ввода формул в Excel также высчитывают даты, время и проценты. Обозначаются в Excel столбцы буквами такими как A, B. C и так далее, а нумерация строк в excel произведена цифрами 1, 2, 3 и так далее. При пересечении столбца и строки получаете адрес ячейки в Excel который обозначается буквой столбца и цифрой строки например самая первая ячейка будет обозначаться A1.
Чтобы работать в эксель нужно знать названия ячеек
Любые формулы в экселе для расчета начинается со знака равенства =. Количество символов в ячейке Excel формула может содержать не более 1024. Делая какое-нибудь вычисление в excel в итоговой ячейке отобразится результат и если навести на эту ячейку с результатом курсор и нажать левую кнопку мыши то вы увидите в строке формул полученную формулу по которой производился расчёт в этой ячейке.
Сложение и вычитание
Если вам нужно в Excel сложить ячейки то нужно навести курсор на пустую ячейку в которой должен отображаться результат и нажать левую кнопку мыши. Затем на клавиатуре нажимаете клавишу =. Потом наводите курсор на первую ячейку которую будем суммировать с другими, нажимаем левую кнопку мыши. На клавиатуре нажимаете клавишу +. Наводите курсор на вторую ячейку которую будете суммировать с другими, нажимаете левую кнопку мыши. Если нужно будет ещё в Excel сложить числа из каких-нибудь ячеек то поочерёдно добавьте их в формулу нажимая на +, а затем на ячейку которую нужно прибавить. Когда будут зафиксированы все ячейки в формуле Excel нужно нажать на клавиатуре клавишу Enter и сразу в ячейке отобразится результат.
Очень легко в экселе сделать формулу сложения
Точно таким же способом создаётся формула в Excel для вычитания чисел. Только чтобы в Excel сделать вычитание нужно на клавиатуре вместо + нажимать минус -. При этом методе не важно в каком столбце и в какой строке находятся суммируемые и вычитаемые ячейки.
Пример как посчитать сумму в экселе в столбце
Если вам нужно будет посчитать в экселе сумму столбца или строки то нужно навести курсор на первую ячейку, нажать левую кнопку на мышке и не отпуская кнопки довести курсор до последней ячейки. Затем отпускаете кнопку мышки и нажимаете кнопку автосуммирование которая находится на панели инструментов Excel.
Умножение
Формула умножения в Excel составляется следующим способом. Например вам нужно перемножить в Excel ячейки A1 и A2, а результат чтобы отображался в ячейке A3.
Легко в Excel сделать формулу умножения
Сначала вам нужно нужно навести курсор на ячейку A3 и нажать левую кнопку мыши. На клавиатуре нажимаете клавишу =. Наводите курсор на ячейку A1 и нажимаете левую кнопку мыши. Затем на клавиатуре нажимаете знак умножения в Excel * который находится в левой части клавиатуры или на цифре 8. Чтобы отобразить знак умножения в экселе * находящийся на цифре 8 нужно сначала нажать на клавиатуре кнопку Shift и удерживая её нажать на * находящуюся на цифре 8. Потом наводите курсор на ячейку A2 и нажимаете левую кнопку мыши. В завершении нажимаете на клавиатуре клавишу Enter и сразу отобразится итог.
Деление
Формула деления в Excel составляется следующим образом. Например вам нужно разделить в Excel ячейку A1 на ячейку A2 и чтобы результат отображался в ячейке A3.
Легко в Excel ввести формулу деления
Сначала вам нужно нужно навести курсор на ячейку A3 и нажать левую кнопку мыши. На клавиатуре нажимаете клавишу =. Наводите курсор на ячейку A1 и нажимаете левую кнопку мыши. Затем на клавиатуре нажимаете знак деления в экселе / который находится в левой части клавиатуры или на клавише с вопросительным знаком. Чтобы поставить знак деления в Excel который находится на клавише с вопросительным знаком нужно сначала клавиатуру перевести на английскую раскладку и только потом нажать эту клавишу. Потом наводите курсор на ячейку A2 и нажимаете левую кнопку мыши. В завершении нажимаете на клавиатуре клавишу Enter и получаете результат деления.
Сложные формулы
Писать сложные формулы в Excel нужно точно такими же как и в математике. Например у вас заполнены ячейки данными и нужно чтобы в ячейке A4 отобразился результат исходя из формулы =A1+A2/(A3-A2)*A1.
Можно как писать формулы в Excel или вставлять уже готовые
Первым делом копируете эту формулу в буфер обмена чтобы потом вставить формулу в эксель. Наводите курсор на ячейку A4 и нажимаете левую кнопку мыши. Наводите курсор на строку формул в Excel и нажимаете левую кнопку мыши. Затем нужно в эту строку в Excel вставить формулу из буфера обмена, а для этого нажмите на клавиатуре клавишу Ctrl и удерживая её нажмите клавишу V. Формула пропишется в строке формул, а нажав на клавишу Enter сразу отобразится результат вычисления по этой формуле.
Чтобы удалить формулу из ячейки в excel достаточно навести курсор на ячейку, нажать левую кнопку мыши и удалить формулу в строке формул.
Источник
The following step-by-step example shows how to calculate the percentage difference between two columns in a pivot table in Excel.
Step 1: Enter the Data
First, let’s enter the following sales data for three different stores:
Step 2: Create the Pivot Table
Next, let’s create the following pivot table to summarize the total sales by store and by year:
Step 3: Calculate Percentage Difference Between Two Columns in the Pivot Table
Suppose we would like to create a new column in the pivot table that displays the percentage difference between the Sum of 2021 and Sum of 2022 columns.
To do so, we need to add a calculated field to the pivot table by clicking on any value in the pivot table, then clicking the PivotTable Analyze tab, then clicking Fields, Items & Sets, then Calculated Field:
In the new window that appears, type “Percentage Difference” in the Name field, then type the following in the Formula field:
= ('2022' - '2021') / '2021'
Then click Add, then click OK.
This calculated field will automatically be added to the pivot table:
This new field displays the percentage difference between the 2022 and 2021 sales for each store.
For example:
- There was a 16.67% increase in sales between 2021 and 2022 for store A.
- There was a 15.44% increase in sales between 2021 and 2022 for store B.
- There was a 24.62% increase in sales between 2021 and 2022 for store C.
Feel free to highlight the values in the new field and change their format to a percentage format:
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Sum Two Columns in a Pivot Table in Excel
How to Subtract Two Columns in a Pivot Table in Excel
How to Calculate Weighted Average in a Pivot Table in Excel
