Какая из перечисленных функций в ms excel суммирует парные произведения элементов массива
Функция СУММПРОИЗВ() , английская версия SUMPRODUCT(), не так проста, как кажется с первого взгляда: помимо собственно нахождения суммы произведений, эта функция может использоваться для подсчета и суммирования значений на основе критериев, а также, в некоторых случаях, избавить от необходимости применений формул массива.
Существует несколько вариантов применения функции СУММПРОИЗВ() :
- нахождение суммы произведений элементов списка (массива);
- суммирование и подсчет значений, удовлетворяющих определенным критериям;
- замена формул массива (в некоторых случаях).
Нахождение суммы произведений элементов массивов
В этом разделе показан синтаксис функции СУММПРОИЗВ() и раскрыт ее потенциал для других применений.
Пусть имеется 2 диапазона чисел A3:A6 и B3:B6 , содержащие соответственно 2 массива чисел : и . Записав формулу =СУММПРОИЗВ(A3:A6;B3:B6) , получим 123. Результат получен поэлементным перемножением всех элементов двух массивов, а затем сложением полученных произведений. То есть были выполнены следующие арифметические действия: 4*7 + 8*6 + 6*7 + 1*5= 123
Таким образом, можно найти сумму произведений 3-х, 4-х и т.д. массивов.
В формуле =СУММПРОИЗВ(A3:A6;B3:B6) функция СУММПРОИЗВ() трактует нечисловые элементы массивов как нулевые. Однако, как показано ниже, функцию можно использовать для подсчета текстовых значений.
Что произойдет если указать только 1 массив, т.е. =СУММПРОИЗВ(A3:A6) ? Тогда функция СУММПРОИЗВ() вернет сумму элементов, т.е. будет эквивалентна функции СУММ() : =СУММ(A3:A6) .
Синтаксис функции СУММПРОИЗВ() позволяет не просто указывать в качестве аргумента определенный диапазон, но и осуществлять арифметические действия перед операцией суммирования. Например, записав:
- =СУММПРОИЗВ(A3:A6*2) , получим сумму произведений =38 (каждый элемент массива из A3:A6 был умножен на 2, затем все произведения просуммированы);
- =СУММПРОИЗВ(A3:A6*B3:B6) , получим результат суммы произведений – 123 (все элементы массивов были попарно перемножены, а затем сложены, т.е. A3*B3+ A4*B4+ A5*B5+ A6*B6), т.е. эта запись эквивалента формула =СУММПРОИЗВ(A3:A6;B3:B6) ;
- =СУММПРОИЗВ(A3:A6+B3:B6) , получим сумму элементов из двух диапазонов;
- =СУММПРОИЗВ(A3:A6/B3:B6 ), получим сумму попарных отношений всех элементов, т.е. 4/7 + 8/6 + 6/7 + 1/5= 2,9619
Аналогичные вычисления можно выполнить и с функцией СУММ() , только для этого нужно ее ввести как формулу массива , т.е. после ввода функции в ячейку вместо ENTER нажать CTRL+SHIFT+ENTER : =СУММ(A3:A6/B3:B6)
Прелесть функции СУММПРОИЗВ() в том, что после ввода функции в ячейку можно просто нажать ENTER , что снимает некий психологический барьер перед использованием формул массива .
Оказывается, что в качестве аргумента этой функции можно указать не только произведение массивов ( A3:A6*B3:B6 ), но и использовать другие функции и даже применить к массивам операции сравнения, т.е. использовать ее для сложения чисел, удовлетворяющих определенным условиям.
Суммирование и подсчет значений удовлетворяющих определенным критериям
Попробуем подсчитать число значений больших 2 в диапазоне A3:A6 , содержащий значения 4, 8, 6, 1.
Если мы запишем формулу =СУММПРОИЗВ(A3:A6>2) , то получим результат 0. Выделив в Строке формул A3:A6>2 и нажав клавишу F9 , получим массив , который говорит, что мы движемся в правильном направлении: в диапазоне A3:A6 больше 2 только первые 3 значения. Хотя значению ИСТИНА соответствует 1, а ЛОЖЬ – 0, мы не получим 3, т.к. для перевода значений ИСТИНА/ЛОЖЬ в числовую форму требуется применить к ним арифметическую операцию. Для этого можно, например, применить операцию двойного отрицания (--), что позволит привести массив в числовую форму .
Итак, задача подсчета значений больше 2 решается следующим образом: =СУММПРОИЗВ(--(A3:A6>2))
Вместо двойного отрицания можно использовать другие формулы: =СУММПРОИЗВ(1*(A3:A6>2)) или =СУММПРОИЗВ(0+(A3:A6>2)) или даже так =СУММПРОИЗВ((A3:A6>2)^1) .
Запись >2 является критерием, причем можно указать любые операции сравнения ( =; =).
Критерии можно указывать в форме ссылки: =СУММПРОИЗВ(--(A3:A6>G8)) – ячейка G 8 должна содержать число 2.
Критерии можно применять и к текстовым значениям, например, =СУММПРОИЗВ(--(B3:B6="яблоки")) – вернет количество ячеек, содержащие слово яблоки (подробнее, например, в статье Подсчет значений с множественными критериями (Часть 1. Условие И)) .
Функцию СУММПРОИЗВ() можно использовать для отбора значений по нескольким критериям (с множественными условиями). Как известно, 2 критерия могут образовывать разные условия:
- Условие ИЛИ . Например, подсчитать ячеек содержащих значение яблоки ИЛИ груши =СУММПРОИЗВ((B3:B6="яблоки")+ (B3:B6="груши") ) ;
- Условие И . Например, подсчитать количество значений больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 )
- Условие И . Например, найти сумму Чисел больше 2 и меньше 5: =СУММПРОИЗВ((A3:A6>2)* (A3:A6 A3:A6 ) )
В файле примера приведены решения подобных задач.
СУММПРОИЗВ() – как формула массива
В ряде случаев (когда нужно подсчитать или сложить значения, удовлетворяющие определенным критериям) можно заменить использование формул массива функцией СУММПРОИЗВ() , например:
- =СУММПРОИЗВ(--ЕПУСТО(D2:D23)) подсчет пустых ячеек в диапазоне;
- =СУММПРОИЗВ(НАИБОЛЬШИЙ(A:A;)) сумма 3-х наибольших значений ;
- =СУММПРОИЗВ((A3:A6>СРЗНАЧ(A3:A6))*(A3:A6)) сумма значений, которые больше среднего .
Совет : Дополнительную информацию об этой функции можно ]]> подчерпнуть здесь (английский язык). ]]>
Кудрявцева, Л. Б. Информатика: учеб. пособие / Л. Б. Кудрявцева. – Ростов н/Дону : Российская таможенная академия, Ростовский филиал, 2011 (Раздел «Электронные таблицы»).
Додж, М., Кината К., Стинсон К. Эффективная работа с MS EXCEL 2000 / М. Додж, К. Кината, К. Стинсон. – СПб. : BPV-Санкт-Петербург, 2001 (Части 1 – 6).
Кудрявцева, Л.Б. Информатика : учеб. пособие [Электронный ресурс] / Л. Б. Кудрявцева. – Режим доступа: Учебный сервер Ростовского филиала Российской таможенной академии, 2011 (Раздел «Электронные таблицы»).
V. Контрольные вопросы для самопроверки
Проверьте свои знания, ответив на следующие вопросы. Ответ может содержать несколько вариантов.
1. Что можно заносить в ячейки?
2. Что может входить в состав формулы?
3. Что такое абсолютный адрес?
Адрес, который не меняется при копировании формулы
Адрес, который меняется при копировании формулы
Адрес, состоящий из буквы и цифры
4. Что такое относительный адрес?
Адрес, который не меняется при копировании формулы
Адрес, который меняется при копировании формулы
5. Что происходит с относительным адресом при копировании формул?
Адрес не меняется
При копировании "вниз" меняется цифра адреса
При копировании "вправо" меняется буква адреса
Меняется его формат
6. Какими способами можно создать абсолютный адрес ? (отметьте нужные действия)
Задать имя ячейке
Поставить знак $ перед формулой
Поставить знак $ перед буквой или (и) цифрой адреса
7. Какая из перечисленных функций суммирует парные произведения элементов массива ?
8. Какие из формул написаны неверно?
9. Что такое диапазон ячеек ?
Ячейки, расположенные рядом в строке
Ячейки, расположенные рядом в столбце
Ячейки, расположенные рядом в прямоугольной области
10. С чего начинается любая формула ?
Модуль 3. Алгоритмические средства информатики
Тема 3.1. Общие сведения о моделировании.Алгоритмизация и программирование
I. Задания для самостоятельной работы
Ознакомиться с понятием алгоритм, изучить свойства алгоритма и способы записи алгоритма.
Изучить, как строится структурная схема, ее основные элементы и структуры (условные, циклические, присваивания).
Создать структурную схему наиболее используемых алгоритмов - суммирования, нахождения произведения (циклические), нахождения корней квадратного уравнения общего вида (условные).
Познакомиться с обзором языков программирования, изучить общие структуры.
Ознакомиться с понятием среда программирования?
Ознакомиться с понятиями трансляция программы на языке программирования, программы-компиляторы, программы-трансляторы.
Ознакомиться с понятием технологии программирования.
Познакомиться с основными методами проектирования программ.
Кудрявцева, Л. Б. Информатика: учеб. пособие / Л. Б. Кудрявцева. – Ростов н/Дону : Российская таможенная академия, Ростовский филиал, 2011 (Раздел «Электронные таблицы»).
Додж, М., Кината К., Стинсон К. Эффективная работа с MS EXCEL 2000 / М. Додж, К. Кината, К. Стинсон. – СПб. : BPV-Санкт-Петербург, 2001 (Части 1 – 6).
Кудрявцева, Л.Б. Информатика : учеб. пособие [Электронный ресурс] / Л. Б. Кудрявцева. – Режим доступа: Учебный сервер Ростовского филиала Российской таможенной академии, 2011 (Раздел «Электронные таблицы»).
V. Контрольные вопросы для самопроверки
Проверьте свои знания, ответив на следующие вопросы. Ответ может содержать несколько вариантов.
1. Что можно заносить в ячейки?
2. Что может входить в состав формулы?
3. Что такое абсолютный адрес?
Адрес, который не меняется при копировании формулы
Адрес, который меняется при копировании формулы
Адрес, состоящий из буквы и цифры
4. Что такое относительный адрес?
Адрес, который не меняется при копировании формулы
Адрес, который меняется при копировании формулы
5. Что происходит с относительным адресом при копировании формул?
Адрес не меняется
При копировании "вниз" меняется цифра адреса
При копировании "вправо" меняется буква адреса
Меняется его формат
6. Какими способами можно создать абсолютный адрес ? (отметьте нужные действия)
Задать имя ячейке
Поставить знак $ перед формулой
Поставить знак $ перед буквой или (и) цифрой адреса
7. Какая из перечисленных функций суммирует парные произведения элементов массива ?
8. Какие из формул написаны неверно?
9. Что такое диапазон ячеек ?
Ячейки, расположенные рядом в строке
Ячейки, расположенные рядом в столбце
Ячейки, расположенные рядом в прямоугольной области
10. С чего начинается любая формула ?
Модуль 3. Алгоритмические средства информатики
Тема 3.1. Общие сведения о моделировании.Алгоритмизация и программирование
I. Задания для самостоятельной работы
Ознакомиться с понятием алгоритм, изучить свойства алгоритма и способы записи алгоритма.
Изучить, как строится структурная схема, ее основные элементы и структуры (условные, циклические, присваивания).
Создать структурную схему наиболее используемых алгоритмов - суммирования, нахождения произведения (циклические), нахождения корней квадратного уравнения общего вида (условные).
Познакомиться с обзором языков программирования, изучить общие структуры.
Ознакомиться с понятием среда программирования?
Ознакомиться с понятиями трансляция программы на языке программирования, программы-компиляторы, программы-трансляторы.
Ознакомиться с понятием технологии программирования.
Познакомиться с основными методами проектирования программ.
Кудрявцева, Л. Б. Информатика: учеб. пособие / Л. Б. Кудрявцева. – Ростов н/Дону : Российская таможенная академия, Ростовский филиал, 2011 (Раздел «Электронные таблицы»).
Соболь, Б. В. Информатика : учебник / Б. В. Соболь, А. П. Галин, Ю. В. Панов. – Ростов н/Д : Феникс, 2006 (Раздел 4, тема 4.3).
Додж, М., Кината К., Стинсон К. Эффективная работа с MS EXCEL 2000 / М. Додж, К. Кината, К. Стинсон. – СПб. : BPV-Санкт-Петербург, 2001 (Части 1 – 6).
Кудрявцева, Л.Б. Информатика : учеб. пособие [Электронный ресурс] / Л. Б. Кудрявцева. – Режим доступа: Учебный сервер Ростовского филиала Российской таможенной академии, 2011 (Раздел «Электронные таблицы»).
V. Контрольные вопросы для самопроверки
Проверьте свои знания, ответив на следующие вопросы. Ответ может содержать несколько вариантов.
1. Что можно заносить в ячейки?
2. Что может входить в состав формулы?
3. Что такое абсолютный адрес?
Адрес, который не меняется при копировании формулы
Адрес, который меняется при копировании формулы
Адрес, состоящий из буквы и цифры
4. Что такое относительный адрес?
Адрес, который не меняется при копировании формулы
Адрес, который меняется при копировании формулы
5. Что происходит с относительным адресом при копировании формул?
Адрес не меняется
При копировании "вниз" меняется цифра адреса
При копировании "вправо" меняется буква адреса
Меняется его формат
6. Какими способами можно создать абсолютный адрес ? (отметьте нужные действия)
Задать имя ячейке
Поставить знак $ перед формулой
Поставить знак $ перед буквой или (и) цифрой адреса
7. Какая из перечисленных функций суммирует парные произведения элементов массива ?
8. Какие из формул написаны неверно?
9. Что такое диапазон ячеек ?
Ячейки, расположенные рядом в строке
Ячейки, расположенные рядом в столбце
Ячейки, расположенные рядом в прямоугольной области
10. С чего начинается любая формула ?
Тема 3.4. Средства анализа данных в табличном процессоре MS EXCEL
I. Задания для самостоятельной работы
Повторить учебный материал первого семестра, абсолютные и относительные адреса, работа с формулами, сводные таблицы.
Просмотреть и вспомнить решение задач первого семестра.
Изучить новый материал – средства анализа данных в табличном процессоре.
II. Планы практических занятий
I занятие (2 ч)
Повторение материала первого семестра.
Использование стандартных функций для решения задач.
Решение задач таможенного цикла на вычисление удельного веса, построение трендов и другие расчеты.
II занятие (2 ч)
Самостоятельная работа, блиц-опрос.
Знакомство с Методом «Подбор параметра» для решения различных уравнений-моделей.
III занятие (2 ч)
Решение задач Методом «Подбор параметра».
Решение уравнений графическим способом.
IV занятие (2 ч)
Изучение и использование метода «Сценарий» для прогнозирования оптимального решения.
V занятие (2 ч)
Знакомство с методом «Поиск решения».
Блиц-опрос по темам модуля.
III. Рекомендации по выполнению заданий
Изучение этой темы необходимо начать с повторения основных понятий и приемов работы в ЭТ MS Excel.
Существует ряд средств, которые позволяют проводить анализ "что-если":
Подбор параметра – это механизм для решения уравнений (даже очень сложных, трансцендентных). С помощью техники Подбор параметра можно решать как математические, так и экономические задачи. Однако этот механизм позволяет найти только одно значение. Например, для решения квадратного уравнения, имеющего два корня, необходимо применить Подбор параметра дважды.
Пример математической задачи
Необходимо найти корни уравнения Х 2 - 3Х + 1 = 0.
Для решения уравнения необходимо выполнить следующие действия:
Взять произвольное значение Х, например, Х=2 и вычислить значение У.
Сделать ячейку с формулой активной (установить курсор) и найти в меню Анализ, затем выбрать команду Подбор параметра.
В окне диалога Подбор параметра оставьте без изменения значение в поле Установить в ячейке.
В поле Значение ввести значение, которое нужно получить для нахождения одного из корней, в данном примере - 0 (т.к. корень обращает функцию в 0).
В поле Изменяя значение ячейки ввести ссылку на ячейку, где находится начальное значение Х.
После нажатия кнопки "ОК" выведется окно диалога Результат подбора параметра, для сохранения этого значения в ячейке снова нажать "ОК".
Для нахождения 2-го корня взять Х=-2 и вычислить значение У.
Для этого значения повторить пункты 2 - 6.
На картинке приводятся значения Х, У (слева) до применения техники Подбор параметра, а справа после ее применения - два найденных корня с некоторой точностью (1-й менее точный, 2-й - более).
При моделировании сложных задач можно использовать диспетчер сценариев или команду Сценарий.
Определим некоторые термины:
Сценарий — это именованные комбинации значений, заданных для одной или нескольких изменяемых ячеек в модели "что-если".
Модель “что-если” — это любой рабочий лист, в котором можно подставлять различные значения для переменных, таких как “Среднее кол-во покупателей”, чтобы увидеть их влияние на другие величины, например “Чистая прибыль”, которые вычисляются по формулам, зависящим от этих переменных. Изменяемые ячейки — это ячейки, содержащие значения, которые будут изменяться в ходе исследования.
Диспетчер сценариев позволяет создать столько сценариев, сколько необходимо для модели “что-если”. Затем можно напечатать отчеты с подробными сведениями обо всех изменяемых и результирующих ячейках. При работе с диспетчером сценариев можно:
создать несколько сценариев для одной модели “что-если”, каждый из которых может иметь свое собственное множество переменных;
отслеживать варианты сценария, так как диспетчер сценариев сохраняет дату и имя пользователя при каждом изменении сценария
воспользоваться отчетом Сводная таблица и сравнить между собой сценарии с разными множествами переменных.
Прежде чем начнется работа с диспетчером сценариев, имеет смысл присвоить имена ячейкам, которые используются для переменных, а также ячейкам, содержащим формулы. Данный шаг не является необходимым, но в этом случае отчеты по сценариям, а также некоторые окна диалогов станут более наглядными, поскольку в сценарии будут использоваться не адреса ячеек, а названия величин, находящихся в них.
Поиск решения
Поиск решения используется для решения сложных задач, например, по оптимизации каких-либо решений. Этот инструмент может применяться для решения задач, которые включают много изменяемых ячеек, т.е. найти несколько значений переменных целевой функции. Он также позволяет задать одно или несколько ограничений — условий, которые должны выполняться при поиске решения.
Без формул массива (array formulas ) можно обойтись, т.к. это просто сокращенная запись группы однотипных формул. Однако, у формул массива есть серьезное преимущество: одна такая формула может заменить один или несколько столбцов с обычными формулами.
Например, можно найти сумму квадратов значений из диапазона А2:A12 , просто записав в ячейке B14 формулу =СУММ(A2:A12^2) . Для сравнения: чтобы найти сумму квадратов, используя обычные формулы, нам потребуется дополнительный столбец для вычисления квадратов значений и одна ячейка для их суммирования (см. файл примера или диапазон B 2 :B13 на рисунке ниже).
В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо ENTER комбинацию клавиш CTRL+SHIFT+ENTER (поэтому, иногда, формулы массива также называются формулами CSE - это первые буквы от названия клавиш, используемых для ввода C trl, S hift, E nter). После этого формула будет обрамлена в фигурные скобки < >(их не вводят с клавиатуры, они автоматически появляются после нажатия CTRL+SHIFT+ENTER ). Это обрамление показано на рисунке выше (см. Строку формул ).
Чтобы глубже понять формулы массива проведем эксперимент:
- выделим ячейку B13 , содержащую обычную формулу =СУММ($B$2:$B$12) ;
- в C троке формул выделим аргумент функции СУММ() , т.е. $B$2:$B$12 ;
- нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
- получим – массив квадратов значений из столбца В . Массив – это просто набор неких элементов (значений).
Т.е. обычная функция СУММ() в качестве аргумента получила некий массив (или точнее ссылку на него).Теперь проведем тот же эксперимент с формулой массива:
- выделим ячейку, содержащую формулу массива =СУММ($A$2:$A$12^2) ;
- в строке формул выделим аргумент функции СУММ() , т.е. $A$2:$A$12^2 ;
- нажмем клавишу F9 , т.е. вычислим, выделенную часть формулы;
- получим – тот же массив, что и в первом случае.
Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ() , МАКС() , НАИБОЛЬШИЙ() и т.п.
Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения ( СУММ() , СРЗНАЧ() , МАКС() ). Примеры таких функций массива приведены в статье Формулы массива, возвращающие одно значение .
Формулы массива также могут возвращать сразу несколько значений. Понятно, что для того чтобы отобразить такой результат необходимо задействовать целый диапазон ячеек. Примеры таких функций приведены в статье Формулы массива, возвращающие несколько значений .
Преимущества и недостатки формул массива рассмотрены в одноименной статье Формулы массива. Преимущества и недостатки .
В файле примера также приведено решение данной задачи функцией СУММПРОИЗВ() , которая зачастую не требует введения ее как формулы массива: =СУММПРОИЗВ($A$2:$A$12^2)
Здесь, при вводе формулы СУММПРОИЗВ() нажимать CTRL+SHIFT+ENTER необязательно.
ПРИМЕЧАНИЕ : При создании Именованных формул и правил Условного форматирования формулы массива нельзя ввести нажимая CTRL+SHIFT+ENTER . Эти формулы вводятся только в ячейки листа. Однако, если формуле массива присвоить Имя , то EXCEL «сообразит», что нужно с ней нужно делать. Например, если формуле =СУММ($A$2:$A$12^2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов , то получим правильный результат.
Читайте также: