Стандарт SQL поддерживает четыре оконные функции, которые служат для ранжирования. Это ROW_NUMBER, NTILE, RANK и DENSE_RANK. В стандарте первые две считаются относящимися к одной категории, а вторые две - ко второй. Это связано с различиями в отношении детерминизма. Подробнее я расскажу в процессе рассказа об отдельных функциях.
Функции ранжирования появились еще в SQL Server 2005. Тем не менее я покажу альтернативные, основанные на наборах методы для получения того же результата. Я сделаю это по двум причинам: во-первых, это может быть интересным, а во-вторых, я верю, что это помогает лучше понять нюансы работы функций. Тем не менее имейте в виду, что на практике я настоятельно рекомендую придерживаться оконных функций, потому что они и проще, и намного эффективнее, чем альтернативные решения. Подробнее об оптимизации мы поговорим позже.
Все четыре функции ранжирования поддерживают необязательное предложение секционирования и обязательное предложение упорядочения окна. Если предложение секционирования окна отсутствует, весь результирующий набор базового запроса (вспомните о входных данных этапа SELECT) считается одной секцией. Что касается предложения упорядочения окна, то оно обеспечивает упорядочение при вычислениях. Понятно, что ранжирование строк без определения упорядочения вряд ли имеет смысл. В ранжирующих оконных функциях упорядочение служит другой цели по сравнению с функциями, поддерживающими кадрирование, такими как агрегирующие оконные функции. В первом случае упорядочение имеет логический смысл для самих вычислений, а во втором - упорядочение связано с кадрированием, то есть служит целям фильтрации.
Функция ROW_NUMBER
Эта функция вычисляет последовательные номера строк, начиная с 1, в соответствующей секции окна и в соответствии с заданным упорядочением окна. Посмотрите на пример запроса:
SELECT orderid, val, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum FROM Sales.OrderValues;
Вот сокращенный результат выполнения этого запроса:
Этот запрос кажется тривиальным, но здесь есть несколько моментов, о которых стоит сказать.
Так как в запросе нет предложения представления ORDER BY, упорядочение представления не гарантируется. Поэтому упорядочение представления здесь нужно считать произвольным. На практике SQL Server оптимизирует запрос с учетом отсутствия предложения ORDER BY, поэтому строки могут возвращаться в любом порядке. Если нужно гарантировать упорядочение представления, нужно не забыть добавить предложение представления ORDER BY. Если нужно, чтобы упорядочение представления выполнялось на основе номера строки, можно использовать псевдоним, назначенный в процессе вычисления предложения представления ORDER BY примерно так:
SELECT orderid, val, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum FROM Sales.OrderValues ORDER BY rownum;
Считайте вычисление номеров строк генерацией еще одного атрибута в результирующем наборе запроса. Естественно, если хочется, можно получить упорядочение представления, которое отличается от упорядочения окна, как в следующем запросе:
SELECT orderid, val, ROW_NUMBER() OVER(ORDER BY orderid) AS rownum FROM Sales.OrderValues ORDER BY val DESC;
Можно использовать оконный агрегат COUNT для создания операции которая логически эквивалентна функции ROW_NUMBER. Допустим, WPO - определение секционирования и упорядочения окна, примененное в функции ROW_NUMBER. Тогда ROW_NUMBER OVER WPO эквивалентно COUNT(*) OVER(WPO ROWS UNBOUNDED PRECEDING). Например, следующее эквивалентно запросу из листинга предыдущего примера:
SELECT orderid, val, COUNT(*) OVER(ORDER BY orderid ROWS UNBOUNDED PRECEDING) AS rownum FROM Sales.OrderValues;
Как я говорил, это хорошая тренировка - попытаться и создать альтернативные варианты вместо использования оконных функций, и не важно, что эти варианты сложнее и менее эффективные. Если уж мы говорим о функции ROW_NUMBER, то вот основанная на наборах стандартная альтернатива запросу, в которой оконные функции не используются:
В этом решении используется агрегат COUNT во вложенном запросе для определения, у скольких строк значение упорядочения (в нашем случае orderid) меньше или равно текущему. Это просто, если у вас уникальное упорядочение, основанное на одном атрибуте. Но все сильно усложняется, если упорядочение неуникально, что я и продемонстрирую при обсуждении детерминизма.
Детерминизм
Если упорядочение окна уникально, вычисление ROW_NUMBER является детерминистическим. Это означает, что у запроса есть только одни правильный результат, то есть если не менять входные данные, вы гарантировано будете получать повторяющиеся результаты. Но если упорядочение окна не уникально, вычисление становится недетерминистическим. Функция ROW_NUMBER генерирует уникальные номера строк в рамках секции, даже для строк с одинаковыми значениями в атрибутах упорядочения окна. В качестве примера посмотрите на следующий запрос:
SELECT orderid, orderdate, val, ROW_NUMBER() OVER(ORDER BY orderdate DESC) AS rownum FROM Sales.OrderValues;
Так как атрибут orderdate не уникальный, упорядочение строк с одинаковым значением orderdate следует считать произвольным. В принципе существует более одного корректного результата этого запроса. В качестве примера возьмем четыре строки с датой заказа 2008-05-06. Любой порядок строк с номерами от 1 до 4 считается правильным. Поэтому если вы выполните запрос снова, то в принципе можете получить другой порядок - сейчас не будем оценивать вероятность такого события, обусловленную особенностями реализации SQL Server.
Если нужно гарантировать повторяемость результатов, нужно сделать запрос детерминистическим. Это можно сделать, добавив дополнительный параметр в определение упорядочения окна, чтобы обеспечить уникальность в рамках секции. К примеру в следующем запросе уникальность упорядочения в окне достигается за счет добавления в список orderid DESC:
SELECT orderid, orderdate, val, ROW_NUMBER() OVER(ORDER BY orderdate DESC, orderid DESC) AS rownum FROM Sales.OrderValues;
При использовании оконных функций детерминизм вычисления номеров страниц реализуется просто. Сделать то же, не прибегая к оконным функция сложнее, но вполне реально:
Но вернемся к функции ROW_NUMBER: как мы выдели, ее можно использовать для создания недетерминистических вычислений при использовании неуникального упорядочения. Таким образом, недетерминизм разрешен, но странно то, что он не разрешен полностью. Я имею в виду то, что предложение ORDER BY обязательно. Но что, если вы хотите просто получить в секции уникальные номера строк не обязательно в каком-то определенном порядке? Можно создать такой запрос:
SELECT orderid, orderdate, val, ROW_NUMBER() OVER() AS rownum FROM Sales.OrderValues;
Но, как уже говорилось, предложение ORDER BY в функциях ранжирования является обязательным:
The function "ROW_NUMBER" must have an OVER clause with ORDER BY.
Вы можете поумничать и определить константу в списке ORDER BY:
SELECT orderid, orderdate, val, ROW_NUMBER() OVER(ORDER BY NULL) AS rownum FROM Sales.OrderValues;
Ho SQL Server вернет такую ошибку:
Windowed functions and NEXT VALUE FOR functions do not support constants as ORDER BY clause expressions.
Однако решение есть, и я вскоре его приведу.
Предложение OVER и последовательности
Вам наверное интересно, что делает функция NEXT VALUE FOR в сообщении об ошибке при попытке применить константу в предложении OVER. Это связано с расширенной поддержкой последовательностей в SQL Server 2012 по сравнению со стандартом SQL. Последовательность SQL Server - это объект базы данных, который служит для автогенерации номеров, которые часто используются как ключи. Вот пример кода создания объекта-последовательности dbo.Seq1:
CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;
Для получения очередного значения последовательности применяется функция NEXT VALUE FOR. Вот пример:
SELECT NEXT VALUE FOR dbo.Seq1;
Эту функцию можно вызывать в запросе, возвращающем несколько строк:
SELECT orderid, orderdate, val, NEXT VALUE FOR dbo.Seq1 AS seqval FROM Sales.OrderValues;
Это стандартный код. В SQL Server 2012 возможности функции NEXT VALUE FOR расширены и позволяют определять упорядочение в предложении OVER подобно тому, как это делается в оконных функциях. Таким образом можно гарантировать, что значения последовательности отражают желаемое упорядочение. Вот пример использования расширенной функции NEXT VALUE FOR:
SELECT orderid, orderdate, val, NEXT VALUE FOR dbo.Seq1 OVER(ORDER BY orderdate, orderid) AS seqval FROM Sales.OrderValues;
Аналогичный принцип детерминизма применим к предложению OVER в функции NEXT VALUE FOR, как это происходит в оконных функциях.
Итак, нет прямого способа назначить номера строкам без упорядочения, но, по-видимому, SQL Server не смущает вложенный запрос, возвращающий константу в качестве элемента упорядочения окна. Вот пример:
SELECT orderid, orderdate, val, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM Sales.OrderValues;
Функция NTILE
Эта функция позволяет разбивать строки в секции окна на примерно равные по размеру подгруппы (tiles) в соответствии с заданным числом подгрупп и упорядочением окна. Допустим, что нужно разбить строки представления OrderValues на 10 подгрупп одинакового размера на основе упорядочения по val. В представлении 830 строк, поэтому требуется 10 подгрупп, размер каждой будет составлять 83 (830 деленное на 10). Поэтому первым 83 строкам (одной десятой части), упорядоченным по val, будет назначен номер группы 1, следующим 83 строкам - номер подгруппы 2 и т. д. Вот запрос, вычисляющий номера как строк, так и подгрупп:
SELECT orderid, val, ROW_NUMBER() OVER(ORDER BY val) AS rownum, NTILE(10) OVER(ORDER BY val) AS tile FROM Sales.OrderValues;
Если вы думаете, что разбиение на подгруппы похоже на разбиение на страницы, хочу вас предупредить, что не стоит их путать. При разбиении на страницы, размер страницы является константой, а число страниц меняется динамически - оно определяется делением числа строк в результате запроса на размер страницы. При разбиении на подгруппы число подгрупп является константой, а размер подгруппы меняется и определяется как число строк деленное на заданное число подгрупп. Ясно, для чего нужно разбиение на страницы, а разбиение на подгруппы обычно используется для аналитических задач - когда нужно распределить данные среди заданного числа равных по размеру сегментов с использованием упорядочения по определенному измерению.
Но вернемся к результату запроса, вычисляющего номера как строк, так и подгрупп: как видите они тесно связаны друг с другом. По сути, можно считать, что номер подгруппы вычисляется на основе номера строки. В предыдущем разделе мы говорили, что если упорядочение окна не является уникальным, функция ROW_NUMBER является недетерминистической. Если разбиение на подгруппы принципиально основано на номерах строк, то это означает, что вычисление NTILE также недетерминистично, если упорядочение окна не уникально. Это означает, что у данного запроса может быть несколько правильных результатов. Можно посмотреть на это с другой стороны: двум строкам с одним значением упорядочения могут быть назначены разные номера подгрупп. Если нужен гарантированный детерминизм, можно следовать моим рекомендациям по получению детерминистических номеров строк, а именно добавить в упорядочение окна дополнительный параметр:
SELECT orderid, val, ROW_NUMBER() OVER(ORDER BY val, orderid) AS rownum, NTILE(10) OVER(ORDER BY val, orderid) AS tile FROM Sales.OrderValues;
Теперь у запроса только один правильный результат. Ранее, при описании функции NTILE я пояснил, что она позволяет разбить строки в секции окна на примерно равные подгруппы. Я использовал слово «примерно», потому что число строк, полученное в базовом запросе, может не делиться нацело на число подгрупп. Допустим, вы хотите разбить строки представления OrderValues на 100 подгрупп. При делении 830 на 100 получаем частное 8 и остаток 30. Это означает, что базовая размерность подгрупп будет 8, но часть подгрупп получать дополнительную строку. Функция NTILE не пытается распределять дополнительные строки среди подгрупп с равным расстоянием между подгруппами - она просто добавляет по одному ряду в первые подгруппы, пока не распределит остаток. При наличии остатка 30 размерность первых 30 подгрупп будет на единицу больше базовой размерности. Поэтому первые 30 будут содержать 9 рядов, а последние 70 - 8, как показано в следующем запросе:
SELECT orderid, val, ROW_NUMBER() OVER(ORDER BY val, orderid) AS rownum, NTILE(100) OVER(ORDER BY val, orderid) AS tile FROM Sales.OrderValues;
Следуя привычному методу, попытаемся создать альтернативные решения, заменяющие функцию NTILE и не содержащие оконных функций.
Я покажу один способ решения задачи. Для начала, вот код, который вычисляет число подгрупп по заданным размерности, числу подгрупп и числу строк:
DECLARE @cnt AS INT = 830, @numtiles AS INT = 100, @rownum AS INT = 42; WITH C1 AS (SELECT @cnt / @numtiles AS basetilesize, @cnt / @numtiles + 1 AS extendedtilesize, @cnt % @numtiles AS remainder), C2 AS (SELECT *, extendedtilesize * remainder AS cutoffrow FROM C1) SELECT CASE WHEN @rownum <= cutoffrow THEN (@rownum - 1) / extendedtilesize + 1 ELSE remainder + ((@rownum - cutoffrow) - 1) / basetilesize + 1 END AS tile FROM C2;
Вычисление вполне очевидно. Для входных данных код возвращает 5 в качестве числа подгрупп.
Затем применим эту процедуру к строкам представления OrderValues. Используйте агрегат COUNT, чтобы получить размерность результирующего набора, а не входные данные @cnt, а также примените описанную ранее логику для вычисления номеров строк без использования оконных функций вместо входных данных @rownum:
DECLARE @numtiles AS INT = 100; WITH C1 AS (SELECT COUNT(*) / @numtiles AS basetilesize, COUNT(*) / @numtiles + 1 AS extendedtilesize, COUNT(*) % @numtiles AS remainder FROM Sales.OrderValues), C2 AS (SELECT *, extendedtilesize * remainder AS cutoffrow FROM C1), C3 AS (SELECT O1.orderid, O1.val, (SELECT COUNT(*) FROM Sales.OrderValues AS O2 WHERE O2.val <= O1.val AND (O2.val < O1.val OR O2.orderid <= O1.orderid)) AS rownum FROM Sales.OrderValues AS O1) SELECT C3.*, CASE WHEN C3.rownum <= C2.cutoffrow THEN (C3.rownum - 1) / C2.extendedtilesize + 1 ELSE C2.remainder + ((C3.rownum - C2.cutoffrow) - 1) / C2.basetilesize + 1 END AS tile FROM C3 CROSS JOIN C2;
Как обычно, не пытайтесь повторить это в производственной среде! Это пример предназначен для обучения, а его производительность в SQL Server ужасна по сравнению с функцией NTILE.
Функции RANK и DENSE_RANK
Функции RANK и DENSE RANK похожи на ROW_NUMBER, но в отличие от нее они не создают уникальные значения в оконной секции. При упорядочении окна по возрастанию «Обычный ранг» RANK вычисляется как единица плюс число строк со значением, по которому выполняется упорядочение, меньшим, чем текущее значение в секции. «Плотный ранг» DENSE_RANK вычисляется как единица плюс число уникальных строк со значением, по которому выполняется упорядочение, меньшим, чем текущее значение в секции. При упорядочении окна по убыванию RANK вычисляется как единица плюс число строк со значением, по которому выполняется упорядочение, большим, чем текущее значение в секции. DENSE_RANK вычисляется как единица плюс число уникальных строк со значением, по которому выполняется упорядочение, большим, чем текущее значение в секции.
Вот пример запроса, вычисляющего номера строк, ранги и «уплотненные» ранги. При этом используется секционирование окна по умолчанию и упорядочение по orderdate DESC:
SELECT orderid, orderdate, val, ROW_NUMBER() OVER(ORDER BY orderdate DESC) AS rownum, RANK() OVER(ORDER BY orderdate DESC) AS rnk, DENSE_RANK() OVER(ORDER BY orderdate DESC) AS drnk FROM Sales.OrderValues;
Атрибут orderdate не уникален. Но заметьте, что при этом номера строк уникальны. Значения RANK и DENSE_RANK не уникальны. Все строки с одной датой заказа, например 2008-05-05, получили одинаковый «неплотный» ранг 5 и «плотный» ранг 2. Ранг 5 означает, что есть четыре строки с большими (более поздними) датами заказов (упорядочение ведется по убыванию), а «плотный» ранг 2 означает, что есть одна более поздняя уникальная дата.
Альтернативное решение, заменяющее RANK и DENSE_RANK и не использующие оконные функции, создается просто:
SELECT orderid, orderdate, val, (SELECT COUNT(*) FROM Sales.OrderValues AS O2 WHERE O2.orderdate > O1.orderdate) + 1 AS rnk, (SELECT COUNT(DISTINCT orderdate) FROM Sales.OrderValues AS O2 WHERE O2.orderdate > O1.orderdate) + 1 AS drnk FROM Sales.OrderValues AS O1;
Для вычисления ранга определяется число строк с большим значением, по которому ведется упорядочение, (как вы помните, упорядочение ведется по убыванию) и к нему добавляется единица. Для вычисления плотного ранга нужно пересчитать уникальные большие значения, по которым ведется упорядочение, и добавить к полученному числу единицу.
Детерминизм
Как вы уже сами, наверное, поняли, что как RANK, так и DENSE_RANK детерминистичны по определению. При одном значении упорядочения - независимо от его уникальности - возвращается одно и то же значение ранга. Вообще говоря, эти две функции обычно интересны, если упорядочение неуникально. Если упорядочение уникально, они дают те же результаты, что и Функция ROW_NUMBER.
номер (последовательно , начиная с 1, в порядке, определенном ORDER BY ) каждой строке в секции.Синтаксис:
ROW_NUMBER () OVER ([
Предложение ORDER BY определяет последовательность, в которой строкам назначаются уникальные номера с помощью функции ROW_NUMBER в пределах указанной секции.
Пример 23.8 . Использование функции ROW_NUMBER ()
Пусть нам нужно провести группировку проданных товаров по объему продаж, используя схему на рис. 23.5
Результат выполнения запроса приведен ниже.
Вывод 8 .
| P_PRODUCTKEY | S_AMOUNT | SRNUM |
|---|---|---|
| Ботинки | 100 | 1 |
| Жакеты | 90 | 2 |
| Рубашки | 89 | 3 |
| Футболки | 84 | 4 |
| Свитеры | 75 | 5 |
| Джинсы | 75 | 6 |
| Ремни | 75 | 7 |
| Брюки | 69 | 8 |
| Ленты | 56 | 9 |
| Носки | 45 | 10 |
| Костюмы | NULL | 11 |
Свитерам, джинсам и ремням (с s_amount = 75) назначаются различные номера строк (5, 6, 7).
Подобно функции NTILE() , функция ROW_NUMBER() является недетерминистической функцией, так что "свитеры" мог бы получить номер строки 7 (вместо 5), а "ремни" - 5 (вместо 7). Чтобы избежать подобных ситуаций, необходимо сортировать результирующее множество по уникальному ключу.
Функции, генерирующие отчеты
После того как запрос выполнен, значения агрегатов (типа количество строк в результирующем множестве или среднее значение в колонке) могут быть вычислены для секции и быть доступными для других отчетов. Агрегатные функции генерирования отчетов (Reporting aggregate functions ) возвращают значения агрегатов для каждой строки в секции . К агрегатным функциям генерирования отчетов относятся функции SUM() , AVG() , MAX() , MIN() , COUNT() , использующее предложение OVER . Их поведение относительно NULL -значений такое же, как и в агрегатных функциях SQL .
В предыдущих разделах настоящей лекции мы уже обсуждали такое применение агрегатных функций. Поэтому в настоящем разделе приведем только несколько примеров.
Функции генерирования отчетов допустимы только для предложений SELECT . Основное их назначение состоит в способности выполнять многократный разбор блока данных результирующего множества запроса. Запросы типа "Подсчитать число продавцов, у которых уровень продаж больше на 10% от числа продаж по городу" не требуют соединений между отдельными блоками запроса.
Пример 23.9 . Использование агрегатных функций для генерирования отчетов
Пусть нам нужно для каждого товара найти регион, в котором наблюдается максимальный уровень продаж каждого товара, используя схему на рис. 23.5 . Это можно сделать с помощью следующего запроса:
SELECT s_productkey, s_regionkey, sum_s_amount FROM (SELECT p_productkey, r_regionkey, SUM(s_amount) AS "sum_s_amount", MAX(SUM(s_amount)) OVER (PARTITION BY p_productkey) AS "max_sum_s_amount" FROM sales GROUP BY p_productkey, r_regionkey) WHERE sum_s_amount = max_sum_s_amount;
Данные внутреннего запроса к таблице фактов "Продажи" (sales), сгруппированные по колонкам p_productkey и p_regionkey , агрегируются для первых трех колонок, и функция MAX(SUM(s_amount)) возвращает результат.
Вывод 9 .
| P_PRODUCTKEY | S_REGIONKEY | SUM_S_AMOUNT | MAX_SUM_S_AMOUNT |
|---|---|---|---|
| Жакеты | Запад | 99 | 99 |
| Жакеты | Восток | 50 | 99 |
| Брюки | Восток | 20 | 45 |
| Брюки | Запад | 45 | 45 |
| Рубашки | Восток | 60 | 80 |
| Рубашки | Запад | 80 | 80 |
| Ботинки | Запад | 100 | 130 |
| Ботинки | Восток | 130 | 130 |
| Свитеры | Запад | 75 | 75 |
| Свитеры | Восток | 75 | 75 |
| Носки | Восток | 95 | 95 |
| Носки | Запад | 66 | 95 |
Результат выполнения внешнего запроса приведен ниже.
Определение проблемы:
Введите любое число в ячейке A1 . Теперь попробуйте следующие формулы в любой точке первой строки.
SUM(INDIRECT("A"&ROW())) =SUMPRODUCT(INDIRECT("A"&ROW()))
Первая формула оценивается, вторая – ошибка #VALUE. Это вызвано тем, что функция ROW() ведет себя по-разному в SUMPRODUCT() .
В первой формуле ROW() возвращает 1 . Во второй формуле строка возвращает {1} (массив одной длины), хотя формула не была введена как формула CSE.
Почему это происходит?
Задний план
Мне нужно оценить формулу типа
SUMPRODUCT(INDIRECT(*range formed by concatenation and using ROW()*)>1)
Это приводит к ошибке. В качестве обходного пути к этой проблеме я теперь вычисляю ROW() в другой ячейке (в той же строке, очевидно) и объединяю ее внутри моего INDIRECT() . Кроме того, я также попытался инкапсулировать его внутри функции sum, например SUM(ROW()) , и это тоже работает.
Я был бы признателен, если бы кто-нибудь мог объяснить (или указать мне ресурс, который может объяснить), почему ROW() возвращает массив внутри SUMPRODUCT() без ввода CSE.
Solutions Collecting From Web of "Функция ROW () ведет себя по-разному внутри SUM () и SUMPRODUCT ()"
Интересный вопрос. Здесь есть тонкие проблемы, которые я не видел документально.
Кажется, INDIRECT("A"&ROW()) возвращает массив, состоящий из одного элемента, который является ссылкой на ячейку, а не значения в этой ячейке. Многие функции не могут правильно разрешить этот тип данных, но некоторые функции, такие как N и T, могут «разыгрывать» массив и возвращать базовое значение.
Возьмите этот случай, когда в массиве есть два элемента:
SUM(N(INDIRECT("A"&ROW(1:2))))
Это возвращает A1 + A2, когда массив введен, но он возвращает A1 только при вводе. Однако изменение ROW (1: 2) до {1; 2} в этой формуле возвращает правильный результат при вводе в обычном порядке. Эквивалентная формула SUMPRODUCT возвращает A1 + A2 независимо от того, введен ли массив или нет.
Это может быть связано с тем, как аргументы регистрируются в функции. Согласно http://msdn.microsoft.com/en-us/library/bb687900.aspx существуют два метода регистрации аргументов функции для обработки типов данных Excel:
Тип P / Q: «Excel преобразует одноклеточные ссылки на простые значения и многоклеточные ссылки на массивы при подготовке этих аргументов».
Аргументы SUM, похоже, соответствуют типу R / U, в то время как аргументы SUMPRODUCT ведут себя как тип P / Q. Массив, вводящий формулу SUM выше, заставляет опорный аргумент диапазона в ROW оцениваться как массив, тогда как это происходит автоматически с SUMPRODUCT.
Обновить
После небольшого исследования, вот еще одно доказательство, которое могло бы поддержать эту теорию. На основании ссылки в комментарии формула = SUM ((A1, A2)) дает те же значения, что и:
Executeexcel4macro("CALL(""Xlcall32"",""Excel4"",""2JRJR"",4,1,(!R1C1,!R2C1))")
Регистрация последнего аргумента как типа P путем изменения 2JRJR на 2JRJP дает ошибку в этом случае, но позволяет использовать диапазоны для одной области, такие как!R1C1:!R2C1 . С другой стороны, изменение 4 (xlfsum) до 228 (xlfsumproduct) допускает только ссылки на одну область так, как это называется так же, как SUMPRODUCT.
Когда ROW() возвращает массив, используйте INDEX для получения 1-го элемента.
Затем вы пример: =SUMPRODUCT(INDIRECT("A"&INDEX(ROW(),1)))
Я не думаю, что ROW () ведет себя по-другому здесь, он возвращает массив в обоих случаях. Я предполагаю, что SUM и SUMPRODUCT рассматривают этот массив по-разному – не знаю, почему.
Многие функции или их комбинации возвращают массивы – вам не нужно CTRL + SHIFT + ENTER, чтобы это произошло, вам нужно только CSE во многих случаях обрабатывать созданные массивы.
Я бы просто использовал INDEX вместо INDIRECT (что также приносит вам пользу, избегая изменчивой функции), т.е.
SUMPRODUCT(INDEX(A:A,ROW()))
…. расширяя это до вашего диапазона, эта формула будет подсчитывать количество значений> 1 в диапазоне в столбце A, где x определяет начальную строку, а y – конечную строку
COUNTIF(INDEX(A:A,x):INDEX(A:A,y),">1")
x и y можно вычислить по формулам
вы можете использовать SUMPRODUCT или COUNTIFS аналогичным образом, если есть дополнительные условия для добавления
