Difference between revisions of "RU/kb/00000473"
| Line 5: | Line 5: | ||
<section begin=toc /> | <section begin=toc /> | ||
| − | В этой статье приведён обзор различных способов условного | + | В этой статье приведён обзор различных способов условного подсчёта и суммирования содержимого ячеек, в зависимости от результата некоторой проверки. |
=== Условия в диапазонах ячеек === | === Условия в диапазонах ячеек === | ||
| Line 11: | Line 11: | ||
| − | В числовых вычислениях, '''TRUE''' рассматривается как 1, а '''FALSE''' — как 0. Таким образом | + | В числовых вычислениях, '''TRUE''' рассматривается как 1, а '''FALSE''' — как 0. Таким образом введённая формула '''=SUM(D1:D6)''' просто суммирует эти единицы и нули, и даёт нам количество элементов, которые соответствуют одновременно условиям '''красный''' И '''большой'''. |
На самом деле, так как '''TRUE''' и '''FALSE''' оцениваются как 1 и 0, мы не нуждаемся в функции '''AND''' — в '''D1''' мы можем просто записать '''=(A1=“красный”)*(B1=“большой”)''', и скопировать / вставить вниз в ячейки D2: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)''' теперь даст нам суммарный вес. |
[[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''' теперь показывают требуемый вес как ранее. | [[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 === | ||
| − | Функция SUMPRODUCT может использоваться для выполнения вычислений | + | {| align="right" |
| + | |{{RUkbCalcNoExcel}} | ||
| + | |} | ||
| + | Функция SUMPRODUCT может использоваться для выполнения вычислений подсчёта и суммирования в предыдущем разделе, не используя дополнительные столбцы. Необходимо понять формулы массива, чтобы понять её. | ||
Используя пример суммирования из предыдущего раздела, '''A1:A6=“красный”''', '''B1:B6=“большой”''' и '''C1:C6''' можно рассмотреть как 3 отдельных массива, не отображаемых, но внутренне рассчитываемых. | Используя пример суммирования из предыдущего раздела, '''A1:A6=“красный”''', '''B1:B6=“большой”''' и '''C1:C6''' можно рассмотреть как 3 отдельных массива, не отображаемых, но внутренне рассчитываемых. | ||
| Line 28: | Line 31: | ||
'''(A1=“red”)*(B1=“big”)*C1 + (A2=“red”)*(B2=“big”)*C2 + ...''' | '''(A1=“red”)*(B1=“big”)*C1 + (A2=“red”)*(B2=“big”)*C2 + ...''' | ||
| − | Это так-же | + | Это так-же даёт нам суммарный вес, однако, дополнительный столбец не требуется. |
Для переносимости в Excel, используйте формулу '''=SUMPRODUCT((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6)''',поскольку Calc и Excel рассматривают логические результаты по-разному. | Для переносимости в Excel, используйте формулу '''=SUMPRODUCT((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6)''',поскольку Calc и Excel рассматривают логические результаты по-разному. | ||
| Line 37: | Line 40: | ||
=== Функция SUM с формулами массива === | === Функция SUM с формулами массива === | ||
| − | В качестве альтернативы функции '''SUMPRODUCT''' можно использовать функцию SUM. Предыдущий пример был бы записан следующим образом:'''=SUM((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6))'''и | + | В качестве альтернативы функции '''SUMPRODUCT''' можно использовать функцию SUM. Предыдущий пример был бы записан следующим образом:'''=SUM((A1:A6=“красный”)*(B1:B6=“большой”)*C1:C6))'''и введён как формула массива, нажатием {{Key|Ctrl|Shift|Enter}}. Как и в случае с функцией '''SUMPRODUCT''', это работает, перемножая соответствующие элементы массивов вместе и возвращая их сумму. |
| − | === Суммирование элементов | + | === Суммирование элементов с определённым форматированием === |
Функция '''CELL''' возвращает информацию о ячейках, например, что данные отображаются в формате числа или даты и ширину столбца. Не существует функции, которая возвращает цвет или шрифт ячейки. Если не существует самостоятельной формулы, имеющая отношение к цвету или шрифту, то необходимо использовать макрос. | Функция '''CELL''' возвращает информацию о ячейках, например, что данные отображаются в формате числа или даты и ширину столбца. Не существует функции, которая возвращает цвет или шрифт ячейки. Если не существует самостоятельной формулы, имеющая отношение к цвету или шрифту, то необходимо использовать макрос. | ||
=== Суммирование элементов, соответствующих элементам в отдельном списке === | === Суммирование элементов, соответствующих элементам в отдельном списке === | ||
| − | Предположим, что диапазон '''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.]]Чтобы выполнять это вычисление без дополнительного столбца, можно использовать: '''=SUMPRODUCT(NOT(ISERROR(MATCH(B1:B9; F$1:F$5; 0))); C1:C9)''' | [[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:06, 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
