Difference between revisions of "RU/kb/00000473"
(Created page with "<section begin=title /><noinclude>{{DISPLAYTITLE:База Знаний: Функции {{OOoC|1}}. </noinclude>Условный подсчет и суммирование<noincl...") |
|||
| Line 8: | Line 8: | ||
=== Условия в диапазонах ячеек === | === Условия в диапазонах ячеек === | ||
| − | [[File:Conditions_in_the_ranges_of_cells_ru.png|360px|thumb|right|Рис. 1. Условия в диапазонах ячеек.]]Один простой метод для подсчета или суммирования, использующий несколько условий — ввести условия в новую строку или столбец. Например, если диапазон '''A1:A6''' содержит список цветов, а диапазон '''B1:B6''' — список размеров, то мы можем ввести в ячейку '''D1''' формулу ''' | + | [[File:Conditions_in_the_ranges_of_cells_ru.png|360px|thumb|right|Рис. 1. Условия в диапазонах ячеек.]]Один простой метод для подсчета или суммирования, использующий несколько условий — ввести условия в новую строку или столбец. Например, если диапазон '''A1:A6''' содержит список цветов, а диапазон '''B1:B6''' — список размеров, то мы можем ввести в ячейку '''D1''' формулу '''=(A1=“красный”)''', которая возвращается '''TRUE''' или '''FALSE''' в зависимости от того, содержит ячейка A1 текст '''красный''' или нет. В качестве альтернативы, мы можем ввести в ячейку '''D1''' формулу '''=AND(A1=“красный”; B1=“большой”)''', которая возвращает '''TRUE''', если ячейка '''A1''' содержит '''красный''' И ячейка '''B1''' содержит '''большой''' и '''FALSE''' в противном случае. Скопируйте и вставьте эту формулу в ячейки '''D2:D6''', и мы имеем диапазон ячеек, которые содержат '''TRUE''', если условия выполняются и '''FALSE''' в противном случае. |
| − | В числовых вычислениях, '''TRUE''' рассматривается как 1, а '''FALSE''' — как 0. Таким образом введенная формула ''' | + | В числовых вычислениях, '''TRUE''' рассматривается как 1, а '''FALSE''' — как 0. Таким образом введенная формула '''=SUM(D1:D6)''' просто суммирует эти единицы и нули, и дает нам количество элементов, которые соответствуют одновременно условиям '''красный''' И '''большой'''. |
| − | На самом деле, так как '''TRUE''' и '''FALSE''' оцениваются как 1 и 0, мы не нуждаемся в функции '''AND''' — в '''D1''' мы можем просто записать ''' | + | На самом деле, так как '''TRUE''' и '''FALSE''' оцениваются как 1 и 0, мы не нуждаемся в функции '''AND''' — в '''D1''' мы можем просто записать '''=(A1=“красный”)*(B1=“большой”)''', и скопировать / вставить вниз в ячейки D2:D6. |
| − | Теперь пойдем дальше. Предположим, что диапазон '''C1:C6''' содержит список весов этих элементов, и мы желаем знать суммарную массу для всех больших и красных элементов. В ячейке '''D1''' мы запишем ''' | + | Теперь пойдем дальше. Предположим, что диапазон '''C1:C6''' содержит список весов этих элементов, и мы желаем знать суммарную массу для всех больших и красных элементов. В ячейке '''D1''' мы запишем '''=(A1=“красный”)*(B1=“большой”)*C1''', и скопируем / вставим эту формулу вниз в ячейки '''D2:D6'''. Ячейка '''D1''' будет содержать вес в '''C1''', если условия будут выполняться (и ноль в противном случае) и так далее для ячеек '''D2:D6''', следовательно '''=SUM(D1:D6)''' теперь даст нам суммарный вес. |
| − | [[File:Conditions_in_the_ranges_of_cells-2 ru.png|360px|thumb|right|Рис. 2: Условия в диапазонах ячеек.]]В качестве альтернативы, можно заполнить диапазон '''D1:D6''' формулой массива. В ячейке '''D1''', напишите ''' | + | [[File:Conditions_in_the_ranges_of_cells-2 ru.png|360px|thumb|right|Рис. 2: Условия в диапазонах ячеек.]]В качестве альтернативы, можно заполнить диапазон '''D1:D6''' формулой массива. В ячейке '''D1''', напишите '''=(A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6''', и введите, нажав {{Key|Ctrl|Shift|Enter}}. Все ячейки в диапазоне '''D1:D6''' теперь показывают требуемый вес как ранее. |
=== Функция SUMPRODUCT === | === Функция SUMPRODUCT === | ||
| Line 24: | Line 24: | ||
Используя пример суммирования из предыдущего раздела, '''A1:A6=“красный”''', '''B1:B6=“большой”''' и '''C1:C6''' можно рассмотреть как 3 отдельных массива, не отображаемых, но внутренне рассчитываемых. | Используя пример суммирования из предыдущего раздела, '''A1:A6=“красный”''', '''B1:B6=“большой”''' и '''C1:C6''' можно рассмотреть как 3 отдельных массива, не отображаемых, но внутренне рассчитываемых. | ||
| − | ''' | + | '''=SUMPRODUCT(A1:A6=“красный”; B1:B6=“большой”; C1:C6)''', перемножит соответствующие элементы массивов вместе и возвратит их сумму, то есть: |
'''(A1=“red”)*(B1=“big”)*C1 + (A2=“red”)*(B2=“big”)*C2 + ...''' | '''(A1=“red”)*(B1=“big”)*C1 + (A2=“red”)*(B2=“big”)*C2 + ...''' | ||
| Line 30: | Line 30: | ||
Это так-же дает нам суммарный вес, однако, дополнительный столбец не требуется. | Это так-же дает нам суммарный вес, однако, дополнительный столбец не требуется. | ||
| − | Для переносимости в Excel, используйте формулу ''' | + | Для переносимости в Excel, используйте формулу '''=SUMPRODUCT((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6)''',поскольку Calc и Excel рассматривают логические результаты по-разному. |
| − | Заметьте, что формулы с использованием функции '''SUMPRODUCT''' просто вводятся нажатием клавиши | + | Заметьте, что формулы с использованием функции '''SUMPRODUCT''' просто вводятся нажатием клавиши {{Key|Enter}} — они не требуют нажатия {{Key|Ctrl|Shift|Enter}} даже при том, что связанны с массивами. |
[[File:Function_SUMPRODUCT_ru.png|360px|thumb|right|Рис. 3: SUMPRODUCT.]]Кроме того, знайте, что вычисления, использующие очень большие массивы берут много компьютерного процессорного времени, и могут сильно замедлить обработку электронных таблиц. | [[File:Function_SUMPRODUCT_ru.png|360px|thumb|right|Рис. 3: SUMPRODUCT.]]Кроме того, знайте, что вычисления, использующие очень большие массивы берут много компьютерного процессорного времени, и могут сильно замедлить обработку электронных таблиц. | ||
=== Функция SUM с формулами массива === | === Функция SUM с формулами массива === | ||
| − | В качестве альтернативы функции '''SUMPRODUCT''' можно использовать функцию SUM. Предыдущий пример был бы записан следующим образом:''' | + | В качестве альтернативы функции '''SUMPRODUCT''' можно использовать функцию SUM. Предыдущий пример был бы записан следующим образом:'''=SUM((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6))'''и введен как формула массива, нажатием {{Key|Ctrl|Shift|Enter}}. Как и в случае с функцией '''SUMPRODUCT''', это работает, перемножая соответствующие элементы массивов вместе и возвращая их сумму. |
=== Суммирование элементов с определенным форматированием === | === Суммирование элементов с определенным форматированием === | ||
| Line 45: | Line 45: | ||
Предположим, что диапазон '''A1:A9''' содержит список дат, диапазон '''B1:B9''' — телефонные номера, а диапазон '''C1:C9''' — затраты на телефонные звонки. '''F1:F5''' — список определенных телефонных номеров, и необходимо узнать общую стоимость звонков по этим номерам. | Предположим, что диапазон '''A1:A9''' содержит список дат, диапазон '''B1:B9''' — телефонные номера, а диапазон '''C1:C9''' — затраты на телефонные звонки. '''F1:F5''' — список определенных телефонных номеров, и необходимо узнать общую стоимость звонков по этим номерам. | ||
| − | В ячейке D1 введем:''' | + | В ячейке D1 введем:'''=ISNUMBER(MATCH(B1; F$1:F$5; 0))*C1'''и скопируем и вставим в ячейки '''D2:D9'''. '''=SUM(D1:D9)''' теперь дает общую стоимость звонков. |
| − | [[File:Function_ISNUMBER_ru.png|360px|thumb|right|Рис. 4: ISNUMBER.]]Чтобы выполнять это вычисление без дополнительного столбца, можно использовать: ''' | + | [[File:Function_ISNUMBER_ru.png|360px|thumb|right|Рис. 4: ISNUMBER.]]Чтобы выполнять это вычисление без дополнительного столбца, можно использовать: '''=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)''' |
| − | Мы обсуждали выше, как может быть важно проверить параметры настройки Calc | + | Мы обсуждали выше, как может быть важно проверить параметры настройки Calc; это — хороший пример — если пользователь имеет включенные регулярные выражения (по умолчанию), номер телефона, записанный как (720) 528-1700 интерпретируется как регулярное выражение и может соответствовать записям иным, чем (720) 528-1700. |
<section end=toc /> | <section end=toc /> | ||
Revision as of 14:00, 23 September 2011
В этой статье приведён обзор различных способов условного подсчета и суммирования содержимого ячеек, в зависимости от результата некоторой проверки.
Условия в диапазонах ячеек
Один простой метод для подсчета или суммирования, использующий несколько условий — ввести условия в новую строку или столбец. Например, если диапазон A1:A6 содержит список цветов, а диапазон B1:B6 — список размеров, то мы можем ввести в ячейку D1 формулу =(A1=“красный”), которая возвращается TRUE или FALSE в зависимости от того, содержит ячейка A1 текст красный или нет. В качестве альтернативы, мы можем ввести в ячейку D1 формулу =AND(A1=“красный”; B1=“большой”), которая возвращает TRUE, если ячейка A1 содержит красный И ячейка B1 содержит большой и FALSE в противном случае. Скопируйте и вставьте эту формулу в ячейки D2:D6, и мы имеем диапазон ячеек, которые содержат TRUE, если условия выполняются и FALSE в противном случае.
В числовых вычислениях, TRUE рассматривается как 1, а FALSE — как 0. Таким образом введенная формула =SUM(D1:D6) просто суммирует эти единицы и нули, и дает нам количество элементов, которые соответствуют одновременно условиям красный И большой.
На самом деле, так как TRUE и FALSE оцениваются как 1 и 0, мы не нуждаемся в функции AND — в D1 мы можем просто записать =(A1=“красный”)*(B1=“большой”), и скопировать / вставить вниз в ячейки D2:D6.
Теперь пойдем дальше. Предположим, что диапазон C1:C6 содержит список весов этих элементов, и мы желаем знать суммарную массу для всех больших и красных элементов. В ячейке D1 мы запишем =(A1=“красный”)*(B1=“большой”)*C1, и скопируем / вставим эту формулу вниз в ячейки D2:D6. Ячейка D1 будет содержать вес в C1, если условия будут выполняться (и ноль в противном случае) и так далее для ячеек D2:D6, следовательно =SUM(D1:D6) теперь даст нам суммарный вес.
В качестве альтернативы, можно заполнить диапазон D1:D6 формулой массива. В ячейке D1, напишите =(A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6, и введите, нажав Ctrl + ⇧ Shift + ↵ Enter . Все ячейки в диапазоне D1:D6 теперь показывают требуемый вес как ранее.
Функция SUMPRODUCT
Функция SUMPRODUCT может использоваться для выполнения вычислений подсчета и суммирования в предыдущем разделе, не используя дополнительные столбцы. Необходимо понять формулы массива, чтобы понять её.
Используя пример суммирования из предыдущего раздела, A1:A6=“красный”, B1:B6=“большой” и C1:C6 можно рассмотреть как 3 отдельных массива, не отображаемых, но внутренне рассчитываемых.
=SUMPRODUCT(A1:A6=“красный”; B1:B6=“большой”; C1:C6), перемножит соответствующие элементы массивов вместе и возвратит их сумму, то есть:
(A1=“red”)*(B1=“big”)*C1 + (A2=“red”)*(B2=“big”)*C2 + ...
Это так-же дает нам суммарный вес, однако, дополнительный столбец не требуется.
Для переносимости в Excel, используйте формулу =SUMPRODUCT((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6),поскольку Calc и Excel рассматривают логические результаты по-разному.
Заметьте, что формулы с использованием функции SUMPRODUCT просто вводятся нажатием клавиши ↵ Enter — они не требуют нажатия Ctrl + ⇧ Shift + ↵ Enter даже при том, что связанны с массивами.
Кроме того, знайте, что вычисления, использующие очень большие массивы берут много компьютерного процессорного времени, и могут сильно замедлить обработку электронных таблиц.
Функция SUM с формулами массива
В качестве альтернативы функции SUMPRODUCT можно использовать функцию SUM. Предыдущий пример был бы записан следующим образом:=SUM((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6))и введен как формула массива, нажатием Ctrl + ⇧ Shift + ↵ Enter . Как и в случае с функцией SUMPRODUCT, это работает, перемножая соответствующие элементы массивов вместе и возвращая их сумму.
Суммирование элементов с определенным форматированием
Функция CELL возвращает информацию о ячейках, например, что данные отображаются в формате числа или даты и ширину столбца. Не существует функции, которая возвращает цвет или шрифт ячейки. Если не существует самостоятельной формулы, имеющая отношение к цвету или шрифту, то необходимо использовать макрос.
Суммирование элементов, соответствующих элементам в отдельном списке
Предположим, что диапазон A1:A9 содержит список дат, диапазон B1:B9 — телефонные номера, а диапазон C1:C9 — затраты на телефонные звонки. F1:F5 — список определенных телефонных номеров, и необходимо узнать общую стоимость звонков по этим номерам.
В ячейке D1 введем:=ISNUMBER(MATCH(B1; F$1:F$5; 0))*C1и скопируем и вставим в ячейки D2:D9. =SUM(D1:D9) теперь дает общую стоимость звонков.
Чтобы выполнять это вычисление без дополнительного столбца, можно использовать: =SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)
Мы обсуждали выше, как может быть важно проверить параметры настройки Calc; это — хороший пример — если пользователь имеет включенные регулярные выражения (по умолчанию), номер телефона, записанный как (720) 528-1700 интерпретируется как регулярное выражение и может соответствовать записям иным, чем (720) 528-1700.
InfraOffice.pro 3.1.x
