Здравствуйте. ексель начал изучать относительно недавно. Те функции с которыми работаю, а их не очень много, изучаю мо мере необходимости. Ранее я создавал сложную систему фильтров чтоб упорядочить данные таблиц в одну сводную. Все работает, но сами таблицы достаточно не простые. Решил изучить функцию QUERY, и понял, что табличка стала на много меньше и проще + редактировать ее на много быстрее. Но вот проблема... В таблице, о которой пишу идет следующая структура: справа идет импорт данных из нужных таблиц. У всех количество столбцов от 7 до 11, чтоб QUERY нормально работало, я у всех табличек увеличил количество столбиков до 11, просто лишние оставил пустыми. Далее идет несколько импортов по формуле на подобие этой:
Формула стоит а ячейке А1. Но почему то не полностью подтягиваются названия колонок. Вот например справа выделил рамкой, как заполнено в таблице которая импортировалась и слева видно, что названия колонок почему то подобрало только некоторые, причем в каждом случае это одни и те же названия. Что сделано не правильно и как поправить ситуацию?
Спасибо.
Здравствуйте. ексель начал изучать относительно недавно. Те функции с которыми работаю, а их не очень много, изучаю мо мере необходимости. Ранее я создавал сложную систему фильтров чтоб упорядочить данные таблиц в одну сводную. Все работает, но сами таблицы достаточно не простые. Решил изучить функцию QUERY, и понял, что табличка стала на много меньше и проще + редактировать ее на много быстрее. Но вот проблема... В таблице, о которой пишу идет следующая структура: справа идет импорт данных из нужных таблиц. У всех количество столбцов от 7 до 11, чтоб QUERY нормально работало, я у всех табличек увеличил количество столбиков до 11, просто лишние оставил пустыми. Далее идет несколько импортов по формуле на подобие этой:
Формула стоит а ячейке А1. Но почему то не полностью подтягиваются названия колонок. Вот например справа выделил рамкой, как заполнено в таблице которая импортировалась и слева видно, что названия колонок почему то подобрало только некоторые, причем в каждом случае это одни и те же названия. Что сделано не правильно и как поправить ситуацию?
Чисто фантазирую, без каких-либо аргументов, но это бросается в глаза: текстовые названия колонок пропадают там, где контент колонки - числа. Есть ощущение, что тип колонки как-то определяется по типу большинства значений, после чего значения-инородцы отвергаются. В данном случае получается, что заголовок считается первой записью с текстовым содержанием полей. Попробуйте в какой-нибудь такой колонке заменить значения на текст, например, в колонке "Активные" числовые значения 1 на текстовые "моя 1". Появится, интересно, в этом случае, ее название?
Я обычно с заголовками в SELECTах не заморачиваюсь, а пишу их по месту заранее и формулу с QUERY вставляю строкой ниже уже без заголовков.
Ну, и общее журящее замечание - обычно с таблицей-примером обсуждение проходит веселее и конструктивнее. А только по фотографиям тут обычно не лечат.
Чисто фантазирую, без каких-либо аргументов, но это бросается в глаза: текстовые названия колонок пропадают там, где контент колонки - числа. Есть ощущение, что тип колонки как-то определяется по типу большинства значений, после чего значения-инородцы отвергаются. В данном случае получается, что заголовок считается первой записью с текстовым содержанием полей. Попробуйте в какой-нибудь такой колонке заменить значения на текст, например, в колонке "Активные" числовые значения 1 на текстовые "моя 1". Появится, интересно, в этом случае, ее название?
Я обычно с заголовками в SELECTах не заморачиваюсь, а пишу их по месту заранее и формулу с QUERY вставляю строкой ниже уже без заголовков.
Ну, и общее журящее замечание - обычно с таблицей-примером обсуждение проходит веселее и конструктивнее. А только по фотографиям тут обычно не лечат.Gustav
я пробовал добавлять цифры в названия, пробовал полностью на число заменять, также пробовал названия писать руками, а потом их уже подтягивать - результат тот же. Если какие то диапазоны с итоговой выкидываю - вообще начинает искажаться таблица. Либо квери не желает мне поддаться, либо я что то не понимаю. создам аналог, попробую как там работает, если так же - вкину сюда с доступом.
я пробовал добавлять цифры в названия, пробовал полностью на число заменять, также пробовал названия писать руками, а потом их уже подтягивать - результат тот же. Если какие то диапазоны с итоговой выкидываю - вообще начинает искажаться таблица. Либо квери не желает мне поддаться, либо я что то не понимаю. создам аналог, попробую как там работает, если так же - вкину сюда с доступом.Faraway
В общем, пилюля нарисовалась такая. Во всех первых строчках диапазонов CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN, т.е. там, где заполнено всего два первых поля из 11, например, "Размещ проект" и "4 шт." в BP2:BQ2, введите пробел в остальные 9 ячеек BR2:BZ2 (введите в первую и скопируйте в остальные). По ходу, Google не любит поля null в первой строке и его начинает колбасить от них. А пробелом мы хоть как-то инициализируем значения. А визуально - всё остается так же, как хочется.
В общем, пилюля нарисовалась такая. Во всех первых строчках диапазонов CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN, т.е. там, где заполнено всего два первых поля из 11, например, "Размещ проект" и "4 шт." в BP2:BQ2, введите пробел в остальные 9 ячеек BR2:BZ2 (введите в первую и скопируйте в остальные). По ходу, Google не любит поля null в первой строке и его начинает колбасить от них. А пробелом мы хоть как-то инициализируем значения. А визуально - всё остается так же, как хочется.Gustav
Gustav, да, точно, бредово немного конечно, но все же причина была в этом. Спасибо! Установил все как было сказано, вылезла новая проблема. При подтягивании в итог информации первые две строки первого диапазона сливаются между собой, т.е. в первой ячейке должно быть "Тексты Флуд", а под ней "Клиент", а имеем в первой ячейке "Тексты Флуд Клиент". Есть какие то идеи, почему так искажается? док по прежнему тут, для наглядности подкинул туда данных https://docs.google.com/spreads....2308752
Gustav, да, точно, бредово немного конечно, но все же причина была в этом. Спасибо! Установил все как было сказано, вылезла новая проблема. При подтягивании в итог информации первые две строки первого диапазона сливаются между собой, т.е. в первой ячейке должно быть "Тексты Флуд", а под ней "Клиент", а имеем в первой ячейке "Тексты Флуд Клиент". Есть какие то идеи, почему так искажается? док по прежнему тут, для наглядности подкинул туда данных https://docs.google.com/spreads....2308752Faraway
Сообщение отредактировал Faraway - Понедельник, 14.05.2018, 19:10
попробовал убрать первый диапазон с формулы, чтоб понять, как будет без него. Результат - тот диапазон, который стоит в списке первым, будет сливать первые 1 линейки в одну.
попробовал убрать первый диапазон с формулы, чтоб понять, как будет без него. Результат - тот диапазон, который стоит в списке первым, будет сливать первые 1 линейки в одну.Faraway
Есть ощущение, что тип колонки как-то определяется по типу большинства значений, после чего значения-инородцы отвергаются.
А ведь всё именно так и оказалось! Цитаты из справки по функции QUERY:
Цитата
Каждый столбец данных может содержать только логические, числовые (включая типы дата/время) или строковые значения.
Акцентирую: ТОЛЬКО логические ИЛИ ТОЛЬКО числовые ИЛИ ТОЛЬКО строковые значения ПО ОТДЕЛЬНОСТИ, но не вперемешку в одной колонке, потому что...
Цитата
Если в столбце содержатся данные разных типов, преобладающий тип данных определяет тип всего столбца для целей запросов. Остальные типы данных считаются нулевыми.
По факту не всегда совсем "нулевыми". Так, если в колонке больше числовых значений и меньше текстовых - числовые будут отображаться как числа (которые можно, например, суммировать), а текстовые значения "пропадут" и будут заменены на пустые строки. Если же будет наоборот - больше текстовых и меньше числовых, то тогда текстовые значения не "пропадут", а числовые - как раз "не совсем обнулятся", т.е. не превратятся в значение 0 или пустую строку, но станут текстом (и такие "цифровые тексты" уже нельзя будет суммировать).
Для проверки можно проделать такой трюк. Поместите в ячейку CF10 формулу массива, генерирующую несколько десятков строк текстовых значений "_" в колонках CF:CI: [vba]
Код
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))
[/vba] Не забудьте отметить новые строки "единичками" в колонке CN, после чего посмотрите на результат функции QUERY: видим, что заголовки появились, но попытка посчитать сумму ячеек D2:D4 безуспешна, так как теперь эти значения - текстовые, хоть внешне и выглядящие как числа.
Что же делать? Не использовать в данном конкретном, достаточно сложном, случае функцию QUERY. Вместо нее используйте функцию FILTER (также в ячейке A1): [vba]
Есть ощущение, что тип колонки как-то определяется по типу большинства значений, после чего значения-инородцы отвергаются.
А ведь всё именно так и оказалось! Цитаты из справки по функции QUERY:
Цитата
Каждый столбец данных может содержать только логические, числовые (включая типы дата/время) или строковые значения.
Акцентирую: ТОЛЬКО логические ИЛИ ТОЛЬКО числовые ИЛИ ТОЛЬКО строковые значения ПО ОТДЕЛЬНОСТИ, но не вперемешку в одной колонке, потому что...
Цитата
Если в столбце содержатся данные разных типов, преобладающий тип данных определяет тип всего столбца для целей запросов. Остальные типы данных считаются нулевыми.
По факту не всегда совсем "нулевыми". Так, если в колонке больше числовых значений и меньше текстовых - числовые будут отображаться как числа (которые можно, например, суммировать), а текстовые значения "пропадут" и будут заменены на пустые строки. Если же будет наоборот - больше текстовых и меньше числовых, то тогда текстовые значения не "пропадут", а числовые - как раз "не совсем обнулятся", т.е. не превратятся в значение 0 или пустую строку, но станут текстом (и такие "цифровые тексты" уже нельзя будет суммировать).
Для проверки можно проделать такой трюк. Поместите в ячейку CF10 формулу массива, генерирующую несколько десятков строк текстовых значений "_" в колонках CF:CI: [vba]
Код
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))
[/vba] Не забудьте отметить новые строки "единичками" в колонке CN, после чего посмотрите на результат функции QUERY: видим, что заголовки появились, но попытка посчитать сумму ячеек D2:D4 безуспешна, так как теперь эти значения - текстовые, хоть внешне и выглядящие как числа.
Что же делать? Не использовать в данном конкретном, достаточно сложном, случае функцию QUERY. Вместо нее используйте функцию FILTER (также в ячейке A1): [vba]
Gustav, Спасибо. Тоже решил, что надо работать с фильтром, но не хотел писать об этом, чтоб не наталкивать на такое же мнение. Но хотелось просто узнать, что я не так делаю с QUERY, т.к. сегодня ошибка тут выскочила, а завтра еще где то. теперь понял, не любит абсолютную пустоту и данные в разнобой. Данные должны быть одного типа.
Код
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))
а как работает эта формула я вообще не знаю. Но мне пока и не надо! Спасибо еще раз!
Gustav, Спасибо. Тоже решил, что надо работать с фильтром, но не хотел писать об этом, чтоб не наталкивать на такое же мнение. Но хотелось просто узнать, что я не так делаю с QUERY, т.к. сегодня ошибка тут выскочила, а завтра еще где то. теперь понял, не любит абсолютную пустоту и данные в разнобой. Данные должны быть одного типа.
Код
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))
а как работает эта формула я вообще не знаю. Но мне пока и не надо! Спасибо еще раз!Faraway
Эта формула заполняет символом подчеркивания диапазон таблицы высотой 41 строка (50-10+1) и шириной 4 столбца (по количеству символов подчеркивания в литеральном массиве {"_"\"_"\"_"\"_"}).
Заполнение происходит вниз и вправо от ячейки ввода этой формулы. Если формулу ввести в A1, то получим заполненный символом подчеркивания диапазон A1:D41. Если же формулу ввести, скажем в B10, то в этом случае заполнится диапазон B10:E50. И т.п.
Конечно, удобнее начинать отсчет со строки 1 (а не 10, как у меня в примере выше). Тогда можно построить более-менее универсальную формулу-заполнитель: [vba]
[/vba] Эта формула делает то же самое, что и формула с ROW(10:50), но поскольку теперь нумерация строк начинается с 1, то более наглядны верхние пределы - 41 (строка) и 4 (столбца).
Использование вместо комбинации функций TRANSPOSE(ROW(1:4)) одной, вроде бы более "естественной", функции COLUMN возможно, хотя и менее наглядно, поскольку ее аргументами должны быть буквенные обозначения столбцов, а не числа: [vba]
Код
=ArrayFormula(IF(ROW(1:50);IF(COLUMN(A:D);"_")))
[/vba]
Все эти формулы вводятся в ЕДИНСТВЕННОМ экземпляре в ОДНУ левую верхнюю ячейку нужного диапазона. Т.е. формула - одна, а значений она генерирует в количестве 41x4. Вот она, сила формул массива!
Эта формула заполняет символом подчеркивания диапазон таблицы высотой 41 строка (50-10+1) и шириной 4 столбца (по количеству символов подчеркивания в литеральном массиве {"_"\"_"\"_"\"_"}).
Заполнение происходит вниз и вправо от ячейки ввода этой формулы. Если формулу ввести в A1, то получим заполненный символом подчеркивания диапазон A1:D41. Если же формулу ввести, скажем в B10, то в этом случае заполнится диапазон B10:E50. И т.п.
Конечно, удобнее начинать отсчет со строки 1 (а не 10, как у меня в примере выше). Тогда можно построить более-менее универсальную формулу-заполнитель: [vba]
[/vba] Эта формула делает то же самое, что и формула с ROW(10:50), но поскольку теперь нумерация строк начинается с 1, то более наглядны верхние пределы - 41 (строка) и 4 (столбца).
Использование вместо комбинации функций TRANSPOSE(ROW(1:4)) одной, вроде бы более "естественной", функции COLUMN возможно, хотя и менее наглядно, поскольку ее аргументами должны быть буквенные обозначения столбцов, а не числа: [vba]
Код
=ArrayFormula(IF(ROW(1:50);IF(COLUMN(A:D);"_")))
[/vba]
Все эти формулы вводятся в ЕДИНСТВЕННОМ экземпляре в ОДНУ левую верхнюю ячейку нужного диапазона. Т.е. формула - одна, а значений она генерирует в количестве 41x4. Вот она, сила формул массива!Gustav
Если что то понадобится, можно ли обращаться к вам?
Да Вы ко всем обращайтесь и непременно здесь, на Форуме, а я "подтянусь", если что. В личку не пишите, в личке я вопросы, мОгущие быть обсУжденными на Форуме, не обсуждаю. В личке - только вопросы с "интересом" (моим).
Для проверки можно проделать такой трюк. Поместите в ячейку CF10 формулу массива, генерирующую несколько десятков строк текстовых значений "_" в колонках CF:CI: =ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))
Я только напомню, для чего всё это было сделано. Мы хотели переопределить ("перешибить") числовой тип данных, определяемый по умолчанию, на текстовый - за счет введения в таблицу (в 4 "числовых" столбца) большого ("перешибающего") количества текстовых значений. После того, как мы таким образом "перетянули одеяло" на текстовые значения, у нас появились потерявшиеся заголовки. Но числа - и это обратная сторона этой медали - стали цифровым текстом.
Если что то понадобится, можно ли обращаться к вам?
Да Вы ко всем обращайтесь и непременно здесь, на Форуме, а я "подтянусь", если что. В личку не пишите, в личке я вопросы, мОгущие быть обсУжденными на Форуме, не обсуждаю. В личке - только вопросы с "интересом" (моим).
Для проверки можно проделать такой трюк. Поместите в ячейку CF10 формулу массива, генерирующую несколько десятков строк текстовых значений "_" в колонках CF:CI: =ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))
Я только напомню, для чего всё это было сделано. Мы хотели переопределить ("перешибить") числовой тип данных, определяемый по умолчанию, на текстовый - за счет введения в таблицу (в 4 "числовых" столбца) большого ("перешибающего") количества текстовых значений. После того, как мы таким образом "перетянули одеяло" на текстовые значения, у нас появились потерявшиеся заголовки. Но числа - и это обратная сторона этой медали - стали цифровым текстом.Gustav
Хотелось бы этот же документ немного привести к совершенству. Что именно хочу сделать... Хочу сделать так, чтоб можно было в сводной таблице выбирать параметры, что именно мне там надо видеть. Например в данный момент таблице жестко указано, что надо выводить данные, если в столбике 8 или в столбике 12 значение больше 0
Код
(Col8 > 0 OR Col12 > 0)
Теперь я хочу сделать так, чтоб я мог указывать, по какому из параметров надо выводить данные. Я сделал табличку В ней указаны данные. Далее предполагается сделать следующее: если в столбце "BX" стоит значение "ДА", но вставляется значение которое правее и разделитель между такими значениями " OR ". У меня получилась вот такая формула:
Код
WHERE ("JOIN(" OR ";filter(BY3:BY7;BX3:BX7="да"))")
Предположительно в данный момент вместо нее если подставить данные, то должно бы получится:
Код
WHERE (Col8 > 0 OR Col12 > 0)
но что то не так оно работает, потому что выдает что синтаксическая ошибка в формуле, если просто вставляю (Col8 > 0 OR Col12 > 0), то все ок, значит ошибка именно в куске ("JOIN(" OR ";filter(BY3:BY7;BX3:BX7="да"))") Можете подсказать, где именно ошибка и в чем она заключается? Формула прописана в ячейке AZ2 https://docs.google.com/spreads....0432297
Хотелось бы этот же документ немного привести к совершенству. Что именно хочу сделать... Хочу сделать так, чтоб можно было в сводной таблице выбирать параметры, что именно мне там надо видеть. Например в данный момент таблице жестко указано, что надо выводить данные, если в столбике 8 или в столбике 12 значение больше 0
Код
(Col8 > 0 OR Col12 > 0)
Теперь я хочу сделать так, чтоб я мог указывать, по какому из параметров надо выводить данные. Я сделал табличку В ней указаны данные. Далее предполагается сделать следующее: если в столбце "BX" стоит значение "ДА", но вставляется значение которое правее и разделитель между такими значениями " OR ". У меня получилась вот такая формула:
Код
WHERE ("JOIN(" OR ";filter(BY3:BY7;BX3:BX7="да"))")
Предположительно в данный момент вместо нее если подставить данные, то должно бы получится:
Код
WHERE (Col8 > 0 OR Col12 > 0)
но что то не так оно работает, потому что выдает что синтаксическая ошибка в формуле, если просто вставляю (Col8 > 0 OR Col12 > 0), то все ок, значит ошибка именно в куске ("JOIN(" OR ";filter(BY3:BY7;BX3:BX7="да"))") Можете подсказать, где именно ошибка и в чем она заключается? Формула прописана в ячейке AZ2 https://docs.google.com/spreads....0432297Faraway
Сообщение отредактировал Faraway - Пятница, 18.05.2018, 19:08
ексель начал изучать относительно недавно. Те функции с которыми работаю, а их не очень много, изучаю мо мере необходимости.
Вот и настал момент осознанно освоить соединение нескольких текстовых строк в одну. Гуглите по слову "конкатенация" или "конкатенация Excel" или "конкатенация Google Sheets". Там расскажут, как и во что "оборачивать".
ексель начал изучать относительно недавно. Те функции с которыми работаю, а их не очень много, изучаю мо мере необходимости.
Вот и настал момент осознанно освоить соединение нескольких текстовых строк в одну. Гуглите по слову "конкатенация" или "конкатенация Excel" или "конкатенация Google Sheets". Там расскажут, как и во что "оборачивать".Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
