Суммы трех наименьших элементов диапазона excel
Обновлено: 08.07.2024
При обработке больших таблиц иногда возникает потребность получить итоговые значения на основе данных, расположенных в диапазонах ячеек, не позволяющих применить функции консолидации с прямой адресацией, либо требующих дополнительных вычислений. В статье описывается несколько вариантов построения моделей на примере экономической задачи расчета валовой выручки.
Для простоты понимания все примеры формул будем рассматривать на основе простейшей экономической задачи: вычисление итоговой выручки при известных значениях объема и цен продажи по нескольким покупателям.
В задаче подразумевается, что количество покупателей и периодов не постоянно. Требуется получить итоговые значения по объемам продаж и выручке. Очевидно, что применить простую функцию суммы (СУММ) не получится даже для получения итогов по объемам. Аналогично и для вычисления выручки не годится применение функции СУММПРОИЗВ «в лоб», так как диапазоны ячеек-множителей не связаны между собой.
На практике решение подобных задач зачастую сводится к написанию «некопируемых» формул, либо требует преобразование итоговых формул после добавления данных (см. лист Пример1, ячейки C10,C11). Этот метод допустим только на небольших объемах данных и нарушает основные принципы оптимизации работы в электронных таблицах.
Второй стандартный метод решения проблемы – это создание дополнительных таблиц с промежуточными вычислениями.
Такой способ позволяет применить одинаковые (копируемые) формулы в итоговых ячейках, а также не требует изменения итоговых формул при добавлении строк или столбцов с данными. Эти два условия обеспечивают максимальную достоверность вычислений при развитии и эксплуатации экономической модели.
Обратите внимание, задача корректности формул итогов при добавлении строк решена за счет добавления пустой служебной строки перед строкой итогов (лист Пример2, строки 6 и 22). Без пустой строки при добавлении нового покупателя вниз массива функция СУММ не подхватывает новые данные автоматически. Служебные строки – это стандартный «безопасный» прием при работе с переменным количеством строк с данными. При желании их можно скрыть стандартными методами интерфейса Excel.
К недостаткам способа решения задачи через добавление промежуточных вычислений следует отнести:
- Дополнительное преобразования исходных данных, что зачастую сложно выполнимо на практике (представьте, что в исходном файле покупателей не 3, а 3000).
- Сложность добавления информации – нового покупателя требуется добавлять не в одну, а в три таблицы.
Попробуем все-таки решить задачу без использования промежуточных таблиц, но сохранив копируемость и корректность итоговых формул при добавлении покупателей и периодов.
Как уже отмечалось выше, формулы в ячейках C10:C11 решают задачу верно, но усложняют работу с моделью. Хочется только обратить внимание на применение функции СУММ с дополнительными вычислениями в ячейке C11.
Остальные формулы в диапазоне D10:E11 также верны и при этом полностью соответствуют требованиям задачи. Рассмотрим их поподробнее.
Итого объем, кг: D10
Формула очень простая и очевидная для понимания. Суммирование происходит по условию, закрепленному в ячейке $B$3. Вместо $B$3 можно было написать «объем, кг» непосредственно в функции. Это с одной стороны делает формулу более читаемой, с другой стороны, менее безопасно, так как при замене значения в ячейках заголовков, например с «объем, кг» на «объем, л» функция будет работать неверно.
Также необходимо заметить, что возможность применения СУММЕСЛИ часто бывает ограничена из-за допустимости проверки только по одному условию (только в Excel 2007, появилась функция СУММЕСЛИМН). В сложных случаях без формул с обработкой массивов не обойтись.
Итого объем, кг: E10
Идея формулы – посчитать сумму только в нечетных строках данных. Формула работает с массивом ячеек E3:E9 (признак – фигурные скобки). Нечетные строки здесь определяются как остаток равный 1 при делении порядкового номера строки диапазона на 2. Дополнительно для поддержания целостности номера отсчитываются от заголовка таблицы СТРОКА(E2). Это позволяет переносить данную таблицу целиком на другое место на текущем или другом листе модели.
Формулы обработки массивов применяются через нажатие Ctrl+Shift+Enter
Итого выручка, руб: D11
Одно из нестандартных применений функции СУММПРОИЗВ. Идея – перемножить три массива чисел. Первый массив – объемы, второй – цены, а третий является триггером, умножающим ненужные данные на ноль.
Преобразуем формулу через выделение текста в скобках и нажатие F9, получим:
Представим в более понятном виде:
| 400 | 0,06 | ИСТИНА | =24 |
| 0,06 | 500 | ЛОЖЬ | =0 |
| 500 | 0,15 | ИСТИНА | 75 |
| 0,15 | 600 | ЛОЖЬ | =0 |
| 600 | 0,14 | ИСТИНА | 84 |
| 0,14 | 0 | ЛОЖЬ | =0 |
| 0 | 1500 | ЛОЖЬ | =0 |
При умножении массивов логическое значение ЛОЖЬ преобразуется в 0, а ИСТИНА в 1.
Итого выручка, руб: E11
Формула очень похожа на формулу вычисления итоговых объемов, находящуюся в ячейке E10 (см.выше), только происходит дополнительное умножение на диапазон со сдвигом вниз (E4:E10) – назовем его условно «диапазон цен», хотя это и не совсем верно по смыслу. При обработке массива, в тот момент когда условная функция определяет что строка содержит объемы, происходит дополнительное умножение на ячейку с тем же порядковым номером в своем массиве, но из диапазона цен. Следует заметить, что при применении в формуле нескольких массивов, они должны быть одинаковой размерности. Из-за этого диапазоны обработки в формулах «заезжают» на строку 10, что вообще-то не очень корректно. В нашем примере это не приводит к ошибкам, но в практической работе рекомендуется избегать таких ситуаций. Простейшим решением здесь может быть добавление еще одной пустой строки перед итогами.
Приведенные примеры формул показывают многовариантность решения одной и той же задачи. Наверняка найдется еще несколько способов, не менее эффективных, и возможно даже более простых. Универсальной рекомендации на все случаи жизни, какой способ выбрать при решении подобных задач, дать, пожалуй, не получится. Главное, стремиться не только изучать новые возможности, но и применять их на практике, следуя основным принципам оптимизации работы в электронных таблицах.
считая заданными величины x , y , z соответственно в ячейках A3, B3 и C3.
Введем в ячейки A3, B3 и C3 конкретные значения переменных, например 1.2, 3, 1.5 и присвоим этим ячейкам соответственно имена X , Y , Z . Для присвоения имен ячейкам используйте команду Вставка /Имя /Присвоить.
В ячейки A 5, A 6 и A 7 введем поясняющий текст, а в ячейки B 5, B 6 и B 7 соответствующие формулы. Например, для вычисления первого значения можно ввести формулу =4+3* X +2* X ^2+ X ^3. Однако, лучше провести вычисления по схеме Горнера, которая позволяет уменьшить число выполняемых операций. В этом случае формула примет вид =(( X +2)* X +3)* X +4. Предложенные формулы используют в качестве операндов, созданные имена, что делает их похожими на соответствующие математически формулы. При необходимости, в формулах также можно использовать и ссылки на ячейки рабочей таблицы. В этом случае нужная формула имела бы вид = ( A 3+2)* A 3+3)* A 3+4.
Вид электронной таблицы приведен на следующем рисунке.
Задание 2.
На листе создайте таблицу, содержащую сведения о ценах на продукты. Заполните пустые клетки таблицы произвольными ценами, кроме столбца «Среднее значение» и строки «Всего».
Среднее значение
Создайте имена по строкам и столбцам и вычислите среднемесячные цены каждого продукта и всего молочных продуктов по месяцам, используя построенные имена.
Методические указания.
Для вычисления среднего значения используйте функцию СРЗНАЧ.
Задание 3.
На листе запишите формулу для вычисления произведения сумм двух одномерных массивов A и B , т.е. где a i и b i соответствующие элементы массивов, а n – их размерность.
Конкретные данные, например, A = и B = введем соответственно в ячейки A 2: E 2 второй и A 3: E 3 третьей строки листа 3 рабочей таблицы. Затем в ячейку A 5 введем формулу: =СУММ(A2:E2)*СУММ(A3:E3). Если диапазону A 2: E 2 присвоить имя А, а диапазону А3:Е3 – имя В, то можно применить формулу: =СУММ(A)*СУММ(В).
В
ид электронной таблицы приведен на рисунке.
Задание 4 .
На листе запишите формулы вычисления сумм S i каждой строки двумерного массива (матрицы) D , т.е. где m – количество строк матрицы, n – количество столбцов
Конкретные данные d i , j >, i =1,2,…,5, j =1,2,… ,4. (матрица пять строк четыре столбца), введем в ячейки A 1: D 5. Вычислим суммы каждой строки и поместим их в ячейки F 1: F 5. Для этого поместим в ячейку F 1 формулу: =СУММ(A1: D 1), и с помощью маркера автозаполнения скопируем ее в ячейки F 2: F 5. Так как в формуле используется относительная ссылка, то каждая копия настроится на свое местоположение и будет вычисляться сумма соответствующей строки матрицы.
Задание 5.
На листе запишите формулы для вычисления значений элементов массива y i = a i / max ( b i ) , i =1, 2,…, n , где a i и b i элементы соответствующих массивов, а n – их размерность.
Конкретные данные a i >, i =1,2,…,5; b i >, i =1,2,…,5, введем соответственно в ячейки A 2: E 2 второй, и A 3: E 3 третьей строки листа 5 рабочей таблицы. Затем в ячейку A 5 введем формулу: = A 2/ МАКС($ A $3:$ E $3) и с помощью маркера автозаполнения скопируем ее в ячейки B 5: F 5. Во втором операнде использована абсолютная ссылка, поэтому на новое местоположение будет настраиваться только первый операнд.
Задание 6 .
На листе задайте произвольный массив чисел. Вычислите сумму положительных чисел и количество отрицательных чисел в этом массиве.
Произвольные данные введем, например, соответственно в ячейки A 2: D 6 листа рабочей таблицы. Для вычисления суммы положительных чисел, в ячейку F 4 введем формулу: =СУММЕСЛИ( A 2: D 6;”>0”; A 2: D 6), а для вычисления количества отрицательных в ячейку F 5 формулу: =СЧЕТЕСЛИ( A 2: D 6;”<0”).
Задание 7 .
На листе заполните произвольный диапазон любыми числами. Найдите сумму чисел больших заданного в ячейке A 1 числа.
Конкретные данные введем, например, соответственно в ячейки A 2: E 2 листа рабочей таблицы. В ячейке А1 запишем произвольное число, а в ячейку A 4 введем формулу: =СУММЕСЛИ( A 2: E 2;”>”& A 1; A 2: E 2).
Задание 8 .
На листе задайте массив чисел и используя соответствующие функции вычислите среднее арифметическое положительных чисел и среднее арифметическое абсолютных величин отрицательных чисел в этом массиве.
Методические указания.
Среднее арифметическое значение положительных чисел равно частному от деления суммы положительных чисел на количество положительных. Для решения задания используйте функции СУММЕСЛИ, СЧЕТЕСЛИ и ABS .
Задание 9 .
На листе создайте произвольный список имен, и присвойте ему имя ИМЕНА. Определите, сколько раз в списке ИМЕНА содержится Ваше имя, заданное в ячейке.
Методические указания.
Используйте функцию СЧЕТЕСЛИ.
Задания для самостоятельной работы
Задание 1С .
Написать формулы, заполнения диапазона А1:A100 равномерно распределенными случайными числами из отрезка [-3,55; 6,55], а диапазона B1:B100 случайными целыми числами из отрезка [-20;80]. Скопировать значения указанных диапазонов в диапазоны D 1: D 100 и E 1: E 100, увеличив вдвое значения второго диапазона.
Для заполнения диапазона А1:A100 равномерно распределенными случайными числами из отрезка [-3,55; 6,55] введите в ячейку A1 формулу =СЛУЧМЕЖДУ(-3,55;6,55) или =СЛЧИС()*9,1-3,55 а затем скопируйте ее в остальные ячейки диапазона.
Для заполнения диапазона B 1: B 100 равномерно распределенными случайными числами из отрезка [-20; 80] введите в ячейку B 1 формулу =СЛУЧМЕЖДУ(-20;80) или =СЛЧИС()*100-20 а затем скопируйте ее в остальные ячейки диапазона.
Для увеличения вдвое значений диапазона B 1: B 100 при копировании в диапазон E 1: E 100 введите в ячейку E 1 формулу = B 1*2 а затем скопируйте ее в остальные ячейки диапазона.
В свободные ячейки написать формулы:
нахождения среднего арифметического построенных диапазонов (используйте функцию СРЗНАЧ());
максимального и минимального элементов, построенных диапазонов (используйте функции МАКС() и МИН());
суммы трех наименьших элементов диапазона А1: B 100 ;
положительного элемента, который чаще всего встречается в диапазоне А1: B 100
Задание 2С .
Для заданного диапазона ячеек рабочего листа Excel.
Написать формулы вычисляющие:
Сумму элементов диапазона, значения которых попадают в отрезок [-5; 10].
Количество элементов диапазона больших некоторого числа, записанного в ячейке рабочей таблицы (например, из ячейки G 1) (используйте функцию СЧЁТЕСЛИ()).
Количество элементов диапазона, значение которых меньше среднего значения элементов диапазона (используйте функции СЧЁТЕСЛИ() и СРЗНАЧ(), см. также пояснения к Заданию 7).
Три значения в одном
Здравствуйте, в одной ячейке должно быть три значения,- 1-значение (это 0), 2значение(число меньше.
Найти три наименьших значения первого массива и три наибольших значения второго, попарно поменять их местами
Помогите пожалуйста с решением задачи. А то на ум вообще ничего не приходит:( Даны два массива.
Найти три наименьших элемента массива
Найти три наименьших элемента массива:Z= и указать их индексы .
Под рукой экса нет, проверить не могу, но думаю так работать будет:
=срзнач(наименьший(а1:а5;))
Вместо , по идее, можно использовать строка(1:3)
ЗЫ Ессно - формула массива
Да, работает! Вещь ) по сути понимал как делать, НАИМЕНЬШИЙ и СРЕДНЕЕ.. но не знал как именно сделать выборку по 3м наименьшим значениям. теперь тоже буду знать. Спасибо Подскажите пожалуйста, а как найти наименьшее значение, например, из каждого 10-го значения столбца А (А1, А11, А21 . А1001). Формула массива (вводится с помощью Ctrl+Shift+Enter, отображается в фигурных скобках) Добавлено через 3 минутыКста, вот еще тема про массив и НАИБОЛЬШИЙ (с НАИМЕНЬШИЙ аналогично):
Поиск второго максимального значения в массиве Спасибо, работает. Правда есть один нюанс, она производит вычисление, если заполнен весь массив от А1 до А1001, в моем случае данные дополняются и пока не достигли 1001 ячейки. Как сделать, что бы мин значение определялось каждый раз при добавлении нового значения в вышеуказанном диапазоне? А как вводить значения в ячейки А, если там =СЛЧИС()*1000?
Я с клавиатуры ввожу. Можно макросом ещё. Других способов не знаю.
Вводите как обычно Можете очистить столбец. Эти формулы для того, чтобы продемонстрировать Вам работоспособность составленной формулы. Я вообще-то в курсеМне эта формула нужна была, что бы вручную данные не вводить. Вам-то она зачем?! Теперь все ясно, я думал, что она нужна для работы основной формулы. . и я написал её в тех ячейках, в которых должны быть Ваши значения?!
Ну и логика
Что тут сказать - тупанул слегка (я с excel как-то не очень дружу).
Что-то не выходит, может потому что первое значение находится в А4?
Добавлено через 12 минут
В связи с ограничениями админа я не могу просто скопировать Вашу формулу, и при попытке ее самостоятельно ввести не могу понять, как значение "ДАННЫЕ" из Вашей формулы подхватывает нужный интервал ячеек.
Ужас какой. Даже красные кхмеры такого зверства не творили.
как значение "ДАННЫЕ" из Вашей формулы подхватывает нужный интервал ячеек Что-то не выходит, может потому что первое значение находится в А4? Да, если первое значение находится в А4, формула для имени-функции ДАННЫЕ будет другая.
Но как в таком случае считать каждое 10-е значение? Это значения в ячейках А11, А21, А31 и т.д., как было в исходном задании (А1 отпадает), или А4, А14, А24 и т.д.? Злобный админ заблокировал copy-paste?!
Ужас какой. Даже красные кхмеры такого зверства не творили.
ДАННЫЕ - это имя-функция, см. Ctrl+F3.Да, если первое значение находится в А4, формула для имени-функции ДАННЫЕ будет другая.
Но как в таком случае считать каждое 10-е значение? Это значения в ячейках А11, А21, А31 и т.д., как было в исходном задании (А1 отпадает), или А4, А14, А24 и т.д.?
У меня отсутствует возможность сохранять и передавать в инет файлы (специфика работы), все только через админа - "красных кхмеров".
Да первое значение на в А1, даже на в А4, а в А14 (в предыдущем посте ошибся).
Добавлено через 3 часа 38 минут
Формула для имени-функции ДАННЫЕ при А14 будет соответственно =ЛИСТ1!$A$14:$A$1001.
А может еще причина не работающей формулы в том, что в анализируемых ячейках не просто значения, а результаты деления (например =В14/С14)?
Ниже приведены варианты функции СУММЕСЛИ Excel. Описания, пошаговый алгоритм использования в зависимости от варианта функции, с примерами использования и иллюстрациями.
СУММЕСЛИ следует использовать в тех случаях, когда Вам необходимо суммировать значения в диапазоне, которые соответствует указанному Вами критерию.
Возможные варианты данной функции:
- сумма, если больше;
- сумма, если меньше;
- сумма, если равно;
- сумма, если не равно;
- сумма если больше или равно;
- сумма если меньше или равно.
Отдельная статья описывает функцию СУММЕСЛИ Excel. Точное совпадение. Частичное совпадение. С текстовыми критерий. Содержит описание, примеры и иллюстрации. Статья находиться по этой ссылке: Функция СУММЕСЛИ в MS Excel. Точное совпадение. Частичное совпадение. Инструкция и примеры.
Самый простой и понятный способ вызвать данную функцию, это нажать в правом верхнем углу, рядом с кнопкой ∑, стрелку вниз (Рис. 1).
Появиться список функций. Как правило, это наиболее часто используемые функции. В этом списке уже может быть нужная Вам функция СУММЕСЛИ. В нашем случае ее там нет. Тогда выбираем пункт Другие функции. Он находиться внизу списка (Рис. 2).
Появляется диалоговое окно – Вставка функции. Самое верхнее, это поле для поиска нужной функции. Вводим название функции, выбираем ее в результатах поиска и нажимаем ОК (Рис. 3).
В нем есть три ключевых поля для заполнения:
Что бы выбрать нужный диапазон нажимаете на стрелочку в конце строки диапазона и выбираете нужные Вам ячейки с помощью мыши. Можно сразу выбрать нужный диапазон, не нажимая на стрелочку, но сначала нужно поставив курсор на нужную строку в диалоговом окне. Можно прописать диапазон вручную. Результат от этого не зависит. После того как все готово, нажимает ОК (Рис. 5).
Далее рассмотрим варианты использования данной функции на конкретных примерах, что бы понять, как они работают.
Напоминаю, что перед тем, как вызывать функцию, нужно выбрать ячейку, в которой должно появиться искомое значение, результат применения функции.
Сумма, если больше, Сумма, если меньше. СУММЕСЛИ.
Знаки, которые используются: >, <.
У нас есть 25 строк с названием Пример и номер примера, в таблице. Напротив каждого примера стоит значение (Рис. 6).
Сумма, если больше.
Формула в ячейке, как пример, выглядит вот так:
Напротив каждого примера стоит число от 100 до 1300. Нам нужно просуммировать, только значения, которые больше числа 500.
Вызываем функцию, как описано выше и выбираем нужный нам диапазон в строке – Диапазон (Е3;Е27). Это столбец со значениями чисел от 100 до 1300.
Вводим критерий. В строке Критерий, пишем: >500, вручную. Можно скопировать значение критерия с самой таблицы и добавить в ручную только знак >. Кавычки появятся автоматически (Рис. 7).
Нажимаем ОК. Напротив ячейки с словом ИТОГО, появилась сумма чисел больше 500 (Рис. 8)
Сумма, если меньше.
Формула в ячейке, как пример, выглядит вот так:
Допустим, нам нужно найти сумму в диапазоне значений (E3:E27) меньше числа 500.
Алгоритм точно такой же. В критерии указывает знак меньше <, и необходимое значение. В нашем примере 500.
Сумма, если равно, Сумма, если не равно. СУММЕСЛИ.
Знаки, которые используются: =, <>.
Сумма, если равно.
Формулы в ячейках, как пример, выглядят вот так:
В столбце D, есть четыре ячейки с Пример 1 (выделенные красным шрифтом). Наша задача суммировать значения, которые стоят только напротив Пример 1. Вызываем функцию СУММЕСЛИ. В Диапазоне выбираем нужный диапазон (столбец D) (D3:D27).
В Критерий, вручную пишем Пример 1 или копируем с таблицы. По этому критерию будет осуществляться поиск нужных ячеек в диапазоне (D3:D27).
В Диапазон_суммирования выбираем диапазон ячеек с значениями (столбец Е), которые будут суммироваться (E3:E27). Суммироваться будут те ячейки, которые будут отвечать нашему критерию и находиться напротив ячеек с значением Пример 1. Нажимаем ОК (Рис. 9).
Так же можно использовать данный вариант функции, когда из списка значений (цифр), нужно суммировать только цифры равные определенному значению. Например, в диапазоне значений (E3:E27), нам нужно суммировать только значения равные числу 100 (выделенные красным шрифтом). В строке Критерий указываем необходимое значений, после знака =. В нашем случае это цифра 100. Диапазон_суммирования не заполняем. (Рис. 10).
Получаем искомую сумму, в нашем случае это число 400.
Сумма, если не равно.
Формулы в ячейках, как пример, выглядят вот так:
Рассмотрим на примере, когда нам нужно суммировать все значений, кроме тех, которые находятся напротив ячейки с Пример 1.
Выбираем в Диапазон ячейки, содержащие наш критерий (D3:D27) – Пример 1
В Критерий прописываем: <> Пример 1. Получаем сумму всех значений (диапазон E3:E27), кроме тех, которые стоят напротив Пример 1 (диапазон (D3:D27)).
В Диапазон_суммирования выбираем ячейки, значения которых нужно суммировать (E3:E27). Нажимаем ОК. Получаем нужную нам сумму. (Рис. 11).
Или нам нужно суммировать только значение не равные числу 100.
Тогда в строке Диапазон указываем нужный нам диапазон для суммирования (E3:E27).
В Критерий пишем <>100.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму всех ячеек, кроме тех, где значение равно числу 100. (Рис. 12).
Сумма если больше или равно, Сумма если меньше или равно. СУММЕСЛИ.
Знаки, которые используются: >=, <=.
Сумма если больше или равно.
Формула в ячейке, как пример, выглядит вот так:
Рассмотрим на примере. Нам нужно посчитать сумму значений в столбце (E3:E27). Но суммировать нужно только те числа, которые равны или больше значения числа 500. По стандартному алгоритму вызываем функцию СУММЕСЛИ.
В строке Диапазон выбираем диапазон ячеек, которые нужно просуммировать (E3:E27).
В Критерий пишем:>=500.
Диапазон_суммирования не заполняем.
Нажимаем ОК. Получаем сумму значений равною числу 500, плюс все, что больше этой цифры в заданном диапазоне (E3:E27) (Рис. 13).
Сумма если меньше или равно
Формула в ячейке, как пример, выглядит вот так:
Алгоритм точно такой же, как и в предыдущем примере. Только вместо знак больше, используем знак меньше и равно <=. Получаем сумму всех чисел равную или меньше числу 500, которые находятся в заданном диапазоне (E3:E27).
Обращаю Ваше внимание, что выше приведенные примеры, являются вариантами использования данной функции. Способ использования данной функции должен определяться задачей, которую необходимо решить.
Читайте также: