Добрый день форумчане.
Сломал голову, но не нашел способа как сделать автозаполнение таблицы данными.
Прошу вашей помощи в данном вопросе. В поиске искал, но не нашел (либо искал плохо, либо нет такого ;))
Постараюсь поподробнее.
Есть документ из 2 листов.
В первом есть исходная информация, где в одном из столбцов есть наименование услуги, в другом стоимость и третий, свободный столбец под заполнение.
По появлению знака в этом третьем столбце на второй лист должны отправляться наименование услуги и стоимость.
И вот вопрос, какими функциями и в каком порядке пользоваться для такого действа?
Прикрепляю видео с описанием:
https://www.youtube.com/watch?v=TArhce7p0jU (https://www.youtube.com/watch?v=TArhce7p0jU)
Видимо не в тот раздел форума написал, прошу прощения ))
ничего страшного, я перенесла тему в правильный раздел. :)
Дубликат сейчас удалю.
Цитата: Helen от 28 марта 2018, 20:21
ничего страшного, я перенесла тему в правильный раздел. :)
Дубликат сейчас удалю.
Примного благодарен! )
AlfA, а можете Вы приложить к сообщению сам документ?
Да, конечно. Оригинал документа не проходит по размеру.
Прикладываю упрощенный вариант))
Как вариант, дальше допиливай сам
ps для удобства "масштабирования" в а4 ввести =IFERROR(MATCH(1;OFFSET(Прайс.$F$2:$F$8;SUM(A$2:A3);0);0);"100") и потом простым копированием заполнить ячейки ниже
AlfA - ваша задача не просто популярна, она характеризует сами электронные таблицы как важный инструмент для бизнеса - Calc как раз решает именно такие задачи, и делает это хорошо.
В предложенном решении от Bigor есть одна проблема: если ваш прайс будет насчитывать сотни позиций - простановка "единичек" в нём будет намного труднее, чем указание конкретных 3-10 услуг (нужных сейчас), выбором из списка. Я бы сразу выложил вам пример "как надо", но... нельзя. Проблема с самим списком. Он большой и его нужно продумать и только потом делать.
Список из сотен позиций - большая проблема для глаз. Это вопросы "номенклатуры", из-за которой в нашей стране как минимум 1,5 млн. человек занимаются ерундой (их называют бухгалтерами - они 60% рабочего времени (5 часов в день) - ищут "как у нас называется то, что у них называется эдак". По большому счету это национальный позор, и я в нем участвую лично. Но главное здесь то, что мы сознательно отказываемся от унификации услуг и товаров, принятой в мире. И это - проблема на миллиарды рублей. Но сами бухи не виноваты, виноваты "мы", потребители, которые "хаваем". И еще есть одна проблема, о которой не принято говорить, но при автоматизации стоит учесть. Во "внутреннем", "настоящем" прайсе должно быть, скажем, так (специально искажаю пример, чтобы не было аналогий с реальностью):
Замена масла в двигателе "щаз" - 500
Замена масла в двигателе утром - 600
Замена масла в двигателе утром после прогрева - 700
Замена масла в двигателе утром после прогрева 10 мин - 1000
В Европе по этому поводу не парятся, ставят всегда последнюю позицию. И да, у них есть классификатор услуг типа нашего ОКПД/ОКВЭД, только нормальный. С этого и стоит начать. А потом уже формулы, важнейшая из которых: =ВПР() или =VLOOKUP()
Она делает ровно то что требуется чаще всего в электронных таблицах: берет что-то из такого-то столбца другой таблицы и ставит в эту, в нужную строку.
Цитата: economist от 28 марта 2018, 21:21=ВПР() или =VLOOKUP()
Я бы не стал использовать. Дешевле выйдет.
Если очень много значений, можно их квалифицировать и поставить Автофильтр.
И на рабочем примере уже смотреть, что нужно дальше ("дайте два", сумма работ и тд.)
tagezi - вот вы только что отговорили человека от изучения основополагающей формулы в электронных таблицах, используя волшебное слово "дешевле" :-)
Классификатор или тот же прайс - уже подразумевает уникальность значений, и функция =ВПР() здесь именно то, что нужно. А когда звучит "автозаполнение" - это тем более оно.
Давайте дождемся ответа ТС и реакции его на варианты от Bigor, и если ТС-у мой совет покажется вредным - уйду в "само-бан", тем более что многие этого давно ждут :-)
Цитата: economist от 28 марта 2018, 22:19отговорили человека от изучения основополагающей формулы в электронных таблицах
В примере есть функция INDEX, которая в данном случае, не только является основополагающей для электронных таблиц, но и позволяет решить сразу два вопроса:
1) Сделать пример более читаемым, а значит и адаптируемым;
2) Не искать "опорную точку" для функции ВПР, что значительно упрощает дальнейшее развитие примера.
Решать задачу можно разными методами, и ваш не хуже. Просто не для поставленной задачи.
По всему что ниже, если есть желание, можете открыть тему в разделе О форуме (http://forumooo.ru/index.php/board,17.0.html) или Разговоры обо всём (http://forumooo.ru/index.php/board,16.0.html). Поговорим, почему мы об этом не мечтаем.
Всем большое спасибо за участие и ответы!
С предложенными вариантами все работает.
Цитата: economist от 28 марта 2018, 23:21
А потом уже формулы, важнейшая из которых: =ВПР() или =VLOOKUP()
Для сложных решений это, наверно, действительно то что нужно. В будущем обязательно с ними поработаю, но пока остановился на решении "Bigor".
economist, tagezi, спасибо!
отдельное спасибо Bigor.
Цитата: Bigor от 28 марта 2018, 21:24
Как вариант, дальше допиливай сам
Из описания функции "INDEX"
=INDEX(ссылка; строка; столбец; диапазон)
Я вижу "INDEX(Прайс.A$2:A$8;B2;1;1)", где ссылка "Прайс.", а дальше что то непонятное )) - строка не строка, столбец не столбец )
Почему диапазон A$2:A$8 не 4-ое значение? Какую задачу выполняет ячейка B2? Одна единица - критерий, при котором будет переноситься запись. Для чего тут вторая единица?
Цитата: AlfA от 29 марта 2018, 13:06=INDEX(ссылка; строка; столбец; диапазон)
INDEX(Прайс.A$2:A$8;B2;1;1)
ссылка на лист Прайс ячейки a2:a8
b2 - содержит номер строки из ссылки
1- номер столбца из ссылки
1- индекс диапазона из ссылки
Цитата: AlfA от 29 марта 2018, 13:06Цитата: Bigor от Вчера в 21:24
Как вариант, дальше допиливай сам
Из описания функции "INDEX"
=INDEX(ссылка; строка; столбец; диапазон)
Я вижу "INDEX(Прайс.A$2:A$8;B2;1;1)", где ссылка "Прайс.", а дальше что то непонятное )) - строка не строка, столбец не столбец )
Почему диапазон A$2:A$8 не 4-ое значение? Какую задачу выполняет ячейка B2? Одна единица - критерий, при котором будет переноситься запись. Для чего тут вторая единица?
На листе "
Заявка на выполнение" есть 2 скрытых столбца: столбец
А и столбец
B. Я так понимаю, вы их не нашли. :)
Функция INDEX ничего не ищет, она просто возвращает значение из указанной ячейки, а в первых двух столбцах, как раз и ищется эта ячейка.
Цитировать=IFERROR(MATCH(1;OFFSET(Прайс.$F$2:$F$8;A8+A7+A6+A5+A4+A3+A2;0);0);"100")
;D тык ;D тык ;D
Цитата: tagezi от 29 марта 2018, 14:51тык Смеющийся тык Смеющийся
:) я исправился ниже в PS
Цитата: Bigor от 29 марта 2018, 16:53
Цитата: tagezi от 29 марта 2018, 14:51тык Смеющийся тык Смеющийся
:) я исправился ниже в PS
Ой, не видел.
Да и пример я только вот посмотрел нормально.
Там кстати напрашивается:
Цитировать=MATCH("[:digit:].*";Прайс.$F$2:$F$8;0)
В этом случае оно будет искать любые цифры, а не только единицы. И можно будет потом этот прайс обсчитывать.
Если конечно хочется иметь возможность, потом когда-нибудь, продавать "лампочки" и "масло".
в видео была единица, поэтому с ней и делал
Цитата: Bigor от 29 марта 2018, 13:33
b2 - содержит номер строки из ссылки
1- номер столбца из ссылки
1- индекс диапазона из ссылки
номер столбца при этом, на сколько понимаю, указывать не обязательно, т.к. он единственный?
индекс диапазона из ссылки.. можно попросить раскрыть описание, как работает, для чего нужен, что делает )) заранее спасибо! )
tagezi, да, действительно даже не обратил внимания, пока пальцем не ткнули ))
Ну и с этого момента, впринципе, все встало на свои места.
Ну и раз уж так все гладко пошло, подскажите, если лист с данными не один, а несколько, задача сильно усложняется?)
Цитата: AlfA от 29 марта 2018, 17:57номер столбца при этом, на сколько понимаю, указывать не обязательно, т.к. он единственный?
Да, достаточно 2-х параметров ссылки и номера строки
С несколькими листами сложнее, хотя вчера мне и с одним листом задача казалась сложной :)
Цитата: AlfA от 29 марта 2018, 17:57Ну и раз уж так все гладко пошло, подскажите, если лист с данными не один, а несколько, задача сильно усложняется?)
Цитата: Bigor от 29 марта 2018, 18:14С несколькими листами сложнее, хотя вчера мне и с одним листом задача казалась сложной Улыбка
Решаем задачу для каждого листа по отдельности, потом объединяем их (http://forumooo.ru/index.php/topic,7036.0.html).
Ну, хотя конечно, можно упереться рогом и решить всё в кучу. А нужно?
Да проще сделать как tagezi советует, для каждого листа с данными делаем заявку, а потом сливаем их в одну.
А иначе рог можно сломать :)
А с помощью матриц не проще? Кстати, почему у меня крашится LibreOffice если во втором ROW попытаться удалить содержимое скобок?
Удалял из второго ROW и из обоих, нормально работает вот так: {=IFERROR(INDEX(A2:A8;SMALL(IF(F2:F8=1;ROW( )-1;"");ROW()-1));"")}
Какой у вас офис? У меня LO 5.0.6
либра крэш репорт отправляет? шаги для повторения есть четкие? отпишитесь на багзиллу, если да/да
rami
У меня тоже этот был, но я затем проинсталлировал 6.0.2.1 (x64) и на обоих одно и тоже. У меня крашится даже если попытаться сделать матричную функцию всего лишь с одним ROW(). Может от Windows ещё зависит - у меня немецкий 8.1.
kompilainenn
Либра пытается отправить крэш репорт, но я пока не разрешаю.
Цитата: celler от 30 марта 2018, 17:22А с помощью матриц не проще?
Цитата: celler от 30 марта 2018, 18:16У меня крашится даже если попытаться сделать матричную функцию всего лишь с одним ROW().
С первого раза не понял о каких матрицах вы говорите, в русскоязычном офисе это называется "формула массива".
Если убрать ссылки из ROW(), нельзя будет переносить формулы на другое место (будут показывать не правильно)
rami
С первого раза не понял о каких матрицах вы говорите, в русскоязычном офисе это называется "формула массива".
Прошу прощения, у нас в LibreOffice там птичка Matrix,- так вот и обозвал.
Если убрать ссылки из ROW(), нельзя будет переносить формулы на другое место (будут показывать не правильно)
Во втором ROW() из формулы, насколько я понимаю, ссылка совсем не нужна - она относится к строкам результата. Я после своих экспериментов оставил и выложил не совсем корректный вариант. Там правильнее была бы формула {=IFERROR(INDEX(A2:A8;SMALL(IF(F2:F8=1;ROW(A2:A8)-1;"");ROW(I2:I8)-1));"")}, если без ссылки никак не получается. А при переносе формул в любом случае нужно будет при необходимости корректировать количество вычитаемых строк, находящихся выше зоны результатов.
Цитата: celler от 30 марта 2018, 19:08у нас в LibreOffice там птичка Matrix
Не знаю про птичке в вашем LibreOffice, но в справке на английском это Array Functions (https://help.libreoffice.org/Calc/Array_Functions).
Цитата: celler от 30 марта 2018, 19:08А при переносе формул в любом случае нужно будет при необходимости корректировать количество вычитаемых строк, находящихся выше зоны результатов.
Проблема с формулами массива в том, что при незначительном расширении, работы может быть намного больше. Вместо того, чтобы просто взять и растянуть формулу, нужно её переделывать.
Кроме того, вариант, предложенный
Bigor, очень просто расширяется во все стороны куда пожелаешь, без переделок основных формул.
Я не говорю, что ваш вариант не правильный, просто он в данном случае создает человеку дополнительные сложности, на мой взгляд. Но имеет право быть :)
Если вы смотрели видео, то там такая "красота", что я не смог понять что нужно делать. Любые наши решения автору вопроса прийдётся адаптировать под свою "красоту". Желательно такое решение, чтобы можно было просто переносить формулы в любое место на любой лист и растягивать диапазоны без утомительных корректировок. Я сделал решение с дополнительным столбцом id:
Решений по сути может быть просто не мерено.
Вот простой пример с элементарными формулами. Если нужно печатать, то третий столбец не будет виден. А всё остальное оно делает. Только нужно отсортировать, буквально клацнуть на кнопке... но при этом позволяет дополнительно без геморроя отсортировать по цене, имени и тд. Только крестиком не вышивает :-[
Плохой вариант решения? :roll:
ЗЫ: Каждый использует то что ему удобно под конкретные нужды. :)
UDP: Кстати, это можно даже на первом листе провернуть, вообще без формул, прям в той таблице, если второй лист только конечного результата нужен.
tagezi
Не знаю про птичке в вашем LibreOffice, но в справке на английском это Array Functions.
А на немецком это Matrixfunktionen (https://help.libreoffice.org/Calc/Array_Functions/de).
Проблема с формулами массива в том, что при незначительном расширении, работы может быть намного больше. Вместо того, чтобы просто взять и растянуть формулу, нужно её переделывать.
Можно заранее задать пределы массивов с запасом. И ещё я слышал, что формулы массивов работают эффективнее - для даже большого массива это всего лишь одна формула, а не множество для каждой строки.
А вообще конечно замечательно, что есть много вариантов решения одной и той же задачи.
Цитата: tagezi от 30 марта 2018, 21:33
Решений по сути может быть просто не мерено.
Вот простой пример с элементарными формулами. Если нужно печатать, то третий столбец не будет виден. А всё остальное оно делает. Только нужно отсортировать, буквально клацнуть на кнопке... но при этом позволяет дополнительно без геморроя отсортировать по цене, имени и тд. Только крестиком не вышивает :-[
Плохой вариант решения? :roll:
ЗЫ: Каждый использует то что ему удобно под конкретные нужды. :)
UDP: Кстати, это можно даже на первом листе провернуть, вообще без формул, прям в той таблице, если второй лист только конечного результата нужен.
tagezi, ты видел как выглядит оригинальный документ (на видео)? После сортировки ты концов не найдёшь.
Цитата: rami от 30 марта 2018, 20:03tagezi, ты видел как выглядит оригинальный документ (на видео)? После сортировки ты концов не найдёшь.
Ну, потому я и не настаиваю :)
Я считаю, что пошагово формулами лучше. А пример привел просто, чтобы показать, что вариантов, будет чуть меньше, чем людей решающих вопрос.
Там в видео есть интересный момент - "...должна самостоятельно заполняться в последовательности простановки пожеланий клиента". Если это действительно важно, то без каких-нибудь макросов это не сделать. В качестве альтернативы можно в графе "ДА!" вместо единиц проставлять либо нумерацию, либо дату и выводить в соответствующей последовательности.
celler, почему Вы не пропускаете краш репорт разработчикам?
kompilainenn
почему Вы не пропускаете краш репорт разработчикам?
Пропустил (http://crashreport.libreoffice.org/stats/crash_details/ec8bcece-635c-4744-8e63-dd09d3b09025), но ничего там не понял.
celler, шаги для уверенного повторения креша имеются? если да, то крайне желательно оформить багу в багзилле , и там в соответствующем поле добавить ссылку на крашрепорт
Цитата: celler от 30 марта 2018, 22:36
Там в видео есть интересный момент - "...должна самостоятельно заполняться в последовательности простановки пожеланий клиента". Если это действительно важно, то без каких-нибудь макросов это не сделать. В качестве альтернативы можно в графе "ДА!" вместо единиц проставлять либо нумерацию, либо дату и выводить в соответствующей последовательности.
Как раз думал об этом моменте еще до написания первого поста, и последовательность выбора клиента как раз не нужна, итоговый результат удобнее видеть в последовательности прайса.
Решений действительно много)).. но простота - сестра таланта как говориться)), и самый простой, удобный, а главное РАБОТАЮЩИЙ способ уже предложен ))
Спасибо еще раз всем - Вы, товарищи, гении! ))
Думаю темку можно закрывать! ))
Здравствуйте!
объясните, пожалуйста, как в рассмотренном примере исключаются строки с отсутствующим критерием "1"?
Цитата: ArsKam от 4 мая 2018, 11:53
Здравствуйте!
объясните, пожалуйста, как в рассмотренном примере исключаются строки с отсутствующим критерием "1"?
Добрый день!
Имеется ввиду, как сделать так, что бы отбор происходил не только по "1"? - используй вместо "1" - "[:digit:].*"
Или вопрос в том, как в целом собираются данные, у которых проставлен критерий "1"? Тогда просто надо перечитать тему с начала, тут все очень подробно )), даже мне, деревянному, стало понятно )
Цитата: AlfA от 4 мая 2018, 10:00как сделать так, что бы отбор происходил не только по "1"? - используй вместо "1" - "[:digit:].*"
Или вопрос в том, как в целом собираются данные, у которых проставлен критерий "1"?
это вроде понятно)
пытаюсь понять, как в заявку вообще не попадают строки без "1". предполагаю, что для этого и нужны значения (как счётчики) в скрытых столбцах, наверное)
Цитата: ArsKam от 4 мая 2018, 12:39
Цитата: AlfA от 4 мая 2018, 10:00как сделать так, что бы отбор происходил не только по "1"? - используй вместо "1" - "[:digit:].*"
Или вопрос в том, как в целом собираются данные, у которых проставлен критерий "1"?
это вроде понятно)
пытаюсь понять, как в заявку вообще не попадают строки без "1". предполагаю, что для этого и нужны значения (как счётчики) в скрытых столбцах, наверное)
Да. В первом скрытом столбце функция MATCH ищет значения, а второй столбец формирует их очередность из первого столбца. Таким образом строки, не имеющие критерия "1" не попадают в итоговый список.
в своём похожем примере, но с несколькими столбцами с критериями, собирался использовать впр (vlookup), но не знал, как избавиться от ненужных строк с пустыми критериями.
подскажите, пожалуйста, не проще ли использовать в таком случае сводные таблицы?