Возможно ли в Excel реализовать следующее:

например имеем таблицу из одного столбца.
В ячейки таблицы последовательно вводятся текстовые данные (ну например перечень каких-либо материалов). Текст может повторяться от ячейки к ячейке, но не обязательно.
Задача:
1. При вводе данных в очередную ячейку необходима реализация выбора из выпадающего списка с перечнем всех ранее (выше) введенных данных, без дубликатов. То есть если один и тот же текст (материал) встречается несколько раз, то в списке его достаточно отразить один раз.
2. Так как данных много и список в итоге получится очень большой необходима реализация фильтрации выпадающего списка. То есть набираю первую букву в ячейке - все что не соответствует (начинается с другой буквы) из списка пропадает, набираю вторую - из списка пропадает то что не начинается на первые две буквы. И т.д. пока не останется не большой список из которого можно выбрать.
Такой механизм необходим для ускорения набора текста в таблице, без копипастинга (не спасает).
В Excel самом по себе реализована подобная функция, только он выдает один вариант когда текст уже точно определен и существует в единственном экземпляре. Этого к сожалению мало, хотелось бы эту функциональность расширить.

да нет тут особого комплекса. Если по хорошему это все на не хилую базу данных тянет, но пока временно хочу упростить хотя бы то что есть. Нужны вспомогательные функции для более быстрого ввода данных. Помимо описанной например такая вот еще функция нужна. Есть две колонки в таблице - номер счет фактуры и дата. Было бы не плохо определять была ли счет-фактура с подобным номером, если была автоматом ставить дату. (функция ВПР если не ошибаюсь?) Эта задачка впрочем по проще, но и времени на ней не много сэкономишь. А вот та функция что описана в начале топика очень необходима, т.к. реально сократит время набора.

viktturПример реализации пополняемого списка.
Да это первый пункт задачи. Реализация нравится. :)
Как насчет второго?

-O_o-Изерлонер,
Да вы правы. ВПР поможет во втором случае. А вот в первом у меня есть еще вопрос, на сколько часто вносятся новые данные в колонку? Я так понимаю - эта колонка служит Вам как "база" с названиями оборудования? То есть она для чего-то потом служит дальше? Опишите.

Ну положим колонок таких не одна а может быть несколько. Где то материалы, где-то поставщики и т.д. На первом этапе новые данные понятно вводятся часто. Со временем и заполнением таблицы, новые данные будут появляться все реже.
Я понимаю к чему вы клоните - сделать отдельную таблицу куда вывести весь возможный перечень, но это в данном случае не вариант. Проще уж тогда сразу базу данных в акцесс ваять. Эта задача стоит. Но пока база не сделана, надо хотя бы улучшить то что есть.

vikttur,

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

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

vikttur,

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

-O_o-Изерлонер,
Я так понял, "оператору" нужно будет вносить наименование товара который продается, а также периодически название поставщиков. Следующий вопрос. Как насчет целостности данных? если он внесет разные названия на один и тот же товар? Потом суммы сходиться не будут... И еще кучу боков вылезет...
авторЕсть две колонки в таблице - номер счет фактуры и дата. Было бы не плохо определять была ли счет-фактура с подобным номером, если была автоматом ставить дату.
И насчет этого не понятно. Куда вносить данные??? и что проверять...
Не продается. Используется в ремонте. Не важно.
Вообще выбор из списка как раз отчасти решает проблему ошибочного ввода данных. К сожалению пример с пополняемым списком настолько красив, насколько же и бесполезен. В списке могут быть тысячи наименований, и без фильтрации по первым символам использовать его не возможно. От формы видимо не уйти :( а это уже уровень программирования, в котором я очень слаб.
Со счет-фактурами как раз просто. Суть в чем - вводим номер счета-фактуры, если такая была введена ранее, ячейка с датой счета-фактуры заполняется автоматически. Реализую сам.
Есть еще одна задача. В таблице которую выложил есть столбец "норма по цеху" Необходимо учитывать сколько того или иного материала было использовано в том или ином цехе на машине с определенным номером. Суммировать отдельные расходы этого материала и сравнивать с нормой по цеху (берется из строки с первым применением материала в конкретном цехе где заполняется вручную). Соответственно проставлять норму в ячейке соответствующей строки. И суммарный расход по цеху и машине в ячейке дополнительного столбца (рядом с нормой по цеху ... столбец пока отсутствует). Насколько могу оценить задача может быть решена средствами ексель, без программирования, но крайне сложно.

AndreTMВсё-таки делали бы вы ЭТО сразу в Акцессе.
Понимаете, вы сейчас бьетесь над тем, для для СУБД уже давно сделано и разжевано.
Забавно :) . Когда я кинулся срочно изучать акцесс и на соответствующую ветку форума, мне посоветовали реализовать все в экселе. :) .... Дойдет очередь и до акцесса, в свободное время (когда оно есть) как раз занимаюсь его изучением с пристрастием. Но текущей работы вал, хочу хоть что-то упростить и ускорить. Понятно что делать полноценную базу данных в экселе это как микроскопом гвозди заколачивать - возможно, но зачем? Но это пока и не требуется. Просто нужна частичная автоматизация того что делается пока вручную.

-O_o-,

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

Для начала несколько пояснений по заполнению таблицы. Оператор получает документ «Требование» в котором описано какие материальные средства были списаны на ремонт машины №ХХХХ в Цехе № Y. На основании «Требования» (в требовании может быть несколько материалов выписанных на одну конкретную машину, на один конкретный цех) он последовательно вносит в таблицу Матзатраты данные – в конец таблицы (внизу)
№ машины, № цеха, вспомогательный код и т.д. по ячейкам таблицы (все необходимые данные есть в Требовании)
Допустим по Требованию списывается пять разных материалов. Следовательно в таблице появится пять новых строк.
По получении следующего Требования оператор точно таким же образом заносит данные в таблицу.
Задача.
1. Подсчет суммарного количества израсходованного материала на ремонт одной машины №ХХХХ в цехе № Y. Например для машины № 1947 в цехе № 1 было израсходовано 6 щеток-утюжков согласно строке 2 и 3 щетки согласно строке 10. Суммарный расход в составляет 9 щеток. Те же самые щетки в строке № 7 не учитываются (у них идет учет по своему цеху) так как использовались в другом цехе, и так же не учитываются щетки в строке 4, т.к. там другой номер машины. В итоге мы должны получить для каждого материала суммарный расход по цеху и по машине.
2. В столбец «норма расхода» я вношу норму расхода для щеток утюжков в цехе № 1 - 8 шт на одну машину (F2). Она должна автоматом проставится для всех материалов называющихся так же, и находящихся в данном цехе.
3. Ну и последнее норма расхода и суммарный расход сравниваются и в случае превышения нормы ячейка с суммарным расходом подсвечивается красным например.

Надеюсь достаточно понятно, проще не получается, а если еще подробнее расписывать – боюсь будет еще хуже.
P.S.: ну а по счет-фактурам я имел ввиду следующее – в тот момент когда оператор внесет номер счет фактуры (столбец L) если счет-фактура с таким номером была внесена ранее, то ячейка «Дата счета фактуры» заполняется автоматически. То есть если оператор внес номер «П-140», автоматом устанавливается дата 28.04.2011 (столбец М) (Предполагается что все данные вводятся оператором в таблицу непосредственно, без специально разработанных форм.)

Изерлонер1. Подсчет суммарного количества израсходованного материала на ремонт одной машины №ХХХХ в цехе № Y. Например для машины № 1947 в цехе № 1 было израсходовано 6 щеток-утюжков согласно строке 2 и 3 щетки согласно строке 10. Суммарный расход в составляет 9 щеток. Те же самые щетки в строке № 7 не учитываются (у них идет учет по своему цеху) так как использовались в другом цехе, и так же не учитываются щетки в строке 4, т.к. там другой номер машины. В итоге мы должны получить для каждого материала суммарный расход по цеху и по машине.


Пункт 1 задачи уже решил путем вставки функции =СУММЕСЛИМН(G:G;A:A;A2;B:B;B2;D:D;D2) в столбец E. :) Оказывается не все так уж и сложно. :)

AndreTM,

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

AndreTM,

www.sql.ru/forum/actualthread.aspx?tid=856104

Изерлонер,

для реализации пункта 2 задачи подскажите пожалуйста функцию аналогичную функции СУММЕСЛИМН, но которая вместо суммирования выбирает наибольшую величину из отобранных по критериям.

-O_o-,

настолько заморочено что даже не понял как это. Хотя работает, и вроде как надо. Вечером дома посижу поразбираюсь. Со сводными таблицами в эксель работать не пробовал.
Не понял зачем вы рекомендуете избавиться от столбца с суммарным значением. Хотя как со сводной таблицей разберусь, может понятно станет. Так то идея была что вот она норма, а вот расход - наглядно видно. И ткнуть работникам допустившим перерасход сразу можно, показав где чего и сколько перерасходовали.

Огромное спасибо. Разберусь, если будут вопросы - напишу.

P.S. я не новичек в экселе. А среди сотрудников чуть ли не "гуру", хотя сам свои возможности и знания отметил бы чуть выше среднего. Просто очень плотно и постоянно работаю в эксель. Но по таким примерам вижу что эксель я по ходу вообще не знаю, несмотря на многолетнюю работу с ним. Есть куда стремится.

-O_o-,

разобрался более менее. Елы-палы! Знали бы вы что сделали! Вы сформировали таблицу с нормами (вкладка - база) хотя я такой задачи даже не ставил. Дело в том что таблица норм в самом деле существует, как отдельный документ со своими полями и данными (и в бумаге с подписями ответственных лиц) поля "цех" и "наименование материала" в ней тоже присутствуют. Точнее этих таблиц три - по материалам, запчастям и гсм, плюс по каждому типу машины, да еще и по агрегатам (принадлежность к типу и к агрегату определяется полем "код"). Вы все увязали в единую систему. Достаточно доработать малость, перенести данные из таблиц с нормами в этот файл, и продолжать здесь же вести учет. В едином файле состоящем из взаимосвязанных таблиц. Все несколько сложнее конечно, но по крайней мере понятно что можно сделать. И не знаю как все это ворочаться будет - за пару месяцев работы я уже более трех тысяч строк набил в той, первой, таблице. За пол года это число перевалит за десяток тысяч (а то и несколько десятков тысяч, учитывая такие же таблицы с отдельными агрегатами), и как будет с этой "базой" работать эксель еще вопрос. Конечно без акцесс никуда. Но все постепенно. Все же акцесс для меня пока неизвестная территория. Представление имею, а опыта ноль.

-O_o-,

Подскажите пожалуйста - есть ли какие-либо ограничения по объему данных для сводной таблицы. Делаю сводную таблицу на основе базовой таблицы состоящей из 4 тыс строк, со столбцами от A до Q. При фильтрации по двум полям обнаруживается что в отфильтрованные данные входит не все что должно бы входить. (если не фильтровать - похоже что в таблице тоже не все). Есть подозрение что должно быть видимо какое-то ограничение на количество данных для сводной таблицы в эксель. :(

Проверил еще раз. Если не фильтровать - по сумме все сходится. Т.е. все данные из базовой таблицы входят в сводную. Однако если отфильтровать - в отфильтрованных данных отсутствуют те что должны там находится. :(

С фильтрацией разобрался. Глюк был связан с разными типами данных в ячейках. Привел все к одному типу.
Подскажите пожалуйста еще, возможно ли в сводной таблице при использовании некоторых полей в качестве наименований столбцов перемещать столбцы так как это надо мне.
Конкретно на примере:
имеем сводную таблицу (см рисунок). Строки - названия материалов, столбцы - цеха где эти материалы используются. На пересечении - фактический расход по цеху. Необходимо здесь же добавить нормативный расход по цеху. Однако когда я его добавляю - столбцы располагаются так - сначала группа цехов и столбец с общим итогом, затем группа норм для цехов. Это не удобно для анализа. Необходимо что бы после каждого столбца с фактическим расходом материала по цеху, тут же шел столбец с нормативным расходом, а в конце так же были итоги по цеху с итоговым (общим) нормативом. Т.е. нужно вклинить после каждого столбца - столбец с нормативом, как это показано на рисунке. Возможно ли такое?

С вопросом выше разобрался. Правда не вполне. К сожалению не знаю как удалить или отредактировать свое сообщение.
Теперь нормы стоят рядом с цехами как положено, только проблема в том что в значении показывается либо сумма норм, либо количество в норме, но не сама норма. :(

Благодарю всех откликнувшихся на мою тему, особенно viktur и о_о. Используя ваши примеры, доработав их, я таки запустил свою базу. Понятно что получилось пока все очень сыро и не эффективно, тем не менее эта работа уже начинает приносить свои результаты, да и другой задачи пока не стояло - это временный вариант в условиях жуткой нехватки времени. Более подходящий вариант буду делать в акцесс несколько позже.
Работа с экселем впрочем далеко еще не окончена. Понятно что и как делать, но сейчас в полный рост поднимается проблема оптимизации. Таблицы перегружены функциями эксель, работает все дико тормознуто - в базовой таблице на данный момент около четырех тысяч строк. Листов в файле около десятка. Может подскажете пути оптимизации и ускорения работы программы? Сейчас вижу только два выхода. Многие использованные функции в качестве своего параметра имеют целый столбец (задано как В:В например), вопрос - Если вложить в такую функцию дополнительную - определяющую конец столбца (по не заполненным ячейкам например) с тем что бы основная функция обрабатывала только диапазон в котором есть данные -даст ли это эффект? Или в этом направлении ничего не получится? Второй вариант - сосредоточиться на изучении VBA благо не плохой учебник под рукой - с тем что бы все трудоемкие операцие выполнялись макросами. Для меня этот вариант пока сложноват, хотя рано или поздно учить все равно придется.
Что бы говорить более предметно я могу выложить файл в тему, только без данных и наверное завтра. Но пока интересует ответ на вопрос с использованием функцией в качестве параметра столбец таблицы. Сильно ли это грузит систему, и надо ли избавляться от такого способа задания параметров?
Спасибо.с экселем впрочем далеко еще не окончена. Понятно что и как делать, но сейчас в полный рост поднимается проблема оптимизации. Таблицы перегружены функциями эксель, работает все дико тормознуто - в базовой таблице на данный момент около четырех тысяч строк. Листов в файле около десятка. Может подскажете пути оптимизации и ускорения работы программы? Сейчас вижу только два выхода. Многие использованные функции в качестве своего параметра имеют целый столбец (задано как В:В например), вопрос - Если вложить в такую функцию дополнительную - определяющую конец столбца (по не заполненным ячейкам например) с тем что бы основная функция обрабатывала только диапазон в котором есть данные -даст ли это эффект? Или в этом направлении ничего не получится? Второй вариант - сосредоточиться на изучении VBA благо не плохой учебник под рукой - с тем что бы все трудоемкие операцие выполнялись макросами. Для меня этот вариант пока сложноват, хотя рано или поздно учить все равно придется.Что бы говорить более предметно я могу выложить файл в тему, только без данных и наверное завтра. Но пока интересует ответ на вопрос с использованием функцией в качестве параметра столбец таблицы. Сильно ли это грузит систему, и надо ли избавляться от такого способа задания параметров?
Спасибо.

-O_o-,


работает! В базовой таблице уже порядка восьми тысяч строк, и они активно продолжают добавляться. Функции использую сложные многоуровневые. Например такую как описал viktur с пополняемым списком (очень активно использую и не единожды). у меня две вкладки в книге на основании этого алгоритма формируются. Я в этих вкладках напротив сформированных позиций проставляю нужные значения, которые затем возвращаются обратно в базовую таблицу по всем строкам на свои места. От сводных таблиц пока отказался - не реализуют того что и как мне надо и сильно грузят память компа. Использую только при необходимости разово - чтобы найти и выдать какие-либо данные, после чего удаляю. Хотелось бы забить еще множество функции, несущих скорее вспомогательную роль, но из-за возникающих проблем пока приходится от этого отказатся.
Основные проблемы следующие:
1) Страшные тормоза. Сохранение и открытие файла занимает до 5 минут времени. При открытии файла постоянно выходит сообщение что не хватает ресурсов. Жму ОК таблица открывается.... пока...
2) Не соблюдение целостности данных. По алгоритму viktura очень здорово формируется нужный список, рядом с которым я проставляю свои данные. Однако при удалении какой либо строки в базовой таблице - весь список может сдвинуться, тогда как данные введенные в ручную, а так же то что я выделил цветом - все остается на месте. Кроме того при простом удалении какой либо строки базовой таблице большинство формул начинают выдавать значение #Н/Д. ,ну эта проблема решается достаточно легко - копированием формул из первых ячеек во всю колонку.... Приходится пока отслеживать такие моменты самому. Если посадить другого человека - через пару кликов мышкой таблица станет неработоспособной.

Варианты решения проблем
1) Оптимизация используемых функций и структуры файла. Все делалось галопом в диком темпе, может наворотил чего лишнего.
2) Кардинальный, но и наиболее реальный. Переход на акцесс.

Последнее время работаю в сумашедшем темпе, сильно за пределами рабочего времени. Времени не хватает ни на что. Как только освобожусь и разгребу все это более менее, выложу получившееся чудо-юдо. Данных естественно не будет, просто для наглядности может набью чего.
Не смотря на все проблемы, криво, косо но база работает. Получилось сформировать данные по пяти ремонтируемым машинам за одну ночь, при том что другой человек над одной машиной бился двое суток (кстати она клялась что мне это сделать не удастся за такое короткое время, поставила даже ящик коньяка на то что я этого к конкретному сроку не сделаю. Я это к конкретному сроку с помощью базы сделал. Теперь вот думаю настаивать ли на ящике, или пожалеть бедную женщину.
....Так что работа, была проведена явно не зря. Однако надо переводить ее на что-то посерьезнее. Но пока нет ни времени, ни знаний.