Подстановка значений из другого файла

Автор Frank, 4 апреля 2017, 14:32

0 Пользователи и 1 гость просматривают эту тему.

Frank

Доброго времени суток, уважаемые. Нужна помощь, не могу сообразить как сделать задуманное.
Дано: файл с данными вида, скажем, "номер;имя;рост;вес", раскиданными по группам в нескольких листах (скажем, лист "м" и лист "ж").
Нужно: другой файл, в котором в первом поле выбираем из списка "м" или "ж" (имя листа в первом файле, фактически), во втором поле появляется соответствующий выбор "имя" со значениями из столбца в первом файле, ну а в третье поле автоматически проставляется "вес" из того же листа для выбранного "имя".
Что было мною опробовано:
Сделал именованный диапазон в первом файле на каждой вкладке, содержащий "имя;рост;вес", с именем, скажем, "м" и "ж".
Во втором файле сделал именованный диапазон, скажем, "пол", из значений "м;ж" на втором, "расчётном листе".
На первом, "лицевом листе", в первой ячейке прописал Данные - Проверка - Диапазон ячеек, источник - "пол" (именованный диапазон). Появился выбор значений "м" или "ж".
На "расчётном листе" второго файла сделал копию данных из первого файла по каждому из листов, через Лист - Ссылка на внешние данные, выбрал как URL имя первого файла, в доступных таблицах/диапазонах выбрал поочерёдно именованные диапазоны "м" и "ж".
Создал именованные диапазоны из этих данных, имена тоже "м" и "ж" (потому как нельзя использовать именованные диапазоны из другого файла).
Теперь, во вторую ячейку второго файла рисую INDIRECT(A1), что даёт выбор значений "имя;рост;вес" в виде последовательных строчек. Это неудобно, так как строчек в три раза больше чем нужно, список содержит нужные данные, но я не знаю как их не отображать в списке выбора.
Остаётся в третью ячейку второго файла вписать формулу "=VLOOKUP(B1;INDIRECT(A1);3;0)", и в ней автоматом появляется "вес" выбранного экземпляра.
Если в именованный диапазон ("м или "ж") включать только столбец "имя", то в выборе всё красиво, но не будет работать VLOOKUP по этому диапазону. Как быть, чтоб и рыбку съесть? :)

kompilainenn

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

economist

Зачем всё делать на "Проверка данных"?

Я бы слил всё на один лист "пол;номер;имя;рост;вес"

Далее - текст по столбцам - получили 5 столбцов, сделали внятные заголовки

Далее - Сводная таблица.

Что имеем - интерактивность, готовые формулы, авторасширяемый диапазон исх. данных, никаких имен.


Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Frank

Думал что задачка слишком проста, чтобы рисовать примерный файл. Прикладываю.
Нужно получить: другой файл, в котором выбираем в одном поле имя листа из первого файла, затем в другом поле имя из списка на выбранном листе, после чего в третьей ячейке сразу появляется вес выбранного персонажа.
Между именем и весом есть рост, который нас не интересует. В принципе, можно вставку данных из файла-источника сделать два раза, чтобы не включать рост, что сократит (но не устранит) лишние значения в поле выбора.
economist, задачка не совсем абстрактная, фишка в том, что первый файл, на основе которого нужна выборка, делается одним человеком, а используется потом другим человеком для создания другого документа.

JohnSUN

#4
Если правильно впишешь путь и имя файла в B1, то должно работать...
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

economist

#5
Frank - задачка не абстрактная, а наглядно демонстрирующая неправильный подход к хранению и анализу данных. Интерактивный конструктор отчетов - это Сводные таблицы. И неважно что делают это два разных человека. Calc - один.
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Frank

#6
economist, разумеется это временная затычка нормальной системе. Возможно что на сводных таблицах выборка значений делается проще. Мне, правда, мешало с ними поработать то, что в современном LibreOffice 5.1-5.2 в меню Данные - Сводная таблица есть всего два пункта: Обновить и Удалить, а пункт Создать удалён, создание делается через Вставка, о чём в документации забыли сообщить.

economist

Frank - современный LibreOffice - это 5.3, в нём пункт Создать есть.
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

kompilainenn

Цитата: Frank от 11 апреля 2017, 09:48
economist, разумеется это временная затычка нормальной системе. Возможно что на сводных таблицах выборка значений делается проще. Мне, правда, мешало с ними поработать то, что в современном LibreOffice 5.1-5.2 в меню Данные - Сводная таблица есть всего два пункта: Обновить и Удалить, а пункт Создать удалён, создание делается через Вставка, о чём в документации забыли сообщить.
А это изыски команды дизайна, которые наворочали в ГУИ, а документацию-то переписали на английском (и то недавно), а на русский что-то нет желающих ее транслировать
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

Frank

#9
Поковырялся со Сводными таблицами - "можно жить", но приходится все исходные данные сваливать в одну кучу. Получается один большой список для выбора одной позиции. Как сделать сводную таблицу так, чтобы, например, сформировать документ с четырьмя строчками, скажем, по два мужчины и женщины, не ставя четыре галки в огромной портянке "имя" (представьте что там будет суммарно около сотни пунктов), а сократить портянку вдвое (а на самом деле раз в пять), сначала выбрав М/Ж в отдельном поле?
P.S. галки (поля) м/ж в самих исходных данных нет и не может быть, это разделение чисто умозрительное, а даже если его сделать, оно будет бесполезно (так как выбрать всё равно нужно будет оба, еслив выборке и М, и Ж).

economist

Frank - а данные для анализа и должны быть в одной куче.
И тогда ваша задача решается вообще ОДНОЙ формулой с ОДНОЙ функцией:

=СУММЕН() или =SUMIFS() - аналог в Excel =СУММЕСЛИМН()

Ну, если четыре строки - то 4 формулы :-))

Формулы можно написать так, что её аргументы будут ссылаться на цветные ячейки - "поля" для ввода данных. Поставили "м" - выбрали всех мужиков, поставили в вес ">100" - выбрали рекордсменов по весу итп.

Всё это удобно делать когда все данные в одной "плоской" таблице. И ради неё можно двух людей свести вместе и заставить делать правильно. Или слить данные вместе, скажем, макросом.   
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

economist

Вообще проектированию информационных систем очень не хватает простой,  буквально "детской" терминологии. Например такой:

Нас окружают Данные. Данные бывают 3-х типов:
- Все
- Некоторые
- Итоги

Некоторые - это часть Всех. Итоги - это часть Всех или Некоторых, которую просуммировали или пересчитали. И теперь любую задачу можно сформулировать по-детски...
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Frank

Цитата: economist от 11 апреля 2017, 12:09
ваша задача решается вообще ОДНОЙ формулой с ОДНОЙ функцией
Вы таки неверно поняли задачу. Не требуется что-либо считать, требуется из файла содержащего большой список разбитый на несколько категорий (по листу на категорию) выбрать (без использования банального выбрать руками лист - Найти - Копировать - Вставить) на отдельный лист другого файла нужные строки, всё. Складирование всего в одну таблицу заставляет наоборот, использовать поиск, из-за большого количества значений, затрудняющего визуальный поиск и делающий быстрее встроенный поиск по части имени искомого, или копипасту.

rami

Frank, а как насчёт решения которое предложил JohnSUN? По-моему, оно точно соответствует вашим условиям:
Цитата: Frank от  6 апреля 2017, 09:32Нужно получить: другой файл, в котором выбираем в одном поле имя листа из первого файла, затем в другом поле имя из списка на выбранном листе, после чего в третьей ячейке сразу появляется вес выбранного персонажа.

Вы не заметили решение или тема слилась в другое русло?

economist

#14
Frank - действительно, не понял. Решение JohnSUN, похоже да, оно самое.
По сути это можно назвать "Интерактивный VLOOKUP" по другому файлу.
Или Интерактивный подбор строк/значений из другого файла по фильтру

Замечу, что в Таблице data.ods в столбце С - все Имена должны быть уникальны, иначе VLOOKUP/ВПР вернет первое значение веса. Самый простой способ добавить уникальности - полное ФИО. Правда, и здесь бывает засада. У меня из 5 тыс. сотрудников (включая 4 тыс. бывших) - три пары полных однофамильцев, то есть явление это не такое уж и редкое.
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...