IMPORTRANGE с сортировкой по колонке в таблице приёмнике

Старая форма входа

Страница 1 из 1 1
Модератор форума: _Boroda_, китин

IMPORTRANGE с сортировкой по колонке в таблице приёмнике (Формулы/Formulas)

Kuzmich110

Дата: Среда, 31.05.2023, 13:18 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

Доброго времени суток!
Подскажите пожалуйста.
Возможно ли Сделать сортировку по колонке в таблице приёмнике.

Пример : https://docs.google.com/spreads....9055806

Ответить

Gustav

Дата: Четверг, 01.06.2023, 02:02 | Сообщение № 2

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Я понял так, что надо сделать следующее:
1. К данным, полученным по IMPORTRANGE на листе "Приёмник", нужно временно "приставить" колонку G с числами для сортировки.
2. Отсортировать получившийся составной массив по этой последней приставленной колонке - по возрастанию.
3. После чего "отнять" (удалить) сортировочную колонку, уже не меняя порядка записей после сортировки.
4. Предъявить "оставшийся" массив, отсортированный по доп.колонке - как массив, полученный по IMPORTRANGE.

Если всё так, то формула, проделывающая вышеизложенное, может быть такой:
[vba]

Код

=LET(
таблицаИмпорта;     "1J_wTXPmYmm8VQYYNmm5gdkHv6sSKb-Fj2C6tGG_yMxY";
диапазонИмпорта;    "Исходник";
началоСортКолонки;  G1;

исходныйМассив;     IMPORTRANGE(таблицаИмпорта; диапазонИмпорта);
исходныйЗаголовок;  INDEX(исходныйМассив;1;0);
исходныеДанные;     SORT(FILTER(исходныйМассив; ISNUMBER(INDEX(исходныйМассив;0;1))); 1;FALSE);

строкДанных;        ROWS(исходныеДанные);
колонокДанных;      COLUMNS(исходныеДанные);

сортКолонка;        OFFSET(началоСортКолонки;1;0;строкДанных);
составнойМассив;    SORT({исходныеДанные \ сортКолонка}; колонокДанных+1;TRUE);

{исходныйЗаголовок; ARRAY_CONSTRAIN(составнойМассив; строкДанных; колонокДанных)}
)

[/vba]Эту формулу надо поместить в ячейку A1 листа "Приёмник" вместо имеющейся там сейчас формулы.

Если что-то НЕ так, то скажите - подправим.

Код

[/vba]Эту формулу надо поместить в ячейку A1 листа "Приёмник" вместо имеющейся там сейчас формулы.

Если что-то НЕ так, то скажите - подправим.Gustav

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

Ответить

СообщениеЯ понял так, что надо сделать следующее:
1. К данным, полученным по IMPORTRANGE на листе "Приёмник", нужно временно "приставить" колонку G с числами для сортировки.
2. Отсортировать получившийся составной массив по этой последней приставленной колонке - по возрастанию.
3. После чего "отнять" (удалить) сортировочную колонку, уже не меняя порядка записей после сортировки.
4. Предъявить "оставшийся" массив, отсортированный по доп.колонке - как массив, полученный по IMPORTRANGE.

Если всё так, то формула, проделывающая вышеизложенное, может быть такой:
[vba]

Код

Автор - Gustav
Дата добавления - 01.06.2023 в 02:02

Gustav

Дата: Четверг, 01.06.2023, 13:10 | Сообщение № 3

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Некий альтернативный вариант - без функции FILTER (чтобы не ломать голову над условием), но с функцией COUNTA, подсчитывающей кол-во "живых" строк в IMPORTRANGE (по первой колонке):
[vba]

Код

=LET(
таблицаИмпорта;     "1J_wTXPmYmm8VQYYNmm5gdkHv6sSKb-Fj2C6tGG_yMxY";
диапазонИмпорта;    "Исходник";
началоСортКолонки;  G1;

исходныйМассив;     IMPORTRANGE(таблицаИмпорта; диапазонИмпорта);
исходныйЗаголовок;  CHOOSEROWS(исходныйМассив; 1);

строкДанных;        COUNTA(CHOOSECOLS(исходныйМассив;1)) - 1;
колонокДанных;      COLUMNS(исходныйМассив);
исходныеДанные;     SORT(CHOOSEROWS(исходныйМассив; SEQUENCE(строкДанных;1;2;1)); 1; FALSE);

сортКолонка;        OFFSET(началоСортКолонки;1;0;строкДанных);
составнойМассив;    SORT({исходныеДанные \ сортКолонка}; колонокДанных+1;TRUE);

{исходныйЗаголовок; ARRAY_CONSTRAIN(составнойМассив; строкДанных; колонокДанных)}
)

[/vba]
Без определения числа "живых" строк IMPORTRANGE возвращает все строки (даже пустые) с листа "Исходник" - поскольку в качестве диапазона импорта указан весь лист, без уточнения адресов ячеек.

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

Ответить

Kuzmich110

Дата: Пятница, 02.06.2023, 16:04 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

Уфф!
Прошу прощения, ввиду малого опыта наверно немного не правильно объяснил вопрос.
Попытаюсь объяснить весь механизм.
Из таблицы "Приёмник" данные попадают к каждому исполнителю по отдельности.
После того, как исполнитель проставляет дату выполнения и статус эти данные с помощью конгломерации попадают в таблицу источник.
Далее в "Приёмник" нужно вернуть "дату выполнения" и "статус" которые соответствуют ID в таблице "приёмник".

Лист приёмник скорее стоит переименовать, допустим, в "Журнал". В котором есть задача с номером ID, датой поступления и Ответственным.
Когда назначается ответственный, он видит эту задачу у себя в отдельном файле где ставит дату выполнения и статус, которые в свою очередь появляются в журнале.

Пока печатал в голову пришла идея: Может это воплотить через что-то вроде ВПР в Excel?

Ответить

Gustav

Дата: Пятница, 02.06.2023, 16:15 | Сообщение № 5

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Честно говоря, понял еще меньше, чем из первого сообщения... (

Так что куда надо подтянуть в итоге? В первом-то сообщении, речь, вообще, шла о сортировке. А сейчас про ВПР говорите. А это не одно и то же...

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

Ответить

Kuzmich110

Дата: Пятница, 02.06.2023, 20:44 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация: 0 ±

Замечаний: 0% ±

На примере в приёмнике полученные данные из источника.
А нужно получить только 2 столбца (Дата вып и Статус), которые будут привязаны к ID

Ответить

Gustav

Дата: Суббота, 03.06.2023, 02:10 | Сообщение № 7

Группа: Админы

Ранг: Участник клуба

Сообщений: 2797

Репутация: 1161 ±

Замечаний: ±

начинал с Excel 4.0, видел 2.1

Врезал (как замок) в Вашу ячейку J2 на листе "Приёмник" такую формулу:
[vba]

Код

=LET(
таблицаИмпорта;     "1J_wTXPmYmm8VQYYNmm5gdkHv6sSKb-Fj2C6tGG_yMxY";
диапазонИмпорта;    "Исходник";
исходныйМассив;     IMPORTRANGE(таблицаИмпорта; диапазонИмпорта);

заголовокID;        H2;
столбецID;          OFFSET(заголовокID;1;0;ROWS(INDIRECT("A:A"))-ROW(заголовокID));
массивID;           OFFSET(заголовокID;1;0;COUNTA(столбецID));

строкДанных;        COUNTA(CHOOSECOLS(исходныйМассив;1)) - 1;
колонокДанных;      COLUMNS(исходныйМассив);
исходныеДанные;     CHOOSEROWS(исходныйМассив; SEQUENCE(строкДанных;1;2));

колID;              CHOOSECOLS(исходныеДанные; 1);
колДатаВып;         CHOOSECOLS(исходныеДанные; 3);
колСтатус;          CHOOSECOLS(исходныеДанные; 4);
массивРезультат;    MAP(массивID; LAMBDA(c; XLOOKUP(c; колID; {колДатаВып \ колСтатус}; {"" \ ""})));

{{"Дата вып" \ "Статус"}; массивРезультат}
)

[/vba]
Формула будет саморасширяться вниз по мере добавления новых ID по порядку. Поскольку IMPORTRANGE находится в самой формуле, блок данных в левой части листа "Приёмник" можно удалить. Дальше можно удалить пустые начальные столбцы листа "Приёмник", чтобы весь блок, содержащий формулу, оказался прижатым к левому краю листа. Ячейка J2 с формулой при этом переместится в ячейку C2.

Формула начинается прямо из строки заголовков и заголовки двух колонок {"Дата вып" \ "Статус"} являются частью формулы. Это сделано для того, чтобы можно было произвольно менять местами строки данных ниже строки заголовков, включая самую первую строку данных, находящуюся сразу под строкой заголовков. Можно выделять произвольную строку данных кликом на ее номере слева и далее, зажав левую кнопку мыши, перетаскивать строку целиком в новое место (в пределах области данных). Если бы формула располагалась в первой строке данных, как это часто делают в подобных случаях, то первую строку нельзя было бы перемещать таким образом (из-за разрушения формулы).

Врезал (как замок) в Вашу ячейку J2 на листе "Приёмник" такую формулу:
[vba]

Код