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

Вход

Регистрация

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

 

= Мир MS Excel/IMPORTRANGE с сортировкой по колонке в таблице приёмнике - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » IMPORTRANGE с сортировкой по колонке в таблице приёмнике (Формулы/Formulas)
IMPORTRANGE с сортировкой по колонке в таблице приёмнике
Kuzmich110 Дата: Среда, 31.05.2023, 13:18 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

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

Пример : https://docs.google.com/spreads....9055806
 
Ответить
СообщениеДоброго времени суток!
Подскажите пожалуйста.
Возможно ли Сделать сортировку по колонке в таблице приёмнике.

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

Автор - Kuzmich110
Дата добавления - 31.05.2023 в 13:18
Gustav Дата: Четверг, 01.06.2023, 02:02 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2701
Репутация: 1123 ±
Замечаний: 0% ±

начинал с 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 листа "Приёмник" вместо имеющейся там сейчас формулы.

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


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЯ понял так, что надо сделать следующее:
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 листа "Приёмник" вместо имеющейся там сейчас формулы.

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

Автор - Gustav
Дата добавления - 01.06.2023 в 02:02
Gustav Дата: Четверг, 01.06.2023, 13:10 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2701
Репутация: 1123 ±
Замечаний: 0% ±

начинал с 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
 
Ответить
СообщениеНекий альтернативный вариант - без функции 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 возвращает все строки (даже пустые) с листа "Исходник" - поскольку в качестве диапазона импорта указан весь лист, без уточнения адресов ячеек.

Автор - Gustav
Дата добавления - 01.06.2023 в 13:10
Kuzmich110 Дата: Пятница, 02.06.2023, 16:04 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

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

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

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

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

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

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

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

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


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЧестно говоря, понял еще меньше, чем из первого сообщения... (

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

Автор - Gustav
Дата добавления - 02.06.2023 в 16:15
Kuzmich110 Дата: Пятница, 02.06.2023, 20:44 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - Kuzmich110
Дата добавления - 02.06.2023 в 20:44
Gustav Дата: Суббота, 03.06.2023, 02:10 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2701
Репутация: 1123 ±
Замечаний: 0% ±

начинал с 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.

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


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

Сообщение отредактировал Gustav - Суббота, 03.06.2023, 02:47
 
Ответить
СообщениеВрезал (как замок) в Вашу ячейку 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.

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

Автор - Gustav
Дата добавления - 03.06.2023 в 02:10
Kuzmich110 Дата: Понедельник, 05.06.2023, 10:45 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Большое спасибо!!!
 
Ответить
СообщениеБольшое спасибо!!!

Автор - Kuzmich110
Дата добавления - 05.06.2023 в 10:45
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » IMPORTRANGE с сортировкой по колонке в таблице приёмнике (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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