Difference between revisions of "RU/kb/00000473"

From Wiki
Jump to navigationJump to search
(Created page with "<section begin=title /><noinclude>{{DISPLAYTITLE:База Знаний: Функции {{OOoC|1}}. </noinclude>Условный подсчет и суммирование<noincl...")
 
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
<section begin=title /><noinclude>{{DISPLAYTITLE:База Знаний: Функции {{OOoC|1}}. </noinclude>Условный подсчет и суммирование<noinclude>}}</noinclude><section end=title />
+
<section begin=title /><noinclude>{{DISPLAYTITLE:База Знаний: Функции {{OOoC|1}}. </noinclude>Условный подсчёт и суммирование<noinclude>}}</noinclude><section end=title />
 
{{BreadCrumbL10n|RU/kb/module/calc/function/mathematical}}
 
{{BreadCrumbL10n|RU/kb/module/calc/function/mathematical}}
 
__NOTOC__
 
__NOTOC__
Line 5: Line 5:
  
 
<section begin=toc />
 
<section begin=toc />
−
В этой статье приведён обзор различных способов условного подсчета и&nbsp;суммирования содержимого ячеек, в&nbsp;зависимости от&nbsp;результата некоторой проверки.
+
В этой статье приведён обзор различных способов условного подсчёта и&nbsp;суммирования содержимого ячеек, в&nbsp;зависимости от&nbsp;результата некоторой проверки.
  
 
=== Условия в диапазонах ячеек ===
 
=== Условия в диапазонах ячеек ===
−
[[File:Conditions_in_the_ranges_of_cells_ru.png|360px|thumb|right|Рис. 1. Условия в диапазонах ячеек.]]Один простой метод для подсчета или суммирования, использующий несколько условий — ввести условия в&nbsp;новую строку или столбец. Например, если диапазон '''A1:A6''' содержит список цветов, а&nbsp;диапазон '''B1:B6''' — список размеров, то мы можем ввести в&nbsp;ячейку '''D1''' формулу '''<nowiki>=(</nowiki>A1=“красный”)''', которая возвращается '''TRUE''' или '''FALSE''' в&nbsp;зависимости от&nbsp;того, содержит ячейка A1 текст '''красный''' или нет. В&nbsp;качестве альтернативы, мы можем ввести в&nbsp;ячейку '''D1''' формулу '''<nowiki>=</nowiki>AND(A1=“красный”; B1=“большой”)''', которая возвращает '''TRUE''', если ячейка '''A1''' содержит '''красный''' И&nbsp;ячейка '''B1''' содержит '''большой''' и&nbsp;'''FALSE''' в&nbsp;противном случае. Скопируйте и&nbsp;вставьте эту формулу в&nbsp;ячейки '''D2:D6''', и&nbsp;мы имеем диапазон ячеек, которые содержат '''TRUE''', если условия выполняются и&nbsp;'''FALSE''' в&nbsp;противном случае.
+
[[File:Conditions_in_the_ranges_of_cells_ru.png|360px|thumb|right|Рис. 1. Условия в диапазонах ячеек.]]Один простой метод для подсчета или суммирования, использующий несколько условий — ввести условия в&nbsp;новую строку или столбец. Например, если диапазон '''A1:A6''' содержит список цветов, а&nbsp;диапазон '''B1:B6''' — список размеров, то мы можем ввести в&nbsp;ячейку '''D1''' формулу '''=(A1=“красный”)''', которая возвращается '''TRUE''' или '''FALSE''' в&nbsp;зависимости от&nbsp;того, содержит ячейка A1 текст '''красный''' или нет. В&nbsp;качестве альтернативы, мы можем ввести в&nbsp;ячейку '''D1''' формулу '''=AND(A1=“красный”; B1=“большой”)''', которая возвращает '''TRUE''', если ячейка '''A1''' содержит '''красный''' И&nbsp;ячейка '''B1''' содержит '''большой''' и&nbsp;'''FALSE''' в&nbsp;противном случае. Скопируйте и&nbsp;вставьте эту формулу в&nbsp;ячейки '''D2:D6''', и&nbsp;мы имеем диапазон ячеек, которые содержат '''TRUE''', если условия выполняются и&nbsp;'''FALSE''' в&nbsp;противном случае.
  
  
−
В&nbsp;числовых вычислениях, '''TRUE''' рассматривается как 1, а&nbsp;'''FALSE''' — как 0. Таким образом введенная формула '''<nowiki>=</nowiki>SUM(D1:D6)''' просто суммирует эти единицы и&nbsp;нули, и&nbsp;дает нам количество элементов, которые соответствуют одновременно условиям '''красный''' И&nbsp;'''большой'''.
+
В&nbsp;числовых вычислениях, '''TRUE''' рассматривается как 1, а&nbsp;'''FALSE''' — как 0. Таким образом введённая формула '''=SUM(D1:D6)''' просто суммирует эти единицы и&nbsp;нули, и&nbsp;даёт нам количество элементов, которые соответствуют одновременно условиям '''красный''' И&nbsp;'''большой'''.
  
−
На самом деле, так как '''TRUE''' и&nbsp;'''FALSE''' оцениваются как 1&nbsp;и 0, мы не&nbsp;нуждаемся в&nbsp;функции '''AND''' — в&nbsp;'''D1''' мы можем просто записать '''<nowiki>=(</nowiki>A1=“красный”)*(B1=“большой”)''', и&nbsp;скопировать&nbsp;/&nbsp;вставить вниз в&nbsp;ячейки D2:D6.
+
На самом деле, так как '''TRUE''' и&nbsp;'''FALSE''' оцениваются как 1&nbsp;и 0, мы не&nbsp;нуждаемся в&nbsp;функции '''AND''' — в&nbsp;'''D1''' мы можем просто записать '''=(A1=“красный”)*(B1=“большой”)''', и&nbsp;скопировать&nbsp;/&nbsp;вставить вниз в&nbsp;ячейки D2:D6.
  
−
Теперь пойдем дальше. Предположим, что диапазон '''C1:C6''' содержит список весов этих элементов, и&nbsp;мы желаем знать суммарную массу для всех больших и&nbsp;красных элементов. В&nbsp;ячейке '''D1''' мы запишем '''<nowiki>=(</nowiki>A1=“красный”)*(B1=“большой”)*C1''', и&nbsp;скопируем&nbsp;/&nbsp;вставим эту формулу вниз в&nbsp;ячейки '''D2:D6'''. Ячейка '''D1''' будет содержать вес в&nbsp;'''C1''', если условия будут выполняться (и ноль в&nbsp;противном случае) и&nbsp;так далее для ячеек '''D2:D6''', следовательно '''<nowiki>=</nowiki>SUM(D1:D6)''' теперь даст нам суммарный вес.
+
Теперь пойдём дальше. Предположим, что диапазон '''C1:C6''' содержит список весов этих элементов, и&nbsp;мы желаем знать суммарную массу для всех больших и&nbsp;красных элементов. В&nbsp;ячейке '''D1''' мы запишем '''=(A1=“красный”)*(B1=“большой”)*C1''', и&nbsp;скопируем&nbsp;/&nbsp;вставим эту формулу вниз в&nbsp;ячейки '''D2:D6'''. Ячейка '''D1''' будет содержать вес в&nbsp;'''C1''', если условия будут выполняться (и ноль в&nbsp;противном случае) и&nbsp;так далее для ячеек '''D2:D6''', следовательно '''=SUM(D1:D6)''' теперь даст нам суммарный вес.
  
−
[[File:Conditions_in_the_ranges_of_cells-2 ru.png|360px|thumb|right|Рис. 2: Условия в&nbsp;диапазонах ячеек.]]В&nbsp;качестве альтернативы, можно заполнить диапазон '''D1:D6''' формулой массива. В&nbsp;ячейке '''D1''', напишите '''<nowiki>=(</nowiki>A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6''', и&nbsp;введите, нажав <tt><nowiki>[</nowiki>Ctrl<nowiki>]+[</nowiki>Shift<nowiki>]+[</nowiki>Enter]</tt>. Все ячейки в&nbsp;диапазоне '''D1:D6''' теперь показывают требуемый вес как ранее.
+
[[File:Conditions_in_the_ranges_of_cells-2 ru.png|360px|thumb|right|Рис. 2: Условия в&nbsp;диапазонах ячеек.]]В&nbsp;качестве альтернативы, можно заполнить диапазон '''D1:D6''' формулой массива. В&nbsp;ячейке '''D1''', напишите '''=(A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6''', и&nbsp;введите, нажав {{Key|Ctrl|Shift|Enter}}. Все ячейки в&nbsp;диапазоне '''D1:D6''' теперь показывают требуемый вес как ранее.
  
 
=== Функция SUMPRODUCT ===
 
=== Функция SUMPRODUCT ===
−
Функция SUMPRODUCT может использоваться для выполнения вычислений подсчета и&nbsp;суммирования в&nbsp;предыдущем разделе, не&nbsp;используя дополнительные столбцы. Необходимо понять формулы массива, чтобы понять её.
+
{| align="right"
 +
|{{RUkbCalcNoExcel|1}}
 +
|}
 +
Функция SUMPRODUCT может использоваться для выполнения вычислений подсчёта и&nbsp;суммирования в&nbsp;предыдущем разделе, не&nbsp;используя дополнительные столбцы. Необходимо понять формулы массива, чтобы понять её.
  
 
Используя пример суммирования из&nbsp;предыдущего раздела, '''A1:A6=“красный”''', '''B1:B6=“большой”''' и&nbsp;'''C1:C6''' можно рассмотреть как 3&nbsp;отдельных массива, не&nbsp;отображаемых, но&nbsp;внутренне рассчитываемых.
 
Используя пример суммирования из&nbsp;предыдущего раздела, '''A1:A6=“красный”''', '''B1:B6=“большой”''' и&nbsp;'''C1:C6''' можно рассмотреть как 3&nbsp;отдельных массива, не&nbsp;отображаемых, но&nbsp;внутренне рассчитываемых.
  
−
'''<nowiki>=</nowiki>SUMPRODUCT(A1:A6=“красный”; B1:B6=“большой”; C1:C6)''', перемножит соответствующие элементы массивов вместе и&nbsp;возвратит их сумму, то&nbsp;есть:
+
'''=SUMPRODUCT(A1:A6=“красный”; B1:B6=“большой”; C1:C6)''', перемножит соответствующие элементы массивов вместе и&nbsp;возвратит их сумму, то&nbsp;есть:
  
 
'''(A1=“red”)*(B1=“big”)*C1 + (A2=“red”)*(B2=“big”)*C2 + ...'''
 
'''(A1=“red”)*(B1=“big”)*C1 + (A2=“red”)*(B2=“big”)*C2 + ...'''
  
−
Это так-же дает нам суммарный вес, однако, дополнительный столбец не&nbsp;требуется.
+
Это так-же даёт нам суммарный вес, однако, дополнительный столбец не&nbsp;требуется.
  
−
Для переносимости в&nbsp;Excel, используйте формулу '''<nowiki>=</nowiki>SUMPRODUCT((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6)''',поскольку Calc и&nbsp;Excel рассматривают логические результаты по-разному.
+
Для переносимости в&nbsp;Excel, используйте формулу '''=SUMPRODUCT((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6)''',поскольку Calc и&nbsp;Excel рассматривают логические результаты по-разному.
  
−
Заметьте, что формулы с&nbsp;использованием функции '''SUMPRODUCT''' просто вводятся нажатием клавиши <tt><nowiki>[</nowiki>Enter]</tt> — они не&nbsp;требуют нажатия <tt><nowiki>[</nowiki>Ctrl<nowiki>]+[</nowiki>Shift<nowiki>]+[</nowiki>Enter]</tt> даже при том, что связанны с&nbsp;массивами.
+
Заметьте, что формулы с&nbsp;использованием функции '''SUMPRODUCT''' просто вводятся нажатием клавиши {{Key|Enter}} — они не&nbsp;требуют нажатия {{Key|Ctrl|Shift|Enter}} даже при том, что связанны с&nbsp;массивами.
  
 
[[File:Function_SUMPRODUCT_ru.png|360px|thumb|right|Рис. 3: SUMPRODUCT.]]Кроме того, знайте, что вычисления, использующие очень большие массивы берут много компьютерного процессорного времени, и&nbsp;могут сильно замедлить обработку электронных таблиц.
 
[[File:Function_SUMPRODUCT_ru.png|360px|thumb|right|Рис. 3: SUMPRODUCT.]]Кроме того, знайте, что вычисления, использующие очень большие массивы берут много компьютерного процессорного времени, и&nbsp;могут сильно замедлить обработку электронных таблиц.
  
 
=== Функция SUM с формулами массива ===
 
=== Функция SUM с формулами массива ===
−
В&nbsp;качестве альтернативы функции '''SUMPRODUCT''' можно использовать функцию SUM. Предыдущий пример был бы записан следующим образом:'''<nowiki>=</nowiki>SUM((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6))'''и введен как формула массива, нажатием <tt><nowiki>[</nowiki>Ctrl<nowiki>]+[</nowiki>Shift<nowiki>]+[</nowiki>Enter]</tt>. Как и&nbsp;в&nbsp;случае с&nbsp;функцией '''SUMPRODUCT''', это работает, перемножая соответствующие элементы массивов вместе и&nbsp;возвращая их сумму.
+
В&nbsp;качестве альтернативы функции '''SUMPRODUCT''' можно использовать функцию SUM. Предыдущий пример был бы записан следующим образом:'''=SUM((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6))'''и введён как формула массива, нажатием {{Key|Ctrl|Shift|Enter}}. Как и&nbsp;в&nbsp;случае с&nbsp;функцией '''SUMPRODUCT''', это работает, перемножая соответствующие элементы массивов вместе и&nbsp;возвращая их сумму.
  
−
=== Суммирование элементов с определенным форматированием ===
+
=== Суммирование элементов с определённым форматированием ===
 
Функция '''CELL''' возвращает информацию о&nbsp;ячейках, например, что данные отображаются в&nbsp;формате числа или даты и&nbsp;ширину столбца. Не&nbsp;существует функции, которая возвращает цвет или шрифт ячейки. Если не&nbsp;существует самостоятельной формулы, имеющая отношение к&nbsp;цвету или шрифту, то необходимо использовать макрос.
 
Функция '''CELL''' возвращает информацию о&nbsp;ячейках, например, что данные отображаются в&nbsp;формате числа или даты и&nbsp;ширину столбца. Не&nbsp;существует функции, которая возвращает цвет или шрифт ячейки. Если не&nbsp;существует самостоятельной формулы, имеющая отношение к&nbsp;цвету или шрифту, то необходимо использовать макрос.
  
 
=== Суммирование элементов, соответствующих элементам в отдельном списке ===
 
=== Суммирование элементов, соответствующих элементам в отдельном списке ===
−
Предположим, что диапазон '''A1:A9''' содержит список дат, диапазон '''B1:B9''' — телефонные номера, а&nbsp;диапазон '''C1:C9''' — затраты на&nbsp;телефонные звонки. '''F1:F5''' — список определенных телефонных номеров, и&nbsp;необходимо узнать общую стоимость звонков по&nbsp;этим номерам.
+
Предположим, что диапазон '''A1:A9''' содержит список дат, диапазон '''B1:B9''' — телефонные номера, а&nbsp;диапазон '''C1:C9''' — затраты на&nbsp;телефонные звонки. '''F1:F5''' — список определённых телефонных номеров, и&nbsp;необходимо узнать общую стоимость звонков по&nbsp;этим номерам.
  
−
В&nbsp;ячейке D1 введем:'''<nowiki>=</nowiki>ISNUMBER(MATCH(B1; F$1:F$5; 0))*C1'''и скопируем и&nbsp;вставим в&nbsp;ячейки '''D2:D9'''. '''<nowiki>=</nowiki>SUM(D1:D9)''' теперь дает общую стоимость звонков.
+
В&nbsp;ячейке D1 введём:'''=ISNUMBER(MATCH(B1; F$1:F$5; 0))*C1'''и скопируем и&nbsp;вставим в&nbsp;ячейки '''D2:D9'''. '''=SUM(D1:D9)''' теперь даёт общую стоимость звонков.
  
−
[[File:Function_ISNUMBER_ru.png|360px|thumb|right|Рис. 4: ISNUMBER.]]Чтобы выполнять это вычисление без&nbsp;дополнительного столбца, можно использовать: '''<nowiki>=</nowiki>SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)'''
+
[[File:Function_ISNUMBER_ru.png|360px|thumb|right|Рис. 4: ISNUMBER.]]Чтобы выполнять это вычисление без&nbsp;дополнительного столбца, можно использовать: '''=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)'''
  
−
Мы обсуждали выше, как может быть важно проверить параметры настройки Calc<nowiki>; это — хороший пример — если пользователь имеет включенные регулярные выражения (по умолчанию), номер телефона, записанный как (720)&nbsp;528-1700 интерпретируется как регулярное выражение и&nbsp;может соответствовать записям иным, чем (720)&nbsp;528-1700.</nowiki>
+
Мы обсуждали выше, как может быть важно проверить параметры настройки Calc; это — хороший пример — если пользователь имеет включённые регулярные выражения (по умолчанию), номер телефона, записанный как (720)&nbsp;528-1700 интерпретируется как регулярное выражение и&nbsp;может соответствовать записям иным, чем (720)&nbsp;528-1700.
 
<section end=toc />
 
<section end=toc />
  

Latest revision as of 09:12, 4 October 2011



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

Условия в диапазонах ячеек

Рис. 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. Таким образом введённая формула =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) теперь даст нам суммарный вес.

Рис. 2: Условия в диапазонах ячеек.

В качестве альтернативы, можно заполнить диапазон D1:D6 формулой массива. В ячейке D1, напишите =(A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6, и введите, нажав  Ctrl  +  ⇧ Shift  +  ↵ Enter . Все ячейки в диапазоне D1:D6 теперь показывают требуемый вес как ранее.

Функция SUMPRODUCT

70px right 

Функция 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  даже при том, что связанны с массивами.

Рис. 3: SUMPRODUCT.

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

Функция 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) теперь даёт общую стоимость звонков.

Рис. 4: ISNUMBER.

Чтобы выполнять это вычисление без дополнительного столбца, можно использовать: =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







К началу страницы

Retrieved from "https://wiki.harlamenkov.ru/index.php?title=RU/kb/00000473&oldid=18347"