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

Вход

Регистрация

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

 

= Мир MS Excel/(QUERY) данные некоторых линеек подтягиваются не полностью - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » (QUERY) данные некоторых линеек подтягиваются не полностью (Формулы/Formulas)
(QUERY) данные некоторых линеек подтягиваются не полностью
Faraway Дата: Понедельник, 14.05.2018, 11:38 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Здравствуйте. ексель начал изучать относительно недавно. Те функции с которыми работаю, а их не очень много, изучаю мо мере необходимости.
Ранее я создавал сложную систему фильтров чтоб упорядочить данные таблиц в одну сводную. Все работает, но сами таблицы достаточно не простые. Решил изучить функцию QUERY, и понял, что табличка стала на много меньше и проще + редактировать ее на много быстрее.
Но вот проблема...
В таблице, о которой пишу идет следующая структура:
справа идет импорт данных из нужных таблиц. У всех количество столбцов от 7 до 11, чтоб QUERY нормально работало, я у всех табличек увеличил количество столбиков до 11, просто лишние оставил пустыми.
Далее идет несколько импортов по формуле на подобие этой:

Код
=QUERY(IMPORTRANGE("адрес";"Размещ проект!A6:M");"
SELECT
Col1, Col2, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13
WHERE
Col8 > 0
LABEL
Col1 'Клиент',
Col2 'Проект',
Col5 'Ссылка',
Col6 'План. колич',
Col7 'Факт. сделано',
Col8 'Активные',
Col9 'Подтверждено',
Col10 'Ссылка модер',
Col11 'Модерация',
Col12 'Не принято',
Col13 'Заработано'
")

как оно отображается - видно на скрине в правой части. Формула стоит в ячейке BP3 (подкрашена желтым)

Далее, когда все нужные таблицы импортированы на лист (их 5 шт), слева от них я делаю сводную табличку формулой:

Код
=QUERY({CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN};"
SELECT
Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11
WHERE
Col12 = 1
")

Формула стоит а ячейке А1.
Но почему то не полностью подтягиваются названия колонок. Вот например справа выделил рамкой, как заполнено в таблице которая импортировалась и слева видно, что названия колонок почему то подобрало только некоторые, причем в каждом случае это одни и те же названия.
Что сделано не правильно и как поправить ситуацию?



Спасибо.


Сообщение отредактировал Faraway - Понедельник, 14.05.2018, 12:11
 
Ответить
СообщениеЗдравствуйте. ексель начал изучать относительно недавно. Те функции с которыми работаю, а их не очень много, изучаю мо мере необходимости.
Ранее я создавал сложную систему фильтров чтоб упорядочить данные таблиц в одну сводную. Все работает, но сами таблицы достаточно не простые. Решил изучить функцию QUERY, и понял, что табличка стала на много меньше и проще + редактировать ее на много быстрее.
Но вот проблема...
В таблице, о которой пишу идет следующая структура:
справа идет импорт данных из нужных таблиц. У всех количество столбцов от 7 до 11, чтоб QUERY нормально работало, я у всех табличек увеличил количество столбиков до 11, просто лишние оставил пустыми.
Далее идет несколько импортов по формуле на подобие этой:

Код
=QUERY(IMPORTRANGE("адрес";"Размещ проект!A6:M");"
SELECT
Col1, Col2, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13
WHERE
Col8 > 0
LABEL
Col1 'Клиент',
Col2 'Проект',
Col5 'Ссылка',
Col6 'План. колич',
Col7 'Факт. сделано',
Col8 'Активные',
Col9 'Подтверждено',
Col10 'Ссылка модер',
Col11 'Модерация',
Col12 'Не принято',
Col13 'Заработано'
")

как оно отображается - видно на скрине в правой части. Формула стоит в ячейке BP3 (подкрашена желтым)

Далее, когда все нужные таблицы импортированы на лист (их 5 шт), слева от них я делаю сводную табличку формулой:

Код
=QUERY({CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN};"
SELECT
Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11
WHERE
Col12 = 1
")

Формула стоит а ячейке А1.
Но почему то не полностью подтягиваются названия колонок. Вот например справа выделил рамкой, как заполнено в таблице которая импортировалась и слева видно, что названия колонок почему то подобрало только некоторые, причем в каждом случае это одни и те же названия.
Что сделано не правильно и как поправить ситуацию?



Спасибо.

Автор - Faraway
Дата добавления - 14.05.2018 в 11:38
Gustav Дата: Понедельник, 14.05.2018, 12:57 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Чисто фантазирую, без каких-либо аргументов, но это бросается в глаза: текстовые названия колонок пропадают там, где контент колонки - числа. Есть ощущение, что тип колонки как-то определяется по типу большинства значений, после чего значения-инородцы отвергаются. В данном случае получается, что заголовок считается первой записью с текстовым содержанием полей. Попробуйте в какой-нибудь такой колонке заменить значения на текст, например, в колонке "Активные" числовые значения 1 на текстовые "моя 1". Появится, интересно, в этом случае, ее название?

Я обычно с заголовками в SELECTах не заморачиваюсь, а пишу их по месту заранее и формулу с QUERY вставляю строкой ниже уже без заголовков.

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


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЧисто фантазирую, без каких-либо аргументов, но это бросается в глаза: текстовые названия колонок пропадают там, где контент колонки - числа. Есть ощущение, что тип колонки как-то определяется по типу большинства значений, после чего значения-инородцы отвергаются. В данном случае получается, что заголовок считается первой записью с текстовым содержанием полей. Попробуйте в какой-нибудь такой колонке заменить значения на текст, например, в колонке "Активные" числовые значения 1 на текстовые "моя 1". Появится, интересно, в этом случае, ее название?

Я обычно с заголовками в SELECTах не заморачиваюсь, а пишу их по месту заранее и формулу с QUERY вставляю строкой ниже уже без заголовков.

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

Автор - Gustav
Дата добавления - 14.05.2018 в 12:57
Faraway Дата: Понедельник, 14.05.2018, 14:43 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
я пробовал добавлять цифры в названия, пробовал полностью на число заменять, также пробовал названия писать руками, а потом их уже подтягивать - результат тот же.
Если какие то диапазоны с итоговой выкидываю - вообще начинает искажаться таблица. Либо квери не желает мне поддаться, либо я что то не понимаю.
создам аналог, попробую как там работает, если так же - вкину сюда с доступом.
 
Ответить
Сообщениея пробовал добавлять цифры в названия, пробовал полностью на число заменять, также пробовал названия писать руками, а потом их уже подтягивать - результат тот же.
Если какие то диапазоны с итоговой выкидываю - вообще начинает искажаться таблица. Либо квери не желает мне поддаться, либо я что то не понимаю.
создам аналог, попробую как там работает, если так же - вкину сюда с доступом.

Автор - Faraway
Дата добавления - 14.05.2018 в 14:43
Faraway Дата: Понедельник, 14.05.2018, 15:09 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Переделал все, результат тот же.
Вот док, из которого сосет данные, его редактировать нельзя, к этому доку у мен только просмотр
https://docs.google.com/spreads....9088031
А вот тут моя итоговая табличка, в которой я владелец и могу делать что хочу.
https://docs.google.com/spreads....2308752
 
Ответить
СообщениеПеределал все, результат тот же.
Вот док, из которого сосет данные, его редактировать нельзя, к этому доку у мен только просмотр
https://docs.google.com/spreads....9088031
А вот тут моя итоговая табличка, в которой я владелец и могу делать что хочу.
https://docs.google.com/spreads....2308752

Автор - Faraway
Дата добавления - 14.05.2018 в 15:09
Gustav Дата: Понедельник, 14.05.2018, 17:57 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
В общем, пилюля нарисовалась такая. Во всех первых строчках диапазонов CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN, т.е. там, где заполнено всего два первых поля из 11, например, "Размещ проект" и "4 шт." в BP2:BQ2, введите пробел в остальные 9 ячеек BR2:BZ2 (введите в первую и скопируйте в остальные). По ходу, Google не любит поля null в первой строке и его начинает колбасить от них. А пробелом мы хоть как-то инициализируем значения. А визуально - всё остается так же, как хочется.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВ общем, пилюля нарисовалась такая. Во всех первых строчках диапазонов CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN, т.е. там, где заполнено всего два первых поля из 11, например, "Размещ проект" и "4 шт." в BP2:BQ2, введите пробел в остальные 9 ячеек BR2:BZ2 (введите в первую и скопируйте в остальные). По ходу, Google не любит поля null в первой строке и его начинает колбасить от них. А пробелом мы хоть как-то инициализируем значения. А визуально - всё остается так же, как хочется.

Автор - Gustav
Дата добавления - 14.05.2018 в 17:57
Faraway Дата: Понедельник, 14.05.2018, 19:08 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Gustav, да, точно, бредово немного конечно, но все же причина была в этом. Спасибо!
Установил все как было сказано, вылезла новая проблема.
При подтягивании в итог информации первые две строки первого диапазона сливаются между собой, т.е. в первой ячейке должно быть "Тексты Флуд", а под ней "Клиент", а имеем в первой ячейке "Тексты Флуд Клиент". Есть какие то идеи, почему так искажается?
док по прежнему тут, для наглядности подкинул туда данных
https://docs.google.com/spreads....2308752


Сообщение отредактировал Faraway - Понедельник, 14.05.2018, 19:10
 
Ответить
СообщениеGustav, да, точно, бредово немного конечно, но все же причина была в этом. Спасибо!
Установил все как было сказано, вылезла новая проблема.
При подтягивании в итог информации первые две строки первого диапазона сливаются между собой, т.е. в первой ячейке должно быть "Тексты Флуд", а под ней "Клиент", а имеем в первой ячейке "Тексты Флуд Клиент". Есть какие то идеи, почему так искажается?
док по прежнему тут, для наглядности подкинул туда данных
https://docs.google.com/spreads....2308752

Автор - Faraway
Дата добавления - 14.05.2018 в 19:08
Faraway Дата: Понедельник, 14.05.2018, 19:22 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
попробовал убрать первый диапазон с формулы, чтоб понять, как будет без него. Результат - тот диапазон, который стоит в списке первым, будет сливать первые 1 линейки в одну.
 
Ответить
Сообщениепопробовал убрать первый диапазон с формулы, чтоб понять, как будет без него. Результат - тот диапазон, который стоит в списке первым, будет сливать первые 1 линейки в одну.

Автор - Faraway
Дата добавления - 14.05.2018 в 19:22
Gustav Дата: Вторник, 15.05.2018, 01:22 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Есть ощущение, что тип колонки как-то определяется по типу большинства значений, после чего значения-инородцы отвергаются.

А ведь всё именно так и оказалось! Цитаты из справки по функции QUERY:

Цитата
Каждый столбец данных может содержать только логические, числовые (включая типы дата/время) или строковые значения.

Акцентирую: ТОЛЬКО логические ИЛИ ТОЛЬКО числовые ИЛИ ТОЛЬКО строковые значения ПО ОТДЕЛЬНОСТИ, но не вперемешку в одной колонке, потому что...

Цитата
Если в столбце содержатся данные разных типов, преобладающий тип данных определяет тип всего столбца для целей запросов. Остальные типы данных считаются нулевыми.

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

Для проверки можно проделать такой трюк. Поместите в ячейку CF10 формулу массива, генерирующую несколько десятков строк текстовых значений "_" в колонках CF:CI:
[vba]
Код
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))
[/vba]
Не забудьте отметить новые строки "единичками" в колонке CN, после чего посмотрите на результат функции QUERY: видим, что заголовки появились, но попытка посчитать сумму ячеек D2:D4 безуспешна, так как теперь эти значения - текстовые, хоть внешне и выглядящие как числа.

Что же делать? Не использовать в данном конкретном, достаточно сложном, случае функцию QUERY. Вместо нее используйте функцию FILTER (также в ячейке A1):
[vba]
Код
=FILTER({CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN}; {CN2:CN;CA2:CA;BN2:BN;BA2:BA;AN2:AN}=1)
[/vba]

Кстати, очень наглядно в данном случае смеха ради попробовать обернуть эту функцию FILTER функцией QUERY:
[vba]
Код
=QUERY(FILTER({CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN}; {CN2:CN;CA2:CA;BN2:BN;BA2:BA;AN2:AN}=1);"select *")
[/vba]
и с нескрываемым "прискорбием" констатировать, что мы опять приходим туда, откуда пришли :)


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Есть ощущение, что тип колонки как-то определяется по типу большинства значений, после чего значения-инородцы отвергаются.

А ведь всё именно так и оказалось! Цитаты из справки по функции QUERY:

Цитата
Каждый столбец данных может содержать только логические, числовые (включая типы дата/время) или строковые значения.

Акцентирую: ТОЛЬКО логические ИЛИ ТОЛЬКО числовые ИЛИ ТОЛЬКО строковые значения ПО ОТДЕЛЬНОСТИ, но не вперемешку в одной колонке, потому что...

Цитата
Если в столбце содержатся данные разных типов, преобладающий тип данных определяет тип всего столбца для целей запросов. Остальные типы данных считаются нулевыми.

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

Для проверки можно проделать такой трюк. Поместите в ячейку CF10 формулу массива, генерирующую несколько десятков строк текстовых значений "_" в колонках CF:CI:
[vba]
Код
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))
[/vba]
Не забудьте отметить новые строки "единичками" в колонке CN, после чего посмотрите на результат функции QUERY: видим, что заголовки появились, но попытка посчитать сумму ячеек D2:D4 безуспешна, так как теперь эти значения - текстовые, хоть внешне и выглядящие как числа.

Что же делать? Не использовать в данном конкретном, достаточно сложном, случае функцию QUERY. Вместо нее используйте функцию FILTER (также в ячейке A1):
[vba]
Код
=FILTER({CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN}; {CN2:CN;CA2:CA;BN2:BN;BA2:BA;AN2:AN}=1)
[/vba]

Кстати, очень наглядно в данном случае смеха ради попробовать обернуть эту функцию FILTER функцией QUERY:
[vba]
Код
=QUERY(FILTER({CC2:CN;BP2:CA;BC2:BN;AP2:BA;AC2:AN}; {CN2:CN;CA2:CA;BN2:BN;BA2:BA;AN2:AN}=1);"select *")
[/vba]
и с нескрываемым "прискорбием" констатировать, что мы опять приходим туда, откуда пришли :)

Автор - Gustav
Дата добавления - 15.05.2018 в 01:22
Faraway Дата: Вторник, 15.05.2018, 10:30 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Gustav, Спасибо. Тоже решил, что надо работать с фильтром, но не хотел писать об этом, чтоб не наталкивать на такое же мнение. Но хотелось просто узнать, что я не так делаю с QUERY, т.к. сегодня ошибка тут выскочила, а завтра еще где то.
теперь понял, не любит абсолютную пустоту и данные в разнобой. Данные должны быть одного типа.
Код
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))

а как работает эта формула я вообще не знаю. Но мне пока и не надо!
Спасибо еще раз!
 
Ответить
СообщениеGustav, Спасибо. Тоже решил, что надо работать с фильтром, но не хотел писать об этом, чтоб не наталкивать на такое же мнение. Но хотелось просто узнать, что я не так делаю с QUERY, т.к. сегодня ошибка тут выскочила, а завтра еще где то.
теперь понял, не любит абсолютную пустоту и данные в разнобой. Данные должны быть одного типа.
Код
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))

а как работает эта формула я вообще не знаю. Но мне пока и не надо!
Спасибо еще раз!

Автор - Faraway
Дата добавления - 15.05.2018 в 10:30
Gustav Дата: Вторник, 15.05.2018, 12:39 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
=ArrayFormula(ЕСЛИ(СТРОКА(10:50);{"_"\"_"\"_"\"_"}))

а как работает эта формула я вообще не знаю.

Эта формула заполняет символом подчеркивания диапазон таблицы высотой 41 строка (50-10+1) и шириной 4 столбца (по количеству символов подчеркивания в литеральном массиве {"_"\"_"\"_"\"_"}).

Заполнение происходит вниз и вправо от ячейки ввода этой формулы. Если формулу ввести в A1, то получим заполненный символом подчеркивания диапазон A1:D41. Если же формулу ввести, скажем в B10, то в этом случае заполнится диапазон B10:E50. И т.п.

Конечно, удобнее начинать отсчет со строки 1 (а не 10, как у меня в примере выше). Тогда можно построить более-менее универсальную формулу-заполнитель:
[vba]
Код
=ArrayFormula(IF(ROW(1:41);IF(TRANSPOSE(ROW(1:4));"_")))
[/vba]
Эта формула делает то же самое, что и формула с ROW(10:50), но поскольку теперь нумерация строк начинается с 1, то более наглядны верхние пределы - 41 (строка) и 4 (столбца).

Использование вместо комбинации функций TRANSPOSE(ROW(1:4)) одной, вроде бы более "естественной", функции COLUMN возможно, хотя и менее наглядно, поскольку ее аргументами должны быть буквенные обозначения столбцов, а не числа:
[vba]
Код
=ArrayFormula(IF(ROW(1:50);IF(COLUMN(A:D);"_")))
[/vba]

Все эти формулы вводятся в ЕДИНСТВЕННОМ экземпляре в ОДНУ левую верхнюю ячейку нужного диапазона. Т.е. формула - одна, а значений она генерирует в количестве 41x4. Вот она, сила формул массива!


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
=ArrayFormula(ЕСЛИ(СТРОКА(10:50);{"_"\"_"\"_"\"_"}))

а как работает эта формула я вообще не знаю.

Эта формула заполняет символом подчеркивания диапазон таблицы высотой 41 строка (50-10+1) и шириной 4 столбца (по количеству символов подчеркивания в литеральном массиве {"_"\"_"\"_"\"_"}).

Заполнение происходит вниз и вправо от ячейки ввода этой формулы. Если формулу ввести в A1, то получим заполненный символом подчеркивания диапазон A1:D41. Если же формулу ввести, скажем в B10, то в этом случае заполнится диапазон B10:E50. И т.п.

Конечно, удобнее начинать отсчет со строки 1 (а не 10, как у меня в примере выше). Тогда можно построить более-менее универсальную формулу-заполнитель:
[vba]
Код
=ArrayFormula(IF(ROW(1:41);IF(TRANSPOSE(ROW(1:4));"_")))
[/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
Дата добавления - 15.05.2018 в 12:39
Faraway Дата: Пятница, 18.05.2018, 15:02 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Эта формула заполняет символом подчеркивания...

Спасибо за подробное описание, но я так и не понял ничего, не дорос наверное еще.
Если что то понадобится, можно ли обращаться к вам?
 
Ответить
Сообщение
Эта формула заполняет символом подчеркивания...

Спасибо за подробное описание, но я так и не понял ничего, не дорос наверное еще.
Если что то понадобится, можно ли обращаться к вам?

Автор - Faraway
Дата добавления - 18.05.2018 в 15:02
Gustav Дата: Пятница, 18.05.2018, 15:18 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Если что то понадобится, можно ли обращаться к вам?

Да Вы ко всем обращайтесь и непременно здесь, на Форуме, а я "подтянусь", если что. В личку не пишите, в личке я вопросы, мОгущие быть обсУжденными на Форуме, не обсуждаю. В личке - только вопросы с "интересом" (моим).

Для проверки можно проделать такой трюк. Поместите в ячейку CF10 формулу массива, генерирующую несколько десятков строк текстовых значений "_" в колонках CF:CI:
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))

Я только напомню, для чего всё это было сделано. Мы хотели переопределить ("перешибить") числовой тип данных, определяемый по умолчанию, на текстовый - за счет введения в таблицу (в 4 "числовых" столбца) большого ("перешибающего") количества текстовых значений. После того, как мы таким образом "перетянули одеяло" на текстовые значения, у нас появились потерявшиеся заголовки. Но числа - и это обратная сторона этой медали - стали цифровым текстом.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Пятница, 18.05.2018, 15:29
 
Ответить
Сообщение
Если что то понадобится, можно ли обращаться к вам?

Да Вы ко всем обращайтесь и непременно здесь, на Форуме, а я "подтянусь", если что. В личку не пишите, в личке я вопросы, мОгущие быть обсУжденными на Форуме, не обсуждаю. В личке - только вопросы с "интересом" (моим).

Для проверки можно проделать такой трюк. Поместите в ячейку CF10 формулу массива, генерирующую несколько десятков строк текстовых значений "_" в колонках CF:CI:
=ArrayFormula(IF(ROW(10:50);{"_"\"_"\"_"\"_"}))

Я только напомню, для чего всё это было сделано. Мы хотели переопределить ("перешибить") числовой тип данных, определяемый по умолчанию, на текстовый - за счет введения в таблицу (в 4 "числовых" столбца) большого ("перешибающего") количества текстовых значений. После того, как мы таким образом "перетянули одеяло" на текстовые значения, у нас появились потерявшиеся заголовки. Но числа - и это обратная сторона этой медали - стали цифровым текстом.

Автор - Gustav
Дата добавления - 18.05.2018 в 15:18
Faraway Дата: Пятница, 18.05.2018, 16:27 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Хотелось бы этот же документ немного привести к совершенству.
Что именно хочу сделать...
Хочу сделать так, чтоб можно было в сводной таблице выбирать параметры, что именно мне там надо видеть.
Например в данный момент таблице жестко указано, что надо выводить данные, если в столбике 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


Сообщение отредактировал Faraway - Пятница, 18.05.2018, 19:08
 
Ответить
СообщениеХотелось бы этот же документ немного привести к совершенству.
Что именно хочу сделать...
Хочу сделать так, чтоб можно было в сводной таблице выбирать параметры, что именно мне там надо видеть.
Например в данный момент таблице жестко указано, что надо выводить данные, если в столбике 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

Автор - Faraway
Дата добавления - 18.05.2018 в 16:27
Faraway Дата: Пятница, 18.05.2018, 19:14 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Вопрос вроде решил. Сидел, думал, смотрел на другие формулы которые скидывали, увидел что там формулы дополнительно оборачивают в "&"
Было:
Код
WHERE ("JOIN(" OR ";filter(BY3:BY7;BX3:BX7="да"))")

Стало:
Код
WHERE ("&JOIN(" OR ";filter(BY3:BY7;BX3:BX7="да"))&")

оборачивание адреса ячеек вот так
Код
"&C2&"

для меня тоже не понятно. Есть может где то внятное описание когда что и во что надо оборачивать?
 
Ответить
СообщениеВопрос вроде решил. Сидел, думал, смотрел на другие формулы которые скидывали, увидел что там формулы дополнительно оборачивают в "&"
Было:
Код
WHERE ("JOIN(" OR ";filter(BY3:BY7;BX3:BX7="да"))")

Стало:
Код
WHERE ("&JOIN(" OR ";filter(BY3:BY7;BX3:BX7="да"))&")

оборачивание адреса ячеек вот так
Код
"&C2&"

для меня тоже не понятно. Есть может где то внятное описание когда что и во что надо оборачивать?

Автор - Faraway
Дата добавления - 18.05.2018 в 19:14
Gustav Дата: Суббота, 19.05.2018, 19:23 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Есть может где то внятное описание когда что и во что надо оборачивать?

ексель начал изучать относительно недавно. Те функции с которыми работаю, а их не очень много, изучаю мо мере необходимости.

Вот и настал момент осознанно освоить соединение нескольких текстовых строк в одну. Гуглите по слову "конкатенация" или "конкатенация Excel" или "конкатенация Google Sheets". Там расскажут, как и во что "оборачивать".


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Есть может где то внятное описание когда что и во что надо оборачивать?

ексель начал изучать относительно недавно. Те функции с которыми работаю, а их не очень много, изучаю мо мере необходимости.

Вот и настал момент осознанно освоить соединение нескольких текстовых строк в одну. Гуглите по слову "конкатенация" или "конкатенация Excel" или "конкатенация Google Sheets". Там расскажут, как и во что "оборачивать".

Автор - Gustav
Дата добавления - 19.05.2018 в 19:23
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » (QUERY) данные некоторых линеек подтягиваются не полностью (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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