Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Формула, которая неверно считает при массивном вводе - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула, которая неверно считает при массивном вводе
Формула, которая неверно считает при массивном вводе
Serge_007 Дата: Воскресенье, 05.05.2013, 20:26 | Сообщение № 1
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Друзья!

Вопрос такой: Можно ли написать обычную формулу, которая будет возвращать неправильный результат, если её ввести как формулу массива?
Т.е. требуется обратное действие тому, как если формулу массива вводят не Ctrl+Shift+Enter

У меня есть своё мнение на этот счёт, но я хочу услышать другие или убедиться в своём
Спасибо

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеДрузья!

Вопрос такой: Можно ли написать обычную формулу, которая будет возвращать неправильный результат, если её ввести как формулу массива?
Т.е. требуется обратное действие тому, как если формулу массива вводят не Ctrl+Shift+Enter

У меня есть своё мнение на этот счёт, но я хочу услышать другие или убедиться в своём
Спасибо
Автор - Serge_007
Дата добавления - 05.05.2013 в 20:26
MCH Дата: Воскресенье, 05.05.2013, 21:13 | Сообщение № 2
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Могу предложить такой вариант
Не совсем то, что нужно, но...
К сообщению приложен файл: Array.xls (17.0 Kb)


Сообщение отредактировал MCH - Воскресенье, 05.05.2013, 21:15
 
Ответить
СообщениеМогу предложить такой вариант
Не совсем то, что нужно, но...
Автор - MCH
Дата добавления - 05.05.2013 в 21:13
Serge_007 Дата: Воскресенье, 05.05.2013, 21:20 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Миш, спасибо, но да, это не то...
Понятно что нельзя сравнивать обычную формулу, всегда возвращающую одно значение и формулу массива, возвращающую массив. Однако можно сравнить их, если обе возвращают ОДНО значение. Вот в этом случае и надо найти разницу, если она есть.
Поясню: ИМХО - её (разницы) быть не может по логике формул. Однако - может я ошибаюсь?
Может существуют такие формулы?..

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеМиш, спасибо, но да, это не то...
Понятно что нельзя сравнивать обычную формулу, всегда возвращающую одно значение и формулу массива, возвращающую массив. Однако можно сравнить их, если обе возвращают ОДНО значение. Вот в этом случае и надо найти разницу, если она есть.
Поясню: ИМХО - её (разницы) быть не может по логике формул. Однако - может я ошибаюсь?
Может существуют такие формулы?..
Автор - Serge_007
Дата добавления - 05.05.2013 в 21:20
ikki Дата: Воскресенье, 05.05.2013, 22:03 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
не оно? smile
поясню - запись первого аргумента функции в зеленых ячейках непривычна, но допустима и вполне осмысленна, насколько я понимаю.
К сообщению приложен файл: vl.xls (13.5 Kb)

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщениене оно? smile
поясню - запись первого аргумента функции в зеленых ячейках непривычна, но допустима и вполне осмысленна, насколько я понимаю.
Автор - ikki
Дата добавления - 05.05.2013 в 22:03
MCH Дата: Воскресенье, 05.05.2013, 22:14 | Сообщение № 5
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Цитата (ikki)
не оно?

Александр, я предложил примерно то же самое по смыслу, но Сергею нужно что то другое.

Есть у него ответ или нет я не знаю, но выскажу свое мнение.
Формулы массива, если правильно составлен алгоритм, всегда возвращают ожидаемый результат, в отличае от "немассивных" формул
Сам принцип "немассивной" формулы можно было бы упразнить и расчет формулы производить по принципам обработки массивных формул.
Данный принцип ведь реализован в условном форматировании и в именах, так зачем различать вычисления в ячейках?
 
Ответить
Сообщение
Цитата (ikki)
не оно?

Александр, я предложил примерно то же самое по смыслу, но Сергею нужно что то другое.

Есть у него ответ или нет я не знаю, но выскажу свое мнение.
Формулы массива, если правильно составлен алгоритм, всегда возвращают ожидаемый результат, в отличае от "немассивных" формул
Сам принцип "немассивной" формулы можно было бы упразнить и расчет формулы производить по принципам обработки массивных формул.
Данный принцип ведь реализован в условном форматировании и в именах, так зачем различать вычисления в ячейках?
Автор - MCH
Дата добавления - 05.05.2013 в 22:14
Serge_007 Дата: Воскресенье, 05.05.2013, 22:19 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (MCH)
Формулы массива, если правильно составлен алгоритм, всегда возвращают ожидаемый результат, в отличае от "немассивных" формул
Это не понял, можно пояснить?

Цитата (MCH)
Есть у него ответ или нет я не знаю...
Я уже написал что есть, но я стремлюсь услышать и другие мнения

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (MCH)
Формулы массива, если правильно составлен алгоритм, всегда возвращают ожидаемый результат, в отличае от "немассивных" формул
Это не понял, можно пояснить?

Цитата (MCH)
Есть у него ответ или нет я не знаю...
Я уже написал что есть, но я стремлюсь услышать и другие мнения
Автор - Serge_007
Дата добавления - 05.05.2013 в 22:19
ikki Дата: Воскресенье, 05.05.2013, 22:25 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
ну а что тут искать? точнее - разницу в чем искать?
исходная формула/функции/диапазоны - ессно, одинаковые.
результат - одно число (тоже ессно).

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

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщениену а что тут искать? точнее - разницу в чем искать?
исходная формула/функции/диапазоны - ессно, одинаковые.
результат - одно число (тоже ессно).

разница - во внутренних механизмах работы некоторых функций. там, где "обычная" формула из подсунутого ей диапазона возьмёт одно число (не всегда, конечно же). а массивная - весь диапазон и это повлияет на результат.
Автор - ikki
Дата добавления - 05.05.2013 в 22:25
Serge_007 Дата: Воскресенье, 05.05.2013, 22:28 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (ikki)
разницу в чем искать?

А1+В1 - равно х
Вводим туже формулу массивно, получается y

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (ikki)
разницу в чем искать?

А1+В1 - равно х
Вводим туже формулу массивно, получается y
Автор - Serge_007
Дата добавления - 05.05.2013 в 22:28
ikki Дата: Воскресенье, 05.05.2013, 22:30 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
мне кажется, приведенный пример с ВПР это демонстрирует.
но, мало что понимая в формулах, спорить не буду, конечно.

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщениемне кажется, приведенный пример с ВПР это демонстрирует.
но, мало что понимая в формулах, спорить не буду, конечно.
Автор - ikki
Дата добавления - 05.05.2013 в 22:30
MCH Дата: Воскресенье, 05.05.2013, 22:31 | Сообщение № 10
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Цитата (Serge_007)
Это не понял, можно пояснить?

Как ведется расчет в формулах массива, понятно, и можно всегда предугадать, какой получится ответ.
Есть функции, которые могут обрабатывать аргументы, также как и формулы массива, будучи введеные обычным образом (СУММПРОИЗВ, ЧАСТОТА, МУМНОЖ и др.)
При этом не всегда это происходит так как нужно, например если внутри формулы есть функции ЕСЛИ или ТРАНСП, то массивный ввод обязателен, иначе считать корректно не будет

формула =МУМНОЖ(--(C1:F1=10);A1:A4) не нуждается в массивном вводе
а =МУМНОЖ(ТРАНСП(--(B1:B4=10));A1:A4) - нуждается

аналогично:
=СУММПРОИЗВ((B1:B4=10)*A1:A4) - не нуждается
=СУММПРОИЗВ(ЕСЛИ(B1:B4=10;A1:A4)) - нуждается в массивном вводе

Таим образом, без массивного ввода не возможно заранее предугадать, будет работать формула или нет
 
Ответить
Сообщение
Цитата (Serge_007)
Это не понял, можно пояснить?

Как ведется расчет в формулах массива, понятно, и можно всегда предугадать, какой получится ответ.
Есть функции, которые могут обрабатывать аргументы, также как и формулы массива, будучи введеные обычным образом (СУММПРОИЗВ, ЧАСТОТА, МУМНОЖ и др.)
При этом не всегда это происходит так как нужно, например если внутри формулы есть функции ЕСЛИ или ТРАНСП, то массивный ввод обязателен, иначе считать корректно не будет

формула =МУМНОЖ(--(C1:F1=10);A1:A4) не нуждается в массивном вводе
а =МУМНОЖ(ТРАНСП(--(B1:B4=10));A1:A4) - нуждается

аналогично:
=СУММПРОИЗВ((B1:B4=10)*A1:A4) - не нуждается
=СУММПРОИЗВ(ЕСЛИ(B1:B4=10;A1:A4)) - нуждается в массивном вводе

Таим образом, без массивного ввода не возможно заранее предугадать, будет работать формула или нет
Автор - MCH
Дата добавления - 05.05.2013 в 22:31
MCH Дата: Воскресенье, 05.05.2013, 23:26 | Сообщение № 11
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Еще один пример, логику которого я не понимаю
формула =СУММ((B1:B4=10)*A1:A4) корректно работает только когда вводится как формула массива, при обычном вводе возникает ошибка #ЗНАЧ!
а вот формулу =СУММ(ИНДЕКС((B1:B4=10)*A1:A4;)) можно ввести обычным образом, при этом расчет будет выполнен, как будто она введена как формула массива
Почему вторую формулу не нужно вводить как формулу массива?
 
Ответить
СообщениеЕще один пример, логику которого я не понимаю
формула =СУММ((B1:B4=10)*A1:A4) корректно работает только когда вводится как формула массива, при обычном вводе возникает ошибка #ЗНАЧ!
а вот формулу =СУММ(ИНДЕКС((B1:B4=10)*A1:A4;)) можно ввести обычным образом, при этом расчет будет выполнен, как будто она введена как формула массива
Почему вторую формулу не нужно вводить как формулу массива?
Автор - MCH
Дата добавления - 05.05.2013 в 23:26
ikki Дата: Понедельник, 06.05.2013, 00:22 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1906
Репутация: 504 ±
Замечаний: 0% ±

Excel 2003, 2010
кхм...
в поисках (пока не увенчались успехом) набрёл на очень хорошую статью на MSDN.
я бы сказал - must read
так что не бейте больно, ага.

http://msdn.microsoft.com/ru-ru....4).aspx
(ну и соседние - в списке слева - тоже)

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki
 
Ответить
Сообщениекхм...
в поисках (пока не увенчались успехом) набрёл на очень хорошую статью на MSDN.
я бы сказал - must read
так что не бейте больно, ага.

http://msdn.microsoft.com/ru-ru....4).aspx
(ну и соседние - в списке слева - тоже)
Автор - ikki
Дата добавления - 06.05.2013 в 00:22
Serge_007 Дата: Понедельник, 06.05.2013, 10:04 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Во первых: Огромное спасибо за отклики!

Во-вторых:
Цитата (ikki)
...запись первого аргумента функции в зеленых ячейках...
Да, это тоже самое что и у Михаила.
Однако я всё-же не это имел ввиду...

В принципе, строго по первоначальным условиям топика, ответ я получил, однако всё же хотелось бы понять, возможно ли получить тоже самое, но не прибегая к хитростям, типа ввода формулы в несколько ячеек или использования массива в качестве искомого ОДНОГО значения

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеВо первых: Огромное спасибо за отклики!

Во-вторых:
Цитата (ikki)
...запись первого аргумента функции в зеленых ячейках...
Да, это тоже самое что и у Михаила.
Однако я всё-же не это имел ввиду...

В принципе, строго по первоначальным условиям топика, ответ я получил, однако всё же хотелось бы понять, возможно ли получить тоже самое, но не прибегая к хитростям, типа ввода формулы в несколько ячеек или использования массива в качестве искомого ОДНОГО значения
Автор - Serge_007
Дата добавления - 06.05.2013 в 10:04
MCH Дата: Понедельник, 06.05.2013, 10:56 | Сообщение № 14
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Цитата (Serge_007)
А1+В1 - равно х
Вводим туже формулу массивно, получается y

Для сложения двух ячеек я не смог найти такую разницу

Проверял макросом:
[vba]
Код
Sub www()
     [a1].Formula = "=ROUND(10^(RAND()*10),RAND()*10)"
     [b1].Formula = "=ROUND(10^(RAND()*10),RAND()*10)"
     [c1].Formula = "=A1+B1"
     [d1].FormulaArray = "=A1+B1"
     [e1].Formula = "=C1=D1"

     Application.ScreenUpdating = False
     For i = 1 To 10000
         [a1:e1].Calculate
         If [e1] = False Then Debug.Print [a1], [b1]: Exit Sub
     Next
     Application.ScreenUpdating = True
End Sub
[/vba]
 
Ответить
Сообщение
Цитата (Serge_007)
А1+В1 - равно х
Вводим туже формулу массивно, получается y

Для сложения двух ячеек я не смог найти такую разницу

Проверял макросом:
[vba]
Код
Sub www()
     [a1].Formula = "=ROUND(10^(RAND()*10),RAND()*10)"
     [b1].Formula = "=ROUND(10^(RAND()*10),RAND()*10)"
     [c1].Formula = "=A1+B1"
     [d1].FormulaArray = "=A1+B1"
     [e1].Formula = "=C1=D1"

     Application.ScreenUpdating = False
     For i = 1 To 10000
         [a1:e1].Calculate
         If [e1] = False Then Debug.Print [a1], [b1]: Exit Sub
     Next
     Application.ScreenUpdating = True
End Sub
[/vba]
Автор - MCH
Дата добавления - 06.05.2013 в 10:56
Serge_007 Дата: Понедельник, 06.05.2013, 10:59 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (MCH)
Для сложения двух ячеек...
Миш, это просто пример smile
Действия, выполняемые формулой, могут быть любыми

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (MCH)
Для сложения двух ячеек...
Миш, это просто пример smile
Действия, выполняемые формулой, могут быть любыми
Автор - Serge_007
Дата добавления - 06.05.2013 в 10:59
MCH Дата: Понедельник, 24.06.2013, 10:54 | Сообщение № 16
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Цитата (Serge_007)
У меня есть своё мнение на этот счёт

Сергей, а ты так и не озвучил свое мнение
 
Ответить
Сообщение
Цитата (Serge_007)
У меня есть своё мнение на этот счёт

Сергей, а ты так и не озвучил свое мнение
Автор - MCH
Дата добавления - 24.06.2013 в 10:54
Serge_007 Дата: Понедельник, 24.06.2013, 10:56 | Сообщение № 17
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (Serge_007)
ИМХО - её (разницы) быть не может по логике формул.

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (Serge_007)
ИМХО - её (разницы) быть не может по логике формул.
Автор - Serge_007
Дата добавления - 24.06.2013 в 10:56
MCH Дата: Четверг, 17.10.2013, 13:35 | Сообщение № 18
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Столкнулся с такой штукой, которую не могу понять

формула
Код
=СМЕЩ(A1;СТРОКА();0)=""

Работает и при обычном вводе и при массивном одинаково
а формула
Код
=И(СМЕЩ(A1;СТРОКА();0)="")

корректно работает только при обычном вводе, при массивном - ошибка #ЗНАЧ!
 
Ответить
СообщениеСтолкнулся с такой штукой, которую не могу понять

формула
Код
=СМЕЩ(A1;СТРОКА();0)=""

Работает и при обычном вводе и при массивном одинаково
а формула
Код
=И(СМЕЩ(A1;СТРОКА();0)="")

корректно работает только при обычном вводе, при массивном - ошибка #ЗНАЧ!
Автор - MCH
Дата добавления - 17.10.2013 в 13:35
Serge_007 Дата: Четверг, 17.10.2013, 14:18 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Супер! Миш, спасибо за интересный пример

Миш, думаю это связано с особенностями работы функции И в формулах массива...

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеСупер! Миш, спасибо за интересный пример

Миш, думаю это связано с особенностями работы функции И в формулах массива...
Автор - Serge_007
Дата добавления - 17.10.2013 в 14:18
AndreTM Дата: Четверг, 17.10.2013, 14:26 | Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 501 ±
Замечаний: 0% ±

2003 & 2010
Цитата Serge_007, 17.10.2013 в 14:18, в сообщении № 19
думаю это связано с особенностями работы
Ну да, надо посмотреть на ограничения, описанные в справке. Что, например, выдаёт в 2010
Код
{=И(ЕСЛИОШИБКА(СМЕЩ(A1;СТРОКА();0)="";ЛОЖЬ))}

Skype: andre.tm.007
Donate: Qiwi: 9517375010
 
Ответить
Сообщение
Цитата Serge_007, 17.10.2013 в 14:18, в сообщении № 19
думаю это связано с особенностями работы
Ну да, надо посмотреть на ограничения, описанные в справке. Что, например, выдаёт в 2010
Код
{=И(ЕСЛИОШИБКА(СМЕЩ(A1;СТРОКА();0)="";ЛОЖЬ))}
Автор - AndreTM
Дата добавления - 17.10.2013 в 14:26
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула, которая неверно считает при массивном вводе
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!