Всем доброго времени суток! Подскажите, пожалуйста формулы для заполнения таблиц на листе "Что должно получиться" в образце. Расчеты вручную и базу данных прилагаю.
Всем доброго времени суток! Подскажите, пожалуйста формулы для заполнения таблиц на листе "Что должно получиться" в образце. Расчеты вручную и базу данных прилагаю.Maryasha
_Boroda_, спасибо, а там учитывается, что в 3-ей считается по сумме AK и AL? И не совсем понятно, почему в формуле для вывода победителей идет привязка к столбцу B, а не AB с наименованием победителя? Столбец B - заказчик, то есть вообще другие организации
_Boroda_, спасибо, а там учитывается, что в 3-ей считается по сумме AK и AL? И не совсем понятно, почему в формуле для вывода победителей идет привязка к столбцу B, а не AB с наименованием победителя? Столбец B - заказчик, то есть вообще другие организацииMaryasha
Сообщение отредактировал Maryasha - Пятница, 08.09.2017, 11:59
_Boroda_, что здесь не так не подскажите? специально заменил название на ИНН, думаю, так будет проще P.S. с названиями тоже не работает( Попробовал заменить 1 значение по победителю в исходной таблице-сразу ошибка вышла
_Boroda_, что здесь не так не подскажите? специально заменил название на ИНН, думаю, так будет проще P.S. с названиями тоже не работает( Попробовал заменить 1 значение по победителю в исходной таблице-сразу ошибка вышлаMaryasha
А объяснить эту формулу довольно сложно. Если не сильно много расписывать, то непонятно будет, а чтобы понятно было - я даже не знаю, как написать Ну, давайте вот так попробуем: 219-5+1=215 1. ЕСЛИ(БД!H$5:H$219<>"ЕП";БД!AB$5:AB$219) - это понятно, если в Н не ЕП, то АВ, иначе - ЛОЖЬ 2. СЧЁТЕСЛИМН(БД!H$5:H$219;"<>ЕП";БД!AB$5:AB$219;п.1) - дает массив из 215 элементов. Для каждой записи выводится сколько раз победила та организация, которая в этой строке (с условием того, что без ЕП) 3. 1/СУММЕСЛИМН(БД!X$5:X$219;БД!H$5:H$219;"<>ЕП";БД!AB$5:AB$219;БД!AB$5:AB$219) - аналогично п.2, но только не количество, а сумма. Нужно это для того, чтобы, при одинаковом количестве можно было ранжировать еще и по сумме * По идее туда тоже нужно было вставить кусок из п.1, но тогда все сложнее - нужно обрабатывать ошибку деления на ноль. Поэтому не стал 4. НАИБОЛЬШИЙ(п.2 + п.3; 1+СУММ(D$3:D3)) - из массива п.2 + п.3 берем n-ое наименьшее значение. Для первой строки n=1, для второй - 1 + столько значений, какое количество первых строк, ... 5. ПОИСКПОЗ(п.5; п.2 + п.3;) - ищем номер позиции полученного в п.5 в сумме п.2 и п.3 6. =ИНДЕКС(БД!AB$5:AB$219;п.5) - из массива с названиями вынимаем найденное в п.5 по порядку значение
Извините, забыл я про Ваш вопрос Там еще ЕС мешается. Вот так попробуйте
А объяснить эту формулу довольно сложно. Если не сильно много расписывать, то непонятно будет, а чтобы понятно было - я даже не знаю, как написать Ну, давайте вот так попробуем: 219-5+1=215 1. ЕСЛИ(БД!H$5:H$219<>"ЕП";БД!AB$5:AB$219) - это понятно, если в Н не ЕП, то АВ, иначе - ЛОЖЬ 2. СЧЁТЕСЛИМН(БД!H$5:H$219;"<>ЕП";БД!AB$5:AB$219;п.1) - дает массив из 215 элементов. Для каждой записи выводится сколько раз победила та организация, которая в этой строке (с условием того, что без ЕП) 3. 1/СУММЕСЛИМН(БД!X$5:X$219;БД!H$5:H$219;"<>ЕП";БД!AB$5:AB$219;БД!AB$5:AB$219) - аналогично п.2, но только не количество, а сумма. Нужно это для того, чтобы, при одинаковом количестве можно было ранжировать еще и по сумме * По идее туда тоже нужно было вставить кусок из п.1, но тогда все сложнее - нужно обрабатывать ошибку деления на ноль. Поэтому не стал 4. НАИБОЛЬШИЙ(п.2 + п.3; 1+СУММ(D$3:D3)) - из массива п.2 + п.3 берем n-ое наименьшее значение. Для первой строки n=1, для второй - 1 + столько значений, какое количество первых строк, ... 5. ПОИСКПОЗ(п.5; п.2 + п.3;) - ищем номер позиции полученного в п.5 в сумме п.2 и п.3 6. =ИНДЕКС(БД!AB$5:AB$219;п.5) - из массива с названиями вынимаем найденное в п.5 по порядку значение_Boroda_
_Boroda_, все равно с пустыми не работает и если числовое значение ввожу вместо первой организации тоже ошибку выдает. За объяснение спасибо, теперь более-менее понятно
_Boroda_, все равно с пустыми не работает и если числовое значение ввожу вместо первой организации тоже ошибку выдает. За объяснение спасибо, теперь более-менее понятноMaryasha
Сообщение отредактировал Maryasha - Понедельник, 11.09.2017, 14:57
На примере одной таблицы, дальше сами. Делайте что хотите, но лист БД должен быть пригодным для создания из него сводных таблиц. Я отсёк Ваши красивые, но "неправильные" заголовки и приколотил некрасивые, но "правильные". Сводная таблица будем Вам формировать столбцы E,F листа с топами (НМЦ, руб. Сумма договора, руб.). ДЛя остальных столбцов формулы Вам написали. Некоторые вставил в лист "Что получается". Включать/выключать ЕП будете через "Фильтр отчёта" сводной.
Столбец "Наименование победителя" сводной таблицы скройте, и покажите его ссылками на ячейки этого скрытого столбца.
Резюмирую: основную работу сделают сводные, к ним приделайте нужные столбцы формулами. С этим инструментом, Вы практически королева отчётов!
На примере одной таблицы, дальше сами. Делайте что хотите, но лист БД должен быть пригодным для создания из него сводных таблиц. Я отсёк Ваши красивые, но "неправильные" заголовки и приколотил некрасивые, но "правильные". Сводная таблица будем Вам формировать столбцы E,F листа с топами (НМЦ, руб. Сумма договора, руб.). ДЛя остальных столбцов формулы Вам написали. Некоторые вставил в лист "Что получается". Включать/выключать ЕП будете через "Фильтр отчёта" сводной.
Столбец "Наименование победителя" сводной таблицы скройте, и покажите его ссылками на ячейки этого скрытого столбца.
Резюмирую: основную работу сделают сводные, к ним приделайте нужные столбцы формулами. С этим инструментом, Вы практически королева отчётов!InExSu