Просьба помочь. Есть 2 листа в файле: "FOR PLAN TODAY" и "PLAN TODAY". В первый лист "FOR PLAN TODAY" данные падают из удаленной базы Access, и так как формулы из-за постоянных обновлений (прогрузок новых данных - без них никуда) все время сбиваются, то было принято решение для подстановки всех данных в лист "PLAN TODAY". Но если вы заметили, формула задваивает данные из листа-источника (каждая строчка повторяется дважды), скорей всего потому, что поиск идет по TourR, напротив которого разные числовые показатели в столбцах Balkony, Logim, Mezz, но ориентир идет только по первому полю.
Есть ли возможность, чтобы данные не только подставлялись в таблицу на листе без дублей, но и при этом суммировались, ориентируясь сразу по TourR и Date Start? [moder]Файл уменьшите до 100кб
Добрый день.
Просьба помочь. Есть 2 листа в файле: "FOR PLAN TODAY" и "PLAN TODAY". В первый лист "FOR PLAN TODAY" данные падают из удаленной базы Access, и так как формулы из-за постоянных обновлений (прогрузок новых данных - без них никуда) все время сбиваются, то было принято решение для подстановки всех данных в лист "PLAN TODAY". Но если вы заметили, формула задваивает данные из листа-источника (каждая строчка повторяется дважды), скорей всего потому, что поиск идет по TourR, напротив которого разные числовые показатели в столбцах Balkony, Logim, Mezz, но ориентир идет только по первому полю.
Есть ли возможность, чтобы данные не только подставлялись в таблицу на листе без дублей, но и при этом суммировались, ориентируясь сразу по TourR и Date Start? [moder]Файл уменьшите до 100кбRaven2009
Да, задвоение это как минимум((( Поэтому нужно еще одно поле для ориентировки))) Думаю, дата подойдет. Спасибо за помощь, постараюсь завтра попробовать)))
Да, задвоение это как минимум((( Поэтому нужно еще одно поле для ориентировки))) Думаю, дата подойдет. Спасибо за помощь, постараюсь завтра попробовать)))Raven2009
=ЕСЛИОШИБКА(ИНДЕКС('FOR PLAN TODAY'!$C$1:$C$15;НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ('FOR PLAN TODAY'!$C$1:$C$15;'FOR PLAN TODAY'!$C$1:$C$15;0)=СТРОКА('FOR PLAN TODAY'!$C$1:$C$15);СТРОКА('FOR PLAN TODAY'!$C$1:$C$15);"ж");"ж");СТРОКА($A$1:$A$10)));"")
уникальные значения
думаю с ВПР и суммесли сами разберетесь
Код
=ЕСЛИОШИБКА(ИНДЕКС('FOR PLAN TODAY'!$C$1:$C$15;НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ('FOR PLAN TODAY'!$C$1:$C$15;'FOR PLAN TODAY'!$C$1:$C$15;0)=СТРОКА('FOR PLAN TODAY'!$C$1:$C$15);СТРОКА('FOR PLAN TODAY'!$C$1:$C$15);"ж");"ж");СТРОКА($A$1:$A$10)));"")
В целом вариант неплохой, спасибо.))) Но только первая строчка правильно подставилась. Остальные подставились вместе значениями со смещением вниз, если сверять по второму листу(((
В целом вариант неплохой, спасибо.))) Но только первая строчка правильно подставилась. Остальные подставились вместе значениями со смещением вниз, если сверять по второму листу(((Raven2009
Теперь помогло))) Огромное спасибо))) Чуть позже будет еще один вопросик по данным таблицам))) Можете мне немного объяснить, как работает данная формула?? Я с такой еще не сталкивался)))
Теперь помогло))) Огромное спасибо))) Чуть позже будет еще один вопросик по данным таблицам))) Можете мне немного объяснить, как работает данная формула?? Я с такой еще не сталкивался)))
Можете мне немного объяснить, как работает данная формула?
Могу. Более того - даже объясню. И совсем уж аттракцион невиданной щедрости - объясню прямо сейчас. :D :D [vba]
Код
=ЕСЛИОШИБКА(ИНДЕКС('ДЛЯ PLAN TODAY'!C$2:C$99;ПОИСКПОЗ(;СЧЁТЕСЛИ(B$4:B4;'ДЛЯ PLAN TODAY'!C$2:C$99);))&"";"")
[/vba] Дальше теги формул использовать не буду - с ними непонятная мешанина получается. Пойдем изнутри наружу. 0. СЧЁТЕСЛИ(B$4:B4;'ДЛЯ PLAN TODAY'!C$2:C$99) - в В4 у нас какой-то текст, не относящийся к диапазону 'ДЛЯ PLAN TODAY'!C$2:C$99, поэтому СЧЁТЕСЛИ по нему даст нам ноль. В следующей строке будет из-за смещения вниз будет уже вот так: СЧЁТЕСЛИ(B$4:B5;'ДЛЯ PLAN TODAY'!C$2:C$99) - считаем СЧЁТЕСЛИ уже по 2-м параметрам (В4 и В5). В следующей - СЧЁТЕСЛИ(B$4:B6;'ДЛЯ PLAN TODAY'!C$2:C$99) - считаем СЧЁТЕСЛИ уже по 3-м параметрам (В4, В5 и В6). Этот вариант и рассмотрим (ячейка В7). Там формула будет вот такая [vba]
Код
=ЕСЛИОШИБКА(ИНДЕКС('FOR PLAN TODAY'!C$2:C$99;ПОИСКПОЗ(;СЧЁТЕСЛИ(B$4:B6;'FOR PLAN TODAY'!C$2:C$99);))&"";"")
[/vba] и выше в В5 значение "Авиа Домодедово PD05", а в В6 - "Авиа Шереметьево PD06".
1. СЧЁТЕСЛИ(B$4:B6;'FOR PLAN TODAY'!C$2:C$99) - даст нам количество значений из диапазона 'FOR PLAN TODAY'!C$2:C$99 в диапазоне B$4:B6. Другими словами - последовательно берем каждый элемент диапазона C$2:C$99 (имя листа я опущу для краткости) и считаем, сколько их в диапазоне B$4:B6. Если в B$4:B6 такие значения встречаются, то какая-то цифра, обозначающая количество вхождений, если не встречается, то 0. Итог - массив типа 1:2:1:0:0:0:...
2. ПОИСКПОЗ(;СЧЁТЕСЛИ(B$4:B6;'FOR PLAN TODAY'!C$2:C$99);) можно переписать так: ПОИСКПОЗ(0;МассивИзПункта1;0) - ищем позиция первого нуля в массиве из п.1 - это как раз и будет позиция первого текста из диапазона C$2:C$99, который пока не вошел в наш выводимый диапазон .
3. ИНДЕКС('FOR PLAN TODAY'!C$2:C$99;п.2) - по номеру позиции из п.2 выводит соответствующее значение из массива с текстом C$2:C$99
4. ...&"" - поскольку у нас массив C$2:C$99 взят с запасом (до 99 строки), то наша формула в списке уникальных будет выводить еще и пустое значение (оно у нас самое нижнее получится). А Excel в таких случаях автоматически пытается преобразовать его в число и, если получается, то вместо "" (пусто) дает нам 0 в ячейке В12. Чтобы этого не случилось, мы принудительно указываем Excelю, что у нас там текст. Для этого приклеиваем справа к полученному пустое значение.
5. Если все уникальные значения выведены, то в п.1 будет массив только из цифр, без нулей и ПОИСКПОЗ из п.2 ноль уже не найдет и даст ошибку. Для ее обработки и написан кусок ЕСЛИОШИБКА(...;"")
5а. На самом деле для этого случая ЕСЛИОШИБКА вообще не нужна, она написана просто на всякий случай, больше по привычке. Она нужно только для случая, когда в диапазоне заполнены ВСЕ значения - это следует из п.4. А с запасным диапазоном прекрасно работает и формула [vba]
Код
=ИНДЕКС('FOR PLAN TODAY'!C$2:C$99;ПОИСКПОЗ(;СЧЁТЕСЛИ(B$4:B4;'FOR PLAN TODAY'!C$2:C$99);))&""
[/vba] И конечно же, формулу массива нужно вводить одновременным нажатием Контрл Шифт Ентер
Можете мне немного объяснить, как работает данная формула?
Могу. Более того - даже объясню. И совсем уж аттракцион невиданной щедрости - объясню прямо сейчас. :D :D [vba]
Код
=ЕСЛИОШИБКА(ИНДЕКС('ДЛЯ PLAN TODAY'!C$2:C$99;ПОИСКПОЗ(;СЧЁТЕСЛИ(B$4:B4;'ДЛЯ PLAN TODAY'!C$2:C$99);))&"";"")
[/vba] Дальше теги формул использовать не буду - с ними непонятная мешанина получается. Пойдем изнутри наружу. 0. СЧЁТЕСЛИ(B$4:B4;'ДЛЯ PLAN TODAY'!C$2:C$99) - в В4 у нас какой-то текст, не относящийся к диапазону 'ДЛЯ PLAN TODAY'!C$2:C$99, поэтому СЧЁТЕСЛИ по нему даст нам ноль. В следующей строке будет из-за смещения вниз будет уже вот так: СЧЁТЕСЛИ(B$4:B5;'ДЛЯ PLAN TODAY'!C$2:C$99) - считаем СЧЁТЕСЛИ уже по 2-м параметрам (В4 и В5). В следующей - СЧЁТЕСЛИ(B$4:B6;'ДЛЯ PLAN TODAY'!C$2:C$99) - считаем СЧЁТЕСЛИ уже по 3-м параметрам (В4, В5 и В6). Этот вариант и рассмотрим (ячейка В7). Там формула будет вот такая [vba]
Код
=ЕСЛИОШИБКА(ИНДЕКС('FOR PLAN TODAY'!C$2:C$99;ПОИСКПОЗ(;СЧЁТЕСЛИ(B$4:B6;'FOR PLAN TODAY'!C$2:C$99);))&"";"")
[/vba] и выше в В5 значение "Авиа Домодедово PD05", а в В6 - "Авиа Шереметьево PD06".
1. СЧЁТЕСЛИ(B$4:B6;'FOR PLAN TODAY'!C$2:C$99) - даст нам количество значений из диапазона 'FOR PLAN TODAY'!C$2:C$99 в диапазоне B$4:B6. Другими словами - последовательно берем каждый элемент диапазона C$2:C$99 (имя листа я опущу для краткости) и считаем, сколько их в диапазоне B$4:B6. Если в B$4:B6 такие значения встречаются, то какая-то цифра, обозначающая количество вхождений, если не встречается, то 0. Итог - массив типа 1:2:1:0:0:0:...
2. ПОИСКПОЗ(;СЧЁТЕСЛИ(B$4:B6;'FOR PLAN TODAY'!C$2:C$99);) можно переписать так: ПОИСКПОЗ(0;МассивИзПункта1;0) - ищем позиция первого нуля в массиве из п.1 - это как раз и будет позиция первого текста из диапазона C$2:C$99, который пока не вошел в наш выводимый диапазон .
3. ИНДЕКС('FOR PLAN TODAY'!C$2:C$99;п.2) - по номеру позиции из п.2 выводит соответствующее значение из массива с текстом C$2:C$99
4. ...&"" - поскольку у нас массив C$2:C$99 взят с запасом (до 99 строки), то наша формула в списке уникальных будет выводить еще и пустое значение (оно у нас самое нижнее получится). А Excel в таких случаях автоматически пытается преобразовать его в число и, если получается, то вместо "" (пусто) дает нам 0 в ячейке В12. Чтобы этого не случилось, мы принудительно указываем Excelю, что у нас там текст. Для этого приклеиваем справа к полученному пустое значение.
5. Если все уникальные значения выведены, то в п.1 будет массив только из цифр, без нулей и ПОИСКПОЗ из п.2 ноль уже не найдет и даст ошибку. Для ее обработки и написан кусок ЕСЛИОШИБКА(...;"")
5а. На самом деле для этого случая ЕСЛИОШИБКА вообще не нужна, она написана просто на всякий случай, больше по привычке. Она нужно только для случая, когда в диапазоне заполнены ВСЕ значения - это следует из п.4. А с запасным диапазоном прекрасно работает и формула [vba]
Код
=ИНДЕКС('FOR PLAN TODAY'!C$2:C$99;ПОИСКПОЗ(;СЧЁТЕСЛИ(B$4:B4;'FOR PLAN TODAY'!C$2:C$99);))&""
[/vba] И конечно же, формулу массива нужно вводить одновременным нажатием Контрл Шифт Ентер_Boroda_
небольшой комментарий, форум глючит с тегами, и теги переводят то что не надо переводить. и поэтому модератор в своих объяснения не заметил что копирует не оригинал
ДЛЯ PLAN СЕГОДНЯ = FOR PLAN TODAY [moder]Спасибо. Поправил. Кстати, во всех формулах выше (если они на русском) аналогичное безобразие.
небольшой комментарий, форум глючит с тегами, и теги переводят то что не надо переводить. и поэтому модератор в своих объяснения не заметил что копирует не оригинал
ДЛЯ PLAN СЕГОДНЯ = FOR PLAN TODAY [moder]Спасибо. Поправил. Кстати, во всех формулах выше (если они на русском) аналогичное безобразие.Волхв
Спасибо огромное))) Вот только не получается скопировать данные формулы в другой файл. Там появляется сообще о циклической ссылке и все сбивается((( Это какая то связь с массивом??? [moder]Нет. Это связь с изменившимися диапазонами (строка 4, скорее всего). И вообще - как Вы себе представляете ответ на этот Ваш вопрос без файла?
Спасибо огромное))) Вот только не получается скопировать данные формулы в другой файл. Там появляется сообще о циклической ссылке и все сбивается((( Это какая то связь с массивом??? [moder]Нет. Это связь с изменившимися диапазонами (строка 4, скорее всего). И вообще - как Вы себе представляете ответ на этот Ваш вопрос без файла?Raven2009
Сообщение отредактировал _Boroda_ - Среда, 06.04.2016, 14:51
Модератор: Нет. Это связь с изменившимися диапазонами (строка 4, скорее всего). И вообще - как Вы себе представляете ответ на этот Ваш вопрос без файла?
Другой файл даже с минимальным набором данных весит намного больше 100Кб.
Модератор: Нет. Это связь с изменившимися диапазонами (строка 4, скорее всего). И вообще - как Вы себе представляете ответ на этот Ваш вопрос без файла?
Другой файл даже с минимальным набором данных весит намного больше 100Кб.Raven2009
Появляется такое сообщение и потом все значения в колонках преобразуются в цифры
Понятное дело, в полях для дат я формат поменяю, а вот остальное((( [moder]Дмитрий, а что Вы предлагаете нам сделать? Приехать к Вам в гости? Откусите от файла кусок, удалите ненужные листы, уберите форматирование, заархиваруйте.
Появляется такое сообщение и потом все значения в колонках преобразуются в цифры
Понятное дело, в полях для дат я формат поменяю, а вот остальное((( [moder]Дмитрий, а что Вы предлагаете нам сделать? Приехать к Вам в гости? Откусите от файла кусок, удалите ненужные листы, уберите форматирование, заархиваруйте.Raven2009
Модератор: Дмитрий, а что Вы предлагаете нам сделать? Приехать к Вам в гости? Откусите от файла кусок, удалите ненужные листы, уберите форматирование, заархиваруйте.
Ничего не предлагаю, я же не под дулом пистолета вас прошу помочь))) Откусить не так легко как кажется. Файл имеет целостность((( И размер после откусывания все равно большой...
Вот там почему то и получается, что после вставки формулы данные ссылаются на другой файл
Модератор: Дмитрий, а что Вы предлагаете нам сделать? Приехать к Вам в гости? Откусите от файла кусок, удалите ненужные листы, уберите форматирование, заархиваруйте.
Ничего не предлагаю, я же не под дулом пистолета вас прошу помочь))) Откусить не так легко как кажется. Файл имеет целостность((( И размер после откусывания все равно большой...
Вот там почему то и получается, что после вставки формулы данные ссылаются на другой файлRaven2009
после того как один раз перемкнул эксель, при использование строчного ФМ на 60-70тыс. строк а время было ограничено и пришлось все заново переделать, я стораюсь не использовать строчные массив
после того как один раз перемкнул эксель, при использование строчного ФМ на 60-70тыс. строк а время было ограничено и пришлось все заново переделать, я стораюсь не использовать строчные массивВолхв
Ну так уберите значения в квадратных скобках! И у Вас, как я уже и догадался выше, формулы начинаются не с 4 строки. А целостный Ваш файл не нужны вовсе. Нужны всего два листа без формул, но с расположением и форматом, аналогичным оригиналу.
Ну так уберите значения в квадратных скобках! И у Вас, как я уже и догадался выше, формулы начинаются не с 4 строки. А целостный Ваш файл не нужны вовсе. Нужны всего два листа без формул, но с расположением и форматом, аналогичным оригиналу._Boroda_
Ну и ради Бога, "сторайтесь", кто ж Вам мешает-то? На то человеку голова и дана, чтобы думать ею. И про то, в частности, что формулы, написанные для массивов в сотни строк, не всегда стОит использовать на десятки тысяч. А уникальные из большого диапазона вообще формулами вытаскивать крайне нежелательно. Кстати, а что такое, в Вашем понимании, "строчный массив"?
Ну и ради Бога, "сторайтесь", кто ж Вам мешает-то? На то человеку голова и дана, чтобы думать ею. И про то, в частности, что формулы, написанные для массивов в сотни строк, не всегда стОит использовать на десятки тысяч. А уникальные из большого диапазона вообще формулами вытаскивать крайне нежелательно. Кстати, а что такое, в Вашем понимании, "строчный массив"?_Boroda_
Когда ФМ для каждой ячейки уникальна, и размножается(изменяется) путем протаскивание(прямые и относительные ссылки), следовательно каждая ячейка(строка) выполняет обработку массива на больших объемах это 1000 раз, в 60 000 строчном массивах
в этих случаях нужна одна ФМ, которая выдает 1000 значений из 60 000 массива, соответственно выполняется операция только раз на весь диапазон вывода.
как это относится к теме? автор прикрепил файл ограниченно по весу файла, если у него окажется рабочий файл с большими объемами, то у него может все перемкнуть, и он снова сюда напишет в попытках оптимизировать нагрузку на процессор
Когда ФМ для каждой ячейки уникальна, и размножается(изменяется) путем протаскивание(прямые и относительные ссылки), следовательно каждая ячейка(строка) выполняет обработку массива на больших объемах это 1000 раз, в 60 000 строчном массивах
в этих случаях нужна одна ФМ, которая выдает 1000 значений из 60 000 массива, соответственно выполняется операция только раз на весь диапазон вывода.
как это относится к теме? автор прикрепил файл ограниченно по весу файла, если у него окажется рабочий файл с большими объемами, то у него может все перемкнуть, и он снова сюда напишет в попытках оптимизировать нагрузку на процессор