Excel и матричные формулы
Sep. 19th, 2007 01:53 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
green_frКто-нибудь знает хорошую документацию о матричных формулах Excel?
Точнее, как можно указать, какую формулу я хочу воспринимать, как матричную, а какую, как обычную, скалярную.
Пример: есть колонка, в которой я хочу посчитать элементы, находящиеся в заданных пределах (между 0 и 100, например).
Посчитать элементы меньшие 0 можно как минимум двумя способами: функция =nb.si(range, "<0") (по-английски, по-моему, она называется countif) и {=somme(si(range<0;1;0))} (английские эквиваленты sum и iif). Ни тот, ни другой метод мне не удаётся примирить со вторым критерием: в первом случае просто синтаксис не предусмотрен, во втором Excel не воспринимает et (and) как матричную функция: {=somme(si(et(range<0;range>100);1;0))} даёт выражение, равное {=si(et(range<0;range>100);1;0)}, потому как оно скалярное, потому как {=et(range<0;range>100)} скалярное. Последнее, к слову, равно true тогда и только тогда, когда все элементы матрицы попадают под заданное условие.
Откуда и изначальный вопрос.
В MatLab всё гораздо проще, там чётко разделяется синтаксис операций по математическим правилам обращения с матрицами от поэлементных. Мне в данном случае нужна именно поэлементная операция et, т.е. такая, которая принимает матрицу любого размера и выдаёт матрицу того же размера, каждый элемент которой равен результату операции, применённой к соответствующему элементу матрицы изначальной.
P.S. Варианты с созданием промежуточных колонок, подсчётом через VBA, SQL и "перейди на unix" не предлагать :-)
Точнее, как можно указать, какую формулу я хочу воспринимать, как матричную, а какую, как обычную, скалярную.
Пример: есть колонка, в которой я хочу посчитать элементы, находящиеся в заданных пределах (между 0 и 100, например).
Посчитать элементы меньшие 0 можно как минимум двумя способами: функция =nb.si(range, "<0") (по-английски, по-моему, она называется countif) и {=somme(si(range<0;1;0))} (английские эквиваленты sum и iif). Ни тот, ни другой метод мне не удаётся примирить со вторым критерием: в первом случае просто синтаксис не предусмотрен, во втором Excel не воспринимает et (and) как матричную функция: {=somme(si(et(range<0;range>100);1;0))} даёт выражение, равное {=si(et(range<0;range>100);1;0)}, потому как оно скалярное, потому как {=et(range<0;range>100)} скалярное. Последнее, к слову, равно true тогда и только тогда, когда все элементы матрицы попадают под заданное условие.
Откуда и изначальный вопрос.
В MatLab всё гораздо проще, там чётко разделяется синтаксис операций по математическим правилам обращения с матрицами от поэлементных. Мне в данном случае нужна именно поэлементная операция et, т.е. такая, которая принимает матрицу любого размера и выдаёт матрицу того же размера, каждый элемент которой равен результату операции, применённой к соответствующему элементу матрицы изначальной.
P.S. Варианты с созданием промежуточных колонок, подсчётом через VBA, SQL и "перейди на unix" не предлагать :-)
![[identity profile]](https://www.dreamwidth.org/img/silk/identity/openid.png){kind=small}
no subject
Date: 2007-09-19 12:09 pm (UTC)Google "Excel Array Formulas" рулит. Они на немеряном количестве сайтов освещены.
no subject
Date: 2007-09-19 12:13 pm (UTC)no subject
Date: 2007-09-19 12:19 pm (UTC)no subject
Date: 2007-09-19 12:49 pm (UTC)Hope it helps
Date: 2007-09-19 12:58 pm (UTC)Suppose you have a worksheet which lists names, dates, and amounts, and you wish to add up all the amounts for which the name is Bob OR the date is before 5/1/98.
If you try some variety of using an OR-function you will meet with failure because if any of the items returns TRUE, then the entire OR-function will return TRUE, and you will probably add up all the amounts. For example, if you try =SUM(IF(OR(Name=“Bob”,Month(Date)<5),Amount)) you’ll get a number which is probably wrong because Name=“Bob” might evaluate to {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}, and Month(Date)<5 might evaluate to {FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE} and when these arrays are ORed together, the result is simply TRUE, not an array consisting of TRUE/FALSE. Therefore, the formula reduces to =SUM(IF(TRUE,Amount)) which, or course, is the sum of all the amounts.
The formula for the solution is the array-entered: =SUM(((Name="Bob")+(MONTH(Date)<5)>0)*Amount).
Let’s see how this works. Also, let’s assume the values for Amount are {1;2;3;4;5;6;7;8}. If you ADD the two TRUE/FALSE arrays detailed above, you get the array {1;1;0;2;1;2;0;1}. Note: TRUE + TRUE evaluates to 2, TRUE + FALSE evaluates to 1 and FALSE + FALSE evaluates to 0. What is this? Well, each 1 corresponds to a TRUE+FALSE or FALSE+TRUE, meaning one of the conditions was met (Name was “Bob” or Month(Date) was less than 5). 2 means both conditions were met, and zero means neither condition was met. You’re interested in the non-zeros. Therefore, comparing this array to zero: {1;1;0;2;1;2;0;1}>0 evaluates to {TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE},and when this is multiplied by Amount, it evaluates to {1;2;0;4;5;6;0;8}. When this is SUMmed together you get 26. If you had wanted to add the amounts together when both conditions are met, you could use the array-entered formula: =SUM((Name="Bob")*(MONTH(Date)<5)*Amount).
Re: Hope it helps
Date: 2007-09-19 01:08 pm (UTC)Жаль, что нет "нормального" способа указать. где я хочу оставить матрицу...
Re: Hope it helps
Date: 2007-09-19 01:11 pm (UTC)По-моему, эти функции вообще недокументированные. Типа нефиг офисных работников смущать.
Re: Hope it helps
Date: 2007-09-19 01:19 pm (UTC)не в тему, но очень актуально
Date: 2007-09-21 10:28 am (UTC)не в тему, но очень актуарьно
Date: 2007-09-21 11:26 am (UTC)no subject
Date: 2007-09-21 12:35 pm (UTC)no subject
Date: 2007-09-21 02:48 pm (UTC)no subject
Date: 2007-09-25 05:28 am (UTC)тебе интересно будет, по работе ;)
no subject
Date: 2007-09-26 08:14 am (UTC)