Знатокам Excel - Кофейня и курилка

Ken13

На форуме 17 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 20 Декабря 2011 13:11

Допустим мне надо вычислить сложную формулу с условием если из таблицы

Сперва проверяется значение в первой строке таблицы и, если оно соответствует моему условию, то должна рассчитываться формула, если несоответствует- то строка таблицы в рассчете не участвует.

Чтобы получить в ячейке с формулой совокупный результат по всей таблице (т.е. формула проверяет каждую строку таблицы по-очереди и плюсует рассчеты по каждой "правильной" строке) я должен заключить формулу в фигурные скобки "массив" { }

Да вот беда, не могу их вставить вручную. Т.е. в формуле они рисуются, но вычислений нет. В ячейке просто отражается вся формула.

В готовом другом отчете точно такая формула в фигурных скобках. Все вычисляется. Но скопировать и вставить я ее не могу к себе, т.к. фигурные скобки исчезают и их все-равно надо рисовать вручную.

Вот так cry

Как правильно вручную заключить формулу в массив?

rover

Ушлый вангер

На форуме 20 лет Сообщения: 2616 Авто: Старый чемадан

# 20 Декабря 2011 13:14

Ты значение "если истина" вписал что-ли?

Нафига если, когда просто надо сделать плюсовку и подбиваешь итог.

Формат ячеек глянь, формат чисел, а то может у тебя одни числа время, а другие рисунок.

Mitiok

На форуме 19 лет Сообщения: 315 Откуда: Волгоград Авто: ВАЗ 2107

# 20 Декабря 2011 13:23

Нече непонятно. попробуй через мастер формул.

Goblin

На форуме 20 лет Сообщения: 231

# 20 Декабря 2011 13:26

Mitiok писал(а):

Нече непонятно. попробуй через мастер формул.

Чё тут непонятного ЕСЛИ А1=... ТО <ФОРМУЛА>
У автора возникает проблема при автомат. протягивании как я понял
Проблема здесь с типом данных мне видится

Ken13

На форуме 17 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 20 Декабря 2011 13:28

rover писал(а):

Ты значение "если истина" вписал что-ли?

Нафига если, когда просто надо сделать плюсовку и подбиваешь итог.

Формат ячеек глянь, формат чисел, а то может у тебя одни числа время, а другие рисунок.

Например, имеем таблица. 100 строк 2 столбца.

В первом столбце Поставщик 1 и Поставщик 2. Во втором столбце объем поставок

Мне необходимо в формуле консолидировать только общий объем по поставщику 1.

Я пишу

=суммесли($a$1:$a$100="поставщик1";$b$1:$b$100;0)

Вот эту хрень надо заключить в {}

Нарыл в справке, что для автоматизации массива, необходимо нажать контрол+шифт+ввод. А вот нихрена не получается.

Цитата:

Создание формулы массива, вычисляющей несколько результатов

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

Совет. Формулы массива используют обычный синтаксис. Они все начинаются со знака равенства и могут содержать встроенные функции Excel.

Например, по заданному ряду из трех значений объема продаж (столбец B) для ряда из трех месяцев (столбец A) функция ТЕНДЕНЦИЯ определяет линейный прогноз объемов продаж. Для отображения всех вычисляемых значений формула введена в три ячейки столбца C (C1:C3).

Формула массива, вычисляющая несколько результатов

При вводе в качестве формулы массива формулы =ТЕНДЕНЦИЯ(B1:B3;A1:A3) формируются три отдельных результата (22196, 17079 и 11962), вычисленные по трем объемам продаж за три месяца.
Нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

Приложение Excel автоматически вставляет формулу между фигурными скобками ({ }).

Примечание. При заключении функции в фигурные скобки вручную она не преобразуется в функцию массива. Чтобы создать функцию массива, нажмите сочетание клавиш CTRL+SHIFT+ВВОД.

Важно. При изменении формулы массива фигурные скобки ({ }) не будут отображаться в формуле. В таком случае необходимо еще раз нажать сочетание клавиш CTRL+SHIFT+ВВОД.

[/i]

Я даже удалил из работающей ячейки формулу, предварительно сохранив в буфер.
Снова вставляю в ячейку и ничего. ctrl+shft+enter ничего не дает также

в ячейке вместо вычисления отражается сама формула

Axel_Foley

На форуме 20 лет Сообщения: 1285 Откуда: Московская область --> Тверь Авто: ВАЗ 21104, 1.6, 16V, май 2007, Звездная пыль + Ниссан Террано 3

# 20 Декабря 2011 13:32

Mitiok писал(а):

Нече непонятно

+1
Забей на Excel hehe

MilleniumD

На форуме 18 лет Сообщения: 883 Откуда: Москва Авто: не ржавый соап, не цвета эшториал блау, не на эвакуаторе

# 20 Декабря 2011 13:35

Goblin
Показывай и рассказывай о

Цитата:

FF2 2.0 MT

!

rover

Ушлый вангер

На форуме 20 лет Сообщения: 2616 Авто: Старый чемадан

# 20 Декабря 2011 13:44

Ken13

Цитата:

Мне необходимо в формуле консолидировать только общий объем по поставщику 1.

Нажми курсор в клетку Итгого. Нажми =, выбери вверху слева не ЕСли, а Сумм, в окне нажми значок для сворачивания, в конце окошка, выдели курсором область поставщика 1, нажми два раза Энтыр. В ячейке будет итог только первого поставщика.

Нафига ты в Если полез-то? Там три значения надо ловить, если Истина, если Ложь и Если куда кривая выведет, у него клинит.

rover

Ушлый вангер

На форуме 20 лет Сообщения: 2616 Авто: Старый чемадан

# 20 Декабря 2011 13:46

Я бы прикололся и вбил бы в формулу СлучЧисло() Сумм (20-19,9)+0,01 и наипал бы поставщика красиво, занизив ему обьем, который на бумаге выйдет верным, но будет ниже реального Good

Ken13

На форуме 17 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 20 Декабря 2011 13:46

rover

это был пример простой
формула гораздо длиннее, чем я написал и проверяет еще на 5 условий. Без если невозможно.

Цитата:

=СУММ(ЕСЛИ(Резервы!$B$4:$B$132=$B$2;ЕСЛИ(Резервы!$N$4:$N$132="П";ЕСЛИ(Резервы!$A$4:$A$132=I$6;Резервы!$F$4:$F$132;0);0);0))-СУММ(ЕСЛИ(Резервы!$B$4:$B$132=$B$2;ЕСЛИ(Резервы!$N$4:$N$132="П";ЕСЛИ(Резервы!$I$4:$I$132=I$6;Резервы!$J$4:$J$132;0);0);0))-СУММ(ЕСЛИ(Резервы!$B$4:$B$132=$B$2;ЕСЛИ(Резервы!$N$4:$N$132="П";ЕСЛИ(Резервы!$K$4:$K$132=I$6;Резервы!$L$4:$L$132;0);0);0))

вот она, сцуко

Последний раз редактировалось: Ken13 (20 Декабря 2011 13:48), всего редактировалось 1 раз

rover

Ушлый вангер

На форуме 20 лет Сообщения: 2616 Авто: Старый чемадан

# 20 Декабря 2011 13:48

Ken13 писал(а):

rover

это был пример простой
формула гораздо длиннее, чем я написал и проверяет еще на 5 условий. Без если невозможно.

Я поставил - все работает Pardon

Нажми кантрл, выдели цифры поставщика 1, нажми правой мышкой, формат ячейки, поставь "числовой"

И попробуй обсчитать формулу заново, просто добавляя нажатием, или сворачиванием окошка и выделения конкретных областей поставщика. И Ячейки по возможности должны идти один за другим, и не перебивать логику, т.е. не G5+G1:G20, а (G1+G2):G40.

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

rover

Ушлый вангер

На форуме 20 лет Сообщения: 2616 Авто: Старый чемадан

# 20 Декабря 2011 13:52

Ken13 писал(а):

rover

это был пример простой
формула гораздо длиннее, чем я написал и проверяет еще на 5 условий. Без если невозможно.

Цитата:

=СУММ(ЕСЛИ(Резервы!$B$4:$B$132=$B$2;ЕСЛИ(Резервы!$N$4:$N$132="П";ЕСЛИ(Резервы!$A$4:$A$132=I$6;Резервы!$F$4:$F$132;0);0);0))-СУММ(ЕСЛИ(Резервы!$B$4:$B$132=$B$2;ЕСЛИ(Резервы!$N$4:$N$132="П";ЕСЛИ(Резервы!$I$4:$I$132=I$6;Резервы!$J$4:$J$132;0);0);0))-СУММ(ЕСЛИ(Резервы!$B$4:$B$132=$B$2;ЕСЛИ(Резервы!$N$4:$N$132="П";ЕСЛИ(Резервы!$K$4:$K$132=I$6;Резервы!$L$4:$L$132;0);0);0))

вот она, нехороший человек

rover

Ушлый вангер

На форуме 20 лет Сообщения: 2616 Авто: Старый чемадан

# 20 Декабря 2011 13:52

Если хоть один результат Если у тебя не сходится, вся формула насмарку.
Добавь промежуточные и вычисли поэтапно в отдельных ячейках. Допустим в третьем столбце по 3-10 строк. Wink

Ken13

На форуме 17 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 20 Декабря 2011 13:53

rover

в предыдущем посте посмотри на формулу. Я ее выложил. На нее смотреть страшно, а ты хочешь еще обсчитать каждую область.

Проверка формулы ничего не дает-пишет что ошибка из-за неверных знаков массива

Ken13

На форуме 17 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 20 Декабря 2011 13:54

rover писал(а):

Если хоть один результат Если у тебя не сходится, вся формула насмарку.

в том-то и дело, что формула рабочая. Я ее скопировал и снова вставил в ту же ячейку. Но не могу правильно массив проставить

lml-83

На форуме 14 лет Сообщения: 843 Откуда: Башкортостан Авто: ВАЗ 21093, 1500 карб. и 2190 2013г.в. Шевролет лачетти

# 20 Декабря 2011 15:04

ох этот exlel ?убил бы lol1

иной раз как выдаст так хоть стой хоть падай, в твоём случае не знаю Pardon

Moggucci

На форуме 19 лет Сообщения: 1817 Откуда: Казань Авто: ВАЗ 2112 - MB W212

# 20 Декабря 2011 15:17

Ken13, етсь такая непонятная хрень с массивами. Прочитай еще раз по справке, как правильно вводить формулу, вся фишка в правильном вводе с этим долбанным контрол шифт альт и т.д. У меня по справке не с первого раза получилось. И не отредактируешь. Вообще идиотский инструмент эти формулы с массивами, непрозрачно работают. Имхо хлебнешь еще гавна с ними, попробуй сделать по-нормальному через сводные таблицы.

Surbet

На форуме 15 лет Сообщения: 520

# 20 Декабря 2011 15:20

Ken13
ctrl+shift+enter после ввода формулы

Ken13

На форуме 17 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 20 Декабря 2011 15:24

Moggucci писал(а):

Имхо хлебнешь еще гавна с ними, попробуй сделать по-нормальному через сводные таблицы.

Не. Формат таблиц менять нельзя, т.к. они входят в состав управленческой отчетности. Спасет лишь массив.

Вычитал еще на профильных, что фигурные скобки руками ставить нельзя, только комбинацией контр+шифт+ввод. Я в акуе, короче
cry

Ken13

На форуме 17 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 20 Декабря 2011 15:25

Surbet писал(а):

ctrl+shift+enter после ввода формулы

не получается

Surbet

На форуме 15 лет Сообщения: 520

# 20 Декабря 2011 15:25

Ken13 писал(а):

не получается

Значит в формуле ошибка. Что пишет?

Poops

На форуме 13 лет Сообщения: 77 Откуда: Украина

# 20 Декабря 2011 15:29

Чё только люди не придумают, лишь бы 1С-ом не пользоваться. С такими прибамбасами начинай учить макросы, потому что формулами такие вычисления делать - редкое извращение.

Mitiok

На форуме 19 лет Сообщения: 315 Откуда: Волгоград Авто: ВАЗ 2107

# 20 Декабря 2011 15:30

Surbet писал(а):

Значит в формуле ошибка. Что пишет?

А ниче не пишет, просто вместо скрытой формулы, она в явном виде в ячейке появляется, после постановки фигурных скобок

Ken13

На форуме 17 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 20 Декабря 2011 15:35

Нашел как фигурные скобки вонзить.

1. Вбиваем обычно формулу в ячейку

2. Копируем формулу ИЗ СТРОКИ ФОРМУЛ

3. Только потом жамкаем CTRL+SHIFT+ENTER

Скобки автоматом появляются, результат нихрена не считается. Появляется #Н/Д

Ken13

На форуме 17 лет Сообщения: 865 Откуда: Орёл Авто: Лёха

# 20 Декабря 2011 15:37

Surbet писал(а):

Значит в формуле ошибка. Что пишет?

Не ту ошибки в формуле. Я изголяюсь уже над работающей формулой в другом отчете.
Копирую ее в буфер. потом очищая ячейку. Вставляю снова в эту же ячейку. Появляется формула та же, но без скобок массива.

Скобки научился ставить правильно. В итоге пишет #Н/Д

Показать сообщения:

Перейти:

ИНФОРМАЦИЯ ПО ИКОНКАМ И ВОЗМОЖНОСТЯМ