Формулы в excel в таблицах: Обзор формул — Служба поддержки Майкрософт

Поиск отличий в двух списках

29250
06.11.2012
Скачать пример

Типовая задача, возникающая периодически перед каждым пользователем Excel — сравнить между собой два диапазона с данными и найти различия между ними. Способ решения, в данном случае, определяется типом исходных данных.

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE):

Число несовпадений можно посчитать формулой:

=СУММПРОИЗВ(—(A2:A20<>B2:B20))

или в английском варианте =SUMPRODUCT(—(A2:A20<>B2:B20))

Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) — Отличия по строкам (Row differences). В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

• залить цветом или как-то еще визуально отформатировать
• очистить клавишей Delete
• заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
• удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
• и т. д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.

Цветовое выделение, однако, не всегда удобно, особенно для больших таблиц. Также, если внутри самих списков элементы могут повторяться, то этот способ не подойдет.

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

Полученный в результате ноль и говорит об отличиях.

И, наконец, «высший пилотаж» — можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

Выглядит страшновато, но свою работу выполняет отлично 😉

Ссылки по теме

• Выделение дубликатов в списке цветом
• Сравнение двух диапазонов с помощью надстройки PLEX
• Запрет ввода повторяющихся значений

Хитрости работы с Excel – таблицы, функции, формулы, списки, рабочие книги, макросы

Ввод формул

Введенное в ячейку значение распознается как формула тогда, когда оно начинается со знака равенства «=», а также со знака плюс «+» или минус «—». Если в ячейку введена формула, то изображенное в ней значение будет представлять собой результат выполнения каких-либо действий над значениями других ячеек. Например, в ячейке с формулой может содержаться сумма чисел из других заранее заданных ячеек. При этом в формуле указываются не числа, содержащиеся в этих ячейках, а адреса ячеек.

Благодаря этому, если в дальнейшем изменится значение хотя бы одной из ячеек, входящих в формулу, формула будет автоматически пересчитана.

Причем изменять в ней ничего не надо. Именно формулы являются одной из тех «изюминок», ради которых и стоит использовать Excel. В отличие от обычных таблиц, в электронных таблицах вычисляемые значения не требуется считать и заполнять вручную, они определяются (вычисляются) и выводятся в ячейках автоматически. Простейшими действиями в формулах являются арифметические действия над числами, содержащимися в ячейках: сложение + вычитание умножение деление / возведение в степень Для того, чтобы, например, в какой-либо ячейке было показано число, равное сумме числа 100, и чисел, записанных в ячейки А2, В2, С2 и F3, необходимо ввести в нее формулу: = 100+A2+B2 + C2+F3. Порядок выполнения арифметических действий в формуле соответствует принятым в математике правилам: сначала умножение и деление, затем сложение и вычитание.

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

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

— щелкнуть мышью по ячейке, адрес которой необходимо ввести;

— клавишами перемещения перевести табличный курсор (он становится пунктирным) на ячейку, адрес которой необходимо ввести. Знак равенства и знаки арифметических действий в этом случае по-прежнему необходимо набирать клавиатурой. То есть, например, для того, чтобы в ячейку ввести формулу =А2 + В2, следует набрать знак =, щелкнуть по ячейке А2, набрать знак +, щелкнуть по ячейке В2, а затем завершить ввод, например, нажав Enter.


Такой способ уменьшает вероятность ошибки в формуле и делает ее ввод в ячейку более удобным. Если выбран переключатель автоматически, то пересчет по любой формуле выполняется сразу же после изменения данных в одной из ячеек, адреса которых входят в формулу. При выборе переключателя вручную пересчет по формулам выполняется, только если нажать F9, Ctrl+= или кнопку Вычислить (F9) на вкладке Вычисления. В результате одного из этих действий пересчет будет выполнен в формулах, содержащихся во всех таблицах всех открытых в данный момент книг.

Для того, чтобы произвести пересчет только в текущей таблице (расположенной на текущем листе), следует нажать Shift+F9 или кнопку Пересчет листа на вкладке Вычисления. — необходимости ручного пересчета будет напоминать надпись Вычислить в строке состояния, которая появится после внесения первого изменения и исчезнет после пересчета. Если при выборе переключателя вручную включить переключатель пересчет перед сохранением, то перед сохранением до 5 Готово Вычислить документа автоматически будет выполняться пересчет формул всей книги. Замена формул на вычисленные значения Если, перейдя в режим редактирования формулы, нажать F9, то формула, содержащаяся в ячейке, будет заменена на значение, вычисленное по ней.

Например, если в ячейке записана формула =А2+В2, а в ячейках А2 и В2 содержатся числа 2, то после выполнения описанного действия вместо формулы =А2+В2 в данную ячейку будет записано простое числовое значение 4, которое уже не будет зависеть от содержимого ячеек А2 и В2. Для того, чтобы произвести такую же замену сразу в нескольких ячейках, необходимо выделить эти ячейки (п. 6.1), выполнить копирование в буфер обмена и выполнить команду Специальная вставка… (Правка). Затем в группе Вставить появившегося диалогового окна следует выбрать переключатель значение и нажать кнопку ОК.

Пример. Ввод текстовых значений

Запустите Excel. На чистом листе в ячейку А1 введите слово Текст.

Убедитесь, что введенный текст выровнен по левой границе ячейки. В ячейку А2 введите Текст в ячейке А2 имеет большую длину. Убедитесь, что текст, введенный в ячейку А2, занял соседние ячейки строки 2.

Установите табличный курсор на ячейку В2 и введите в нее Перекрытие текста. Установите табличный курсор на ячейку А2. Убедитесь, что текст, введенный в ячейку А2, виден не полностью (он перекрыт значением ячейки В2), однако содержимое этой ячейки осталось прежним.

Об этом можно судить по значению, показанному в секции содержания панели формул.

Перейдите на ячейку A3, наберите слово Ввод, нажмите Alt+Enter, наберите в три, еще раз нажмите Alt+Enter, наберите отроки и нажмите Enter. Убедитесь, что текст в ячейке A3 размещен в три строки.

Пример. Ввод числовых значений

Перейдите на чистый лист.

В ячейки А1 введите положительное число 261. Убедитесь, что введенное число выровнено по правой границе ячейки.

В ячейку А2 и В2 введите отрицательные числа -266 и -267, соответственно. Убедитесь, что в обеих этих ячейках записано отрицательное число.

В ячейку A3 введите дробное число 15/9. Установите табличный курсор на ячейку A3 и убедитесь, что реально в эту ячейку записано десятичное число, соответствующее введенному дробному числу.

Выполните команду Параметры… (Сервис). В появившемся диалоговом окне на вкладке Правка включите переключатель Фиксированный десятичный формат при вводе.

Затем в поле десятичных разрядов введите число 2 и нажмите кнопку ОК.

В ячейки строки 4 введите следующие значения: 89934, 1258, 211 и 56. Убедитесь, что в ячейках введены десятичные числа с двумя знаками после десятичного разделителя, а именно: 899.34, 12.58, 2.11 и 0.56. Вновь вызовите диалоговое окно Параметры и выключите режим фиксированного десятичного разделителя.

Установите табличный курсор на ячейку А5 и нажмите комбинацию Ctrl+;. Убедитесь, что в ячейку введена текущая дата. Установите табличный курсор на ячейку А6 и нажмите комбинацию Ctrl+Shift+;.

Убедитесь, что в ячейку введено текущее время. В ячейки В5 и В6 еще раз введите текущую дату и текущее время, соответственно, но используйте при этом обычный набор клавиатурой.

В ячейку А7 введите значение 0099, а в ячейку В7 — значение ‘0099 (с апострофом). Убедитесь, в ячейке А7 введено числовое значение 99, причем выровнено оно по правому краю ячейки, а в ячейке В7 — текстовое значение 0099 и выровнено оно по левой границе.

При вводе значения ‘0099 редактор Excel может выдать сообщение о возможной ошибке. В данном случае в раскрывающемся меню сообщения вы должны выбрать Пропустить ошибку. Закройте книгу, не сохраняя ее.

Пример. Ввод формул

Откройте документ Первая книга, перейдите на лист Лист1. Введите в ячейки А2, В2, С2 и ЕЗ числа 31, 246, 55 и 42, соответственно.

В ячейку А4 с использованием клавиатуры введите формулу =100+A2+В2+С2+ЕЗ. Запустите программу Калькулятор и убедитесь, что результат вычисления по срормуле верный.

В ячейку А5 введите ту же формулу, что и в ячейку А4, используя при этом следующий способ: введите знак равенства и первое слагаемое =100+, щелкните мышкой по ячейке А2, введите знак сложения +, щелкните по ячейке В2, введите знак сложения +, щелкните по ячейке С2, введите знак сложения +, щелкните по ячейке ЕЗ, нажмите Enter.

Измените значение в ячейке В2. Убедитесь, что значение в ячейках А4 и А5 автоматически пересчитались. Выполните команду Параметры…

(Сервис), в появившемся диалоговом окне на вкладке Вычисления выберите переключатель вручную и нажмите кнопку ОК. Измените значение в ячейке В2. Убедитесь, что значения в ячейках А4 и А5 не изменились, а в строке состояния появилась надпись Вычислить.  Нажмите клавишу F9. Убедитесь, что значение в ячейках А4 и А5 пересчитались. Верните режим автоматического пересчета формул.


Установите табличный курсор на ячейку А4, нажмите последовательно F2 (произойдет вход в режим редактирования ячейки), F9, а затем Enter. Убедитесь, что в ячейке А4 формула заменена на вычисленное по ней значение. Сохраните документ Первая книга.

---

Newer news items:

• 13/12/2010 17:47 — Excel формулы
• 13/12/2010 17:44 — Функции Excel
• 13/12/2010 16:45 — Расчеты в excel
• 08/07/2010 06:47 — Техника ввода данных Microsoft Excel
• 08/06/2010 06:06 — Работа с формулами в Excel

Older news items:

• 23/11/2007 14:24 — Использование констант и функций в именах
• 21/11/2007 05:56 — Расчет множественных результатов
• 19/11/2007 10:27 — Создание функций при помощи библиотеки
• 17/11/2007 15:12 — Циклические ссылки
• 16/11/2007 22:48 — Исправление ошибок в расчетах

Next page >>

---

 

Как заблокировать ссылки на формулы ячеек в Excel при использовании таблиц данных

Быстрая навигация

Одной из самых мощных функций формул Excel является возможность создавать абсолютные ссылки, которые не перемещаются при перетаскивании, чтобы расширить формулы ячеек или скопируйте их в разные места электронной таблицы. Большинство пользователей Excel выясняют, как заблокировать эти ссылки, либо переключая параметры с помощью клавиши F4 , либо используя символ $ (знак доллара) перед ссылками на столбцы и/или строки.

Когда вы начинаете использовать таблицы данных в Excel, многие пользователи с разочарованием обнаруживают, что обычные методы закрепления ссылок на строки или столбцы не работают! Это может занять много времени, чтобы построить ваши электронные таблицы, когда они используют таблицы. К счастью, есть обходные пути! В этом кратком руководстве мы рассмотрим некоторые из них…

Почему мы используем таблицы данных в первую очередь?

Если вам интересно, почему вы вообще хотите использовать таблицы данных вместо просто красиво упорядоченных ячеек в Excel, вероятно, стоит быстро освежить в памяти…

Таблицы данных в Excel особенно полезны, потому что они  увеличиваются и изменяются , чтобы принимать новые данные при их добавлении в таблицу,  без необходимости обновлять формулы или ссылки . Это очень ценно, когда вы хотите скопировать и вставить данные в электронную таблицу из внешнего источника, чтобы поддерживать ее в актуальном состоянии. В таблице данных вы можете создавать вычисляемые столбцы, которые ссылаются на другие части таблицы данных. Когда добавляются новые данные, вычисляемые столбцы автоматически обновляются новой информацией и новыми строками!

Итоговые формулы, такие как СУММЕСЛИ() , СЧЁТЕСЛИ() , МАКС() , МИН() 9 0010 и AVERAGE() могут быть предназначены для ссылки всю таблицу или определенные столбцы, а затем автоматически изменять их диапазоны, когда таблицы увеличиваются или уменьшаются!

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

Как работают обычные ссылки на таблицы данных

Ссылки на столбцы таблицы данных

Когда вы работаете с таблицами данных в Excel, ссылки на ячейки выглядят немного иначе, чем обычно  A1 комбинация букв и цифр для столбцов и строк. Это связано с тем, что каждая строка таблицы действует так, как будто она находится в своей собственной электронной таблице с 1 строкой. Это означает, что ссылки должны относиться только к имени таблицы и столбцу. Строка считается самой собой.

Давайте посмотрим на простую таблицу:
Если бы мы хотели сложить все столбцы Продажи в таблице данных, формула выглядела бы так:

 =  СУММА  (  Таблица1[Продажи]  ) 

Выход для этой формулы:

  3167  

Обратите внимание, что он не запрашивает начальную или конечную строку. Он просто запрашивает имя столбца и таблицы.

Теперь добавим данные в таблицу:
Формула для суммирования столбца Продажи осталась прежней:

 =  СУММ  (  Таблица1[Продажи]  ) 

Однако теперь вывод для этой формулы обновился автоматически:

  6450  

Без каких-либо действий вычисления учитывают дополнительные данные! Нет необходимости изменять ссылки в формуле SUM() !

Пример таблицы данных с несколькими ссылками

Давайте покажем еще один пример формулы с использованием той же таблицы:
Вместо того, чтобы создавать формулу вне таблицы, давайте добавим вычисляемый столбец для подсчета суммы для каждого региона. Назовите новый столбец справа  9.0006 Регион Итого . В первую ячейку столбца введите следующую формулу:

 =  СУММЕСЛИМН  (  [Продажи]  ,  [Регион]  ,  [@Регион]  ) 

Должно получиться так: 900 03

Обратите внимание, что окончательный термин – критерий СУММЕСЛИМН () – использует @ (символ «at»). Это обозначение означает, что вы имеете в виду значение столбца Region в текущей строке . Просто нажмите ВВЕДИТЕ , чтобы заполнить этой формулой весь столбец, и вы увидите результат этой ссылки в заполненной таблице.

Вычисление СУММЕСЛИ () суммирует все Продажи для каждого Региона , давая соответствующую сумму для каждой строки из региона Север (а также для каждого из остальных).

Теперь, когда вы видите, какими мощными могут быть таблицы данных Excel, вы, вероятно, захотите их использовать… Чтобы быстро и надежно создавать сложные формулы с использованием таблиц данных, вам необходимо иметь возможность блокировать ссылки, поэтому мы узнать дальше… Нажмите вперед!

Как построить блокирующие ссылки

Обычно легко создавать столбцы и строки, блокируя ссылки в формулах с помощью F4 или добавляя знаки доллара в строку и столбец ссылки… Это позволяет перетаскивать формулы вниз на несколько строк или по нескольким столбцам, чтобы быстро построить рабочую электронную таблицу.

Пример обычных относительных и абсолютных (заблокированных) ссылок на ячейки

Возьмем простой пример:

Здесь мы создаем формулу для расчета квартального дохода на основе объема и цены за единицу. Объем будет меняться каждый квартал в новой колонке, но цена за единицу останется прежней, поэтому мы блокируем ссылку, нажимая F4 или введите знаки доллара для $F$2 вручную.

После ввода формулы щелкните в правом нижнем углу, чтобы перетащить ее по столбцам для других кварталов.

Когда мы отпускаем кнопку, вычисляются формулы для каждого квартала. Ссылка на ячейку объема (выделена синим цветом) остается «относительной» ссылкой, которая перемещается вместе с ячейкой формулы. Однако ссылка на цену за единицу (выделена красным) стала «абсолютной», то есть 9 0021 заблокировал в исходном месте.

Создание абсолютных (блокирующих) ссылок в таблицах данных

К сожалению, использование F4 или добавление знаков доллара не блокирует ссылки в таблицах данных. Он даже не вычисляет ячейку, а выдает ошибку. К счастью, — это способ добиться такого же поведения, но для этого требуется использовать синтаксис таблицы данных Excel, о котором мы говорили выше.

Самый простой способ показать, как это делается, — рассмотреть пример. Давайте объединим два примера структур данных, с которыми мы работали, чтобы разработать задачу, которую нужно решить в следующем разделе. Нажмите Далее для продолжения!

Как построить ссылки блокировки в таблицах данных

Пример данных

Давайте расширим нашу таблицу региональных продаж до полного 6-месячного периода. Так могли бы выглядеть ваши данные, если бы вы импортировали их из базы данных или другого программного обеспечения для создания отчетов. Это очень удобно для машинного чтения, но не для человека. В этом примере мы собираемся назвать таблицу в Excel как DataTable .

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

Эта таблица будет называться Сводная таблица . Чтобы заполнить эту таблицу данными, мы собираемся использовать другую простую формулу СУММЕСЛИМН (), но мы будем работать внутри таблицы и искать информацию внутри другой таблицы…

Построение формулы, которая относится к заблокированным Столбцы и строки таблицы данных

Начиная со столбца North и строки Jan-16 , постройте следующую формулу:

 =  СУММЕСЛИМН  ( Таблица данных[[Продажи]:[Продажи]] ,  Таблица данных[[Регион]:[Регион]] ,  B$1 ,  Таблица данных[[Месяц]:[Месяц]] 900 07 ,  PivotTable[@[Month]:[Month]]  ) 

Это будет выглядеть так:

В этой формуле мы блокируем ссылки тремя разными способами:

1. Ссылка на весь заблокированный столбец в Таблица данных.
2. Ссылка на текущую строку заблокированного столбца в таблице данных.
3. : Ссылка на заблокированную строку заголовка таблицы данных.

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

1. Блокировка целых столбцов таблицы данных

Обычные ссылки на столбцы таблицы данных выглядят так, когда вы находитесь внутри одной и той же таблицы:

  [Продажи]  

Когда вы находитесь вне таблицы, в которой находится столбец, они выглядят следующим образом:

  DataTable[Продажи]  

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

  DataTable[[Продажи]:[Продажи]]  

2.

Ссылка на текущую строку заблокированного столбца таблицы данных

Нормальные текущие ссылки на строки для таблиц данных выглядят так, когда вы находитесь внутри той же таблицы:

  [@Month]  

Когда вы находитесь вне таблицы, в которой находится столбец, они выглядят следующим образом:

  PivotTable[@Month]  

Подобно блокировке всего столбца, как показано выше, хитрость блокировки текущая ссылка на строку должна рассматриваться как диапазон, но символ @ используется только в начале ссылки на диапазон:

  Сводная таблица[@[Месяц]:[Месяц]]  

3. Блокировка ссылки на заголовок таблицы данных

Работать с заголовками таблиц данных Excel немного сложно, поскольку Excel обрабатывает их как фиксированные элементы. Это означает, что он не будет помнить, что это даты или другие специальные типы данных. Они становятся именами полей. Предполагается, что количество столбцов в вашей таблице данных не изменится со временем — только количество строк.

Это означает, что для ссылки на заголовок в таблице данных в формуле (и чтобы эту формулу можно было перетаскивать), необходимо заблокировать ссылку. К счастью, строка заголовка таблицы данных всегда остается на одном месте, поэтому вы можете заблокировать ее, как обычную ссылку на ячейку в Excel. В приведенном выше примере формулы именно это мы и сделали:

  B$1  

Это обозначение гарантирует, что при перетаскивании формулы она всегда будет ссылаться на строку 1 в текущем столбце. (Номер строки заблокирован, но столбец по-прежнему является относительным.)

Завершенная формула таблицы данных

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

Новая формула в ячейке E7 считывает:

 =  СУММЕСЛИМН  (  DataTable[[Sales]:[Sales]]  ,  DataTable[[Region]:[Region]]  ,  E$1  ,  DataTable [[Месяц]:[Месяц ]]  ,  Сводная таблица[@[Месяц]:[Месяц]]  ) 

Если все сделано правильно, формула выглядит очень похоже в каждой ячейке таблицы данных! Единственное, что изменится, — это ссылки на строку заголовка таблицы данных, поскольку это обычные ссылки блокировки для ячеек Excel.

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

Получите надстройку Excel для отображения «Блокировка ссылок на таблицы данных» на F4

Этот процесс добавления диапазонов ко всем вашим ссылкам на таблицы данных, чтобы сделать их абсолютными, может быть хлопотным, если вам нужно делать это часто… К счастью , Джон Акампора из Excel Campus создал надстройку, которая сопоставляет эти диапазоны блокировки с клавиатурой как F4 , поэтому они работают так же, как обычные абсолютные ссылки в Excel.

Вы можете проверить его надстройку здесь: Абсолютные структурированные ссылки в формулах таблиц Excel

Эндрю Робертс уже более десяти лет решает бизнес-задачи с помощью Microsoft Excel. Excel Tactics призван помочь вам освоить его.

Подпишитесь на информационный бюллетень, чтобы быть в курсе последних статей. Зарегистрируйтесь, и вы получите бесплатное руководство с 10 быстрыми клавишами для экономии времени!

Как копировать формулы Excel со ссылками на таблицы — блог Contextures

Если вы создаете формулы Excel, содержащие ссылки на таблицы, а затем пытаетесь скопировать эти формулы в соседние столбцы, вы можете получить неправильные результаты. Узнайте, почему это происходит и как избежать этих проблем при копировании формул Excel со ссылками на таблицы.

Проблема копирования формул со ссылками на таблицы

В этом видеоролике показана проблема при копировании формул со ссылками на таблицы и два способа ее предотвращения. Под видео есть письменные шаги.

Хронология видео

• 00:00 Введение
• 00:46 ПРОМЕЖУТОЧНЫЙ ИТОГ Формула для проверки итогов
• 01:19 СУММЕСЛИМН Формула со ссылками на таблицы
• 01:42 Скопируйте формулу на одну строку вниз
• 02:13 Копировать через
• 03:09 Копирование без проблем
• 03:45 Получить дополнительную информацию

Формула Excel со ссылками на таблицы

В видео показана сводка продаж с формулой СУММЕСЛИМН в ячейке C5. Эта формула показывает правильную сумму продаж батончиков в Восточном регионе.

Вот формула в ячейке C5:

=СУММЕСЛИМН(Данные_продаж[Количество],Данные_продаж[Регион],$B5,Данные_продаж[Категория],C$4)

Формула относится к ячейкам заголовков в продажах резюме:

• Название региона — $B5
• Название категории – 4 канадских доллара

Формула также ссылается на 3 столбца в таблице Sales_Data, которая находится на другом листе:

• Sales_Data[ Quantity ]
• Sales_Data[ Регион ]
• Sales_Data[ Категория ]

Скопируйте формулу в следующий столбец

Формула в ячейке C5 работает правильно, но посмотрите, что произойдет, если вы выполните следующие действия:

• Выберите ячейку C5 и наведите курсор на маркер заполнения в правом нижнем углу ячейки
• Когда указатель примет вид черного знака плюса, перетащите вправо, чтобы поместить формулу в ячейку D5 (Восток — Cookies)

Формула в ячейке D5 показывает неправильную сумму, равную нулю. Если вы проверите данные о продажах, количество продаж East Cookie составит 1425.

Ссылки на таблицы смещены вправо

Чтобы устранить проблему, щелкните ячейку D5 и посмотрите на строку формул.

Вместо отображения той же формулы, которая была в ячейке C5, ссылки на таблицы изменились.

=СУММЕСЛИМН(Данные_продаж[ Общая стоимость ], Данные_продаж[ Категория ],$B5, Данные_продаж[ Продукт ],D$4)

• Количество изменено на Общая стоимость
• Регион изменен на Категория

Категория

• изменена на Продукт

Восток не найден в столбце «Категория», а файлы cookie не найдены в столбце «Продукт», поэтому результат формулы СУММЕСЛИМН равен нулю.

Предотвращение проблем со ссылками на таблицы

Чтобы предотвратить эту проблему смещения ссылок на таблицы, не используйте дескриптор заполнения для копирования формулы между столбцами.