Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
[ Закрыто ] Гуру построения простых Баз Данных (БД), ПОЖАЛУЙСТА, помогите оптимизировать мою БД. (ЗАКРЫТА по причине "поломки")
 
#1
21.08.2010 17:38:48
Здравствуйте!  
Пожалуйста, помогите решить следующую проблему в Excel 2003 EN.  
Строю простую базу данных в Excel 2003 EN для малого предприятия розничной торговли.  
 
УСЛОВИЯ ЗАДАНИЯ:  
Макс. кол-во поступаемых/реализуемых артикулов предприятия: 168 единиц.  
Макс. общий суммарный (по всем поступаемым/реализуемым артикулам) годовой товаро оборот: 2000 единиц.  
Макс. срок деятельности предприятия 3 года, а следовательно макс. реализация не превысит 6000 единиц.  
Всё предприятие: склад хранения и пункт реализации поступаемых товаров расположены  в одном помещении.  
Учёт товаров на предприятии: количественно-суммовой.  
 
Для того, что бы БД на протяжении всего времени своего существования работала как можно шустрее, в обязательном порядке необходимо найти и применить ряд комплексных приёмов, способов и методов для оптимального решения данного задания:  
1. Размер файла cамой БД (рабочей книги *.xls) должен быть как можно меньше.  
2. Не применять макросы и формы на VBA при построении данной простой БД.  
3. И т.д...  
 
ЗАДАНИЕ:  
Построить в Excel 2003 EN БД для удобного и оперативного ввода и последующего контроля поступаемым/реализуемым артикулов.  
Для решения применил следующий СПОСОБ:  
1. Кол-во листов рабочей книги ="Общий лист Все приходы"+"Общий лист Все уходы"+кол-во листов с каждым (уникальным) артикулом 168+1+1=170.  
2. Каждый лист артикула абсолютно одинаков по форме, отличия только в названиях самих листов:  
Названия каждого листа соответствует названию одного (уникального) артикула!  
И все данные уходов/приходов содержащиеся в каждом листе артикула строго соответствуют только одному (этому самому уникальному) артикулу!  
(Столбцы: Артикул/Цена 1шт./Продано наличные шт./Продано фактуры шт./Переведено на брак шт./Сумма наличные/Сумма фактуры/Дата продажи/Продавец Имя  
далее:  
Поступило всего за всё время шт./Брак шт./Продано шт./Остаток шт.  
далее:  
Артикул/Поступило на склад шт./Дата поступления на склад.  
 
3. В "Общий лист Все приходы" заносятся все данные о поступивших артикулах  
(Столбцы: Артикул/Кол-во/Дата поступления/Уплачено денег поставщику).  
 
4. В "Общий лист Все уходы" заносятся все данные о реализованных артикулах  
(Столбцы: Артикул/Цена 1шт./Продано наличные шт./Продано фактуры шт./Переведено на брак шт./Сумма наличные/Сумма фактуры/Дата продажи/Продавец Имя).  
 
Таким образом при оприходовании товара пользователь вводит вручную данные о всех артикулах в "Общий лист Все приходы", и эти же данные вводит в лист каждого артикула в столбцы приходов!!!  
 
Что бы свести к нулю вероятность ошибок (забывчивости, невнимательности при вводе данных) в БД применил возможность "самоконтроля":  
Двойной ввод данных (дублирование) сделано для обязательной возможность верификации (сравнения контрольных сумм кол-ва артикулов)  
в листах: "Общий лист Все приходы" и "Общий лист Все уходы"!!!    
На прикреплённом примере это отлично видно.  
Таким образом пользователь всегда сможет проконтролировать, правильно ли он одновременно ввёл данные:  
1. При приходе товара в "Общий лист Все приходы" и в отдельные листы каждого артикула!  
2. При уходе товара в "Общий лист Все уходы" и в отдельные листы каждого артикула!    
Так же есть мысль каким то образом ЗАЩИТИТЬ пользователя от возможных ошибок при дублировании ввода артикулов  
"ПЕРЕСОРТИЦЫ" в отдельные листы каждого артикула!  
Т.е. например исключить ввод "Артикула1" из "Общего листа Всех приходов" в лист "Артикула2" и т.д...  
В идеале данный процесс неплохо было бы полностью автоматизировать, о чём упомянул в конце поста:  
ВТОРОЙ СПОСОБ.  
 
P.S.  
Очень долго думал над тем, как лучше всего создать БД для оптимального решения УСЛОВИЯ данного ЗАДАНИЯ...  
Ничего лучшего на ум не пришло...  
Была (есть и сейчас) мысль реализовать ВТОРЫМ СПОСОБом построение данной БД:  
Построить БД только с двумя листами рабочей книги:  
1. В "Общий лист Все приходы" заносятся все данные о поступивших артикулах.  
2. В "Общий лист Все уходы" заносятся все данные о реализованных артикулах.  
НО ВОЗМОЖНО ЛИ В Excel 2003 ПОЛНОСТЬЮ АВТОМАТИЗИРОВАТЬ ПРОЦЕСС, КОГДА НА ОДНОМ ЛИСТЕ АВТОМАТИЧЕСКИ ВЫВОДИТСЯ ОСТАТОК ПО КАЖДОМУ АРТИКУЛУ И Т.Д.???  
Честно говоря, (не уверен в этом) Excel 2003 вряд ли способен на такое...  
Пожалуйста, если кто то из Гуру planetaexcel.ru может помочь советом/подсказкой по оптимизации/улучшению первого и второго  
(если второй способ вообще реализуем) способам, а так же Вашим авторским идеям и способам для решения данного простого задания БД,  
пожалуйста,помогите...  
 
В целом, построение БД первым способом не вызвало у меня особых трудностей, если не считать самой главной проблемы:  
При 6000 строк в каждом листе артикула (а всего листов должно быть 170) я ожидал получить БД в Excel 2003 приемлемых размеров для удобной работы...  
Но при построении БД, при копировании листов "Артикулов" доведя кол-во листов до 17, БД "разбухла" до 130MB и Excel 2003 упортно отказывался продолжать  
далее выполнять любые мои попытки увеличить кол-во листов с артикулами более 17!!!  
Выкидывая следующее сообщение:  
Excel cannot complete this task with available resources. Choose less data or close other applications.  
Excel не может выполнить эту задачу с учетом имеющихся ресурсов. Выберите меньше данных или закройте другие приложения.  
При этом свободного объёма RAM PC было около 150MB!!! Не считая свободных 2GB "виртуальной памяти" на HDD...  
Почему Excel 2003 мне не удалось заставить больше увеличить (копированием) кол-во листов в рабочей книге, остаётся загадкой...  
Tools--->Options-->General-->Sheets in new workbook устанавливаю любое!!! (от 1 до 170) проблему не решает...  
 
Заранее благодарен всем создателям и участникам planetaexcel.ru!!!  
Земной поклон Вам, Мегареспект и Уважение!!!  
<EM><STRONG>Файл удален</STRONG> - велик размер - [<STRONG>МОДЕРАТОРЫ</STRONG>]</EM>
 
 
 
#2
21.08.2010 17:48:58
Dmdon, такого объёма файл надо на сторонний ресурс класть, а сюда ссылку.
 
 
 
#3
21.08.2010 18:24:30
{quote}{login=Hugo}{date=21.08.2010 05:48}{thema=}{post}Dmdon, такого объёма файл надо на сторонний ресурс класть, а сюда ссылку.{/post}{/quote}  
Объём прикреплённого за-архивированного файла примера 840 KB (в распакованном виде 18MB).  
Извиняюсь, если превысил допустимый на форуме лимит...  
http://ifolder.ru/18975610  
Исправился :-)
 
 
 
#4
21.08.2010 18:26:42
Для удобства кол-во листов "Артикулов" в нём принудительно уменьшено до 6.  
Для удобства кол-во строк в листах "Артикулов" принудительно уменьшено до 61.
 
 
 
#5
21.08.2010 22:57:22
{quote}{login=dmdon}{date=21.08.2010 06:26}{thema=Re: Re: }{post}Для удобства кол-во листов "Артикулов" в нём принудительно уменьшено до 6.  
Для удобства кол-во строк в листах "Артикулов" принудительно уменьшено до 61.{/post}{/quote}  
а access вам не подойдет?  
артикулов мало , критерии вроде постоянные если excel то лучше один лист справочник артикулов, второй продажи и поступления, третий сводная таблица  
по поводу макросов..... чего вы их так боитесь?  
 
могу решить вашу задачу с помощью OLAP SERVER  и клиента в екселе, это если нужен многопользовательский режим с контролем доступа к данным  
......но это уже другая песня... dg_rusak@mail.ru  
_________  
26900
Спасибо
 
 
 
#6
21.08.2010 23:08:55
{quote}{login=dmdon}{date=21.08.2010 05:38}{thema=Гуру построения простых Баз    
 
УСЛОВИЯ ЗАДАНИЯ:  
 
3. И т.д...  
 
{/post}{/quote}  
 
 
классное условие
Спасибо
 
 
 
#7
21.08.2010 23:32:38
Ну ни фига себе ТЗ! У меня терпения не хватило прочитать до конца. dmdon, хотите помощи - разбейте задание на несколько маленьких. А то "Война и мир"...
Я сам - дурнее всякого примера! ...
 
 
 
#8
21.08.2010 23:40:41
{quote}{login=KuklP}{date=21.08.2010 11:32}{thema=}{post}Ну ни фига себе ТЗ! У меня терпения не хватило прочитать до конца. dmdon, хотите помощи - разбейте задание на несколько маленьких. А то "Война и мир"...{/post}{/quote}  
 
тебе Серега по праву достается 3 пункт :) самый короткий  
________  
84346
Спасибо
 
 
 
#9
21.08.2010 23:43:54
Только на третий и соглашусь:-) И его только в твоем посте прочитал. Так бы не дошел.
Я сам - дурнее всякого примера! ...
 
 
 
#10
22.08.2010 00:23:40
{quote}{login=R Dmitry}{date=21.08.2010 10:57}если excel то лучше один лист справочник артикулов, второй продажи и поступления, третий сводная таблица  
{/post}{/quote}  
R Dmitry, спасибо!  
Буду пробовать построить БД с тремя листами в рабочей книге.  
Лист1: "Справочник артикулов" (согласен идеально всё получится).  
Лист2: "Продажи и поступления" (согласен идеально всё получится).  
Лист3: "Сводная таблица" EN: Pivot Table and Pivot Chart Report...  
Обожаю данный мощный инструмент в Excel 2003 и пользуюсь им много лет...  
Насколько я представляю Pivot Table не при каких обстоятельствах не способен сам автоматически высчитывать остаток товаров по каждому артикулу из Листа2...  
Пожалуйста, поправте меня если ошибаюсь...  
Для того, что бы получить данные о остатке (наличии) того или иного артикула, отчёт с пом. Pivot Table придётся делать самостоятельно вручную, либо в конце каждого рабочего дня, либо раз в месяц...  
Пользователю необходимо за считаные секунды, сразу после ввода данных в Лист2:  
"Продажи и поступления" видеть кол-во (остаток) каждого артикула в Листе3.  
 
Если в Excel 2003 c пом. Pivot Table этот процес возможно автоматизировать и автоматически выводить данные об "Остатке" атрикулов в Лист3 из данных Листа2: "Продажи и поступления"...  
Данный вариант для моей БД был бы идеальным!!!  
Обязуюсь в ближайшее время построить БД по Вашему варианту,  
и выложить пример.  
Только как автоматизировать расчёты в Листе3, понятия не имею :(((
 
 
 
#11
22.08.2010 12:21:58
дерзайте пункты 1 и 2 а затем сводную с расчетами тут с ваяем
Спасибо
 
 
 
#12
22.08.2010 13:41:22
вот вам маленький пример сводной с вычисляемым остатком  
и пример построения б.д.  
сводная строится на динамическом диапазоне
Спасибо
 
 
 
#13
22.08.2010 16:49:33
{quote}{login=R Dmitry}{date=22.08.2010 01:41}{thema=}{post}вот вам маленький пример сводной с вычисляемым остатком  
и пример построения б.д.  
сводная строится на динамическом диапазоне{/post}{/quote}  
R Dmitry, спасибо Вам огромное за помощь!  
Скачал Ваш файл, открыл, но не смог да же открыть  
не один раскрывающийся список в ячейках:  
J2 (Все).  
J4 Значения.  
I5 Названия.  
 
Мой Excel 2003 выдает сообщение:  
This Pivot Table was created in a later version of Excel and can't be updated in this version. To update it, click OK, and then open the workbook in the version of Excel it was originally created in.  
Эта сводная таблица была создана в более поздней версии Excel и не может быть обновлена в этой версии. Для ее обновления, нажмите кнопку ОК, а затем откройте книгу в Excel версии в которой она была создана первоначально.  
 
Ваш файл, наверно создавался в Excel 2007 или Excel 2010.  
И судя по всему мой самый любимый на свете инструмент:  
Excel 2003 EN: Pivot Table and Pivot Chart Report,  
как я и предполагал ранее не способен автоматически показывать "остаток".  
А вот более поздние версии Excel 2007/2010 вернее их Pivot Table,  
более продвинутые и мощные!!! И в них, как раз можно реализовать то,  
над чем я безрезультатно бьюсь несколько лет...  
 
R Dmitry, пожалуйста,  
1. Подтвердите/опровергните мою мысль касаемо отличий  
Pivot Table2003 от 2007/2010.  
2. Подскажите, пожалуйста, как дальше мне продолжить работу над моей БД.  
Насколько я понимаю, мне сейчас срочно в экстренном порядке необходимо установить на свой PC более позднею версию Excel 2007 или Excel 2010.  
А лучше всего (идеально) точно такую же, как у Вас :-)  
У меня только один физический HDD 160GB (разделен на два виртуальных),  
пол года назад скачал с оф сайта Excel 2010 RU BETA и пытался установить его на свой домашний комп, но при интсаляции получил сообщение, что инсталляция не возможна, необходимо полное удаление ранее установленной версии (Excel 2003 EN).  
Я очень испугался удалять на своем домашнем компе свой Excel 2003 EN и не стал это делать, т.к. у меня сотни очень ценных файлов созданных мной именно на моем домашнем компе в Excel 2003 EN!  
"Страх" был вызван риском, что многие файлы созданные в Excel 2003 EN после установки Excel 2010 RU могут вообще не открыться или часть функций потеряется.  
Например у меня на работе уже давно установлен Excel 2010 RU BETA, и почти все файлы созданные на моем рабочем ПК в Excel 2010 RU BETA,  
НЕ ОТКРЫВАЮТСЯ ВООБЩЕ на моем домашнем ПК в Excel 2003 EN :(((  
 
Что делать???  
Докупать второй HDD и заливать на него отдельно Excel 2010 RU?  
Это единственный выход:) ???...
 
 
 
#14
22.08.2010 16:56:05
да делал в 2007, 2003 под рукой нету и точно не смогу сказать на ваш вопрос  
но, если даже сводная это не делает, то все можно сделать с помощью формул  
типа суммесли
Спасибо
 
 
 
#15
22.08.2010 17:00:09
вы сначала определитесь с форматом бд и справочником  
затем на странице итогов сделайте то что вы хотите получать в итоге  
и будет вам счастье :-)
Спасибо
 
 
 
#16
22.08.2010 17:05:26
dmdon, файлы созданные в ранних версиях Экс, однозначно будут открываться в более поздних версиях. Так я своим 2003 легко открываю файлы писанные мною еще в Экс5, под ДОС(Win3.11). Чего не скажеш об обратной совместимости:-(  
"Докупать второй HDD и заливать на него отдельно Excel 2010 RU?  
Это единственный выход:) ???..." А что это даст? Все равно при установке он затрет настройки 2003. Или вы планируете туда же поставить еще один экземпляр операционки? Кажется, у The_Prist(модератор форума) стоят обе версии Экса. Спросите его - как.  
 
14774
Я сам - дурнее всякого примера! ...
 
 
 
#17
22.08.2010 17:15:08
У меня тоже на работе стоит 2 версии и нормально только я сначала запускаю с ярлыка 2003 а затем открываю файл , что бы открыть его в 2003
Спасибо
 
 
 
#18
22.08.2010 17:35:20
Для того, чтобы сводные таблицы, созданные в версии 2007 и позднее, работали в версии 2003, нужно просто, чтобы они изначально создавались в формате 97-2003 в режиме совместимости. И всего делов :)
KL
 
 
 
#19
22.08.2010 17:47:02
Вот пример R Dmitry в нужном формате.
KL
 
 
 
#20
22.08.2010 18:06:07
вот посмотрите специально для вас поставил 2003 офис  
хотя давно собирался
Спасибо
 
 
 
#21
22.08.2010 18:11:41
О тут уже ответили спасибо KL :)  
 
при использовании диапазона сводной лучше используйте динамический  
=СМЕЩ(Лист1!$A$1:$E$1;0;0;СЧЁТЗ(Лист1!$A:$A))  
тогда не надо будет расширять диапазон сводной  
:-) сам опредилиться  
_____  
83236
Спасибо
 
 
 
#22
22.08.2010 19:39:11
R Dmitry, KL спасибо!  
Ваши примеры открылись, прекрассно работают...  
К сожалению они не решают моей самой главной задачи:  
{quote}{login=dmdon}{date=21.08.2010 05:38}{thema=Гуру построения простых Баз Данных (БД), ПОЖАЛУЙСТА, помогите оптимизировать мою БД.}{post}  
В Excel 2003 ПОЛНОСТЬЮ АВТОМАТИЗИРОВАТЬ ПРОЦЕСС, КОГДА НА ОДНОМ ЛИСТЕ АВТОМАТИЧЕСКИ ВЫВОДИТСЯ ОСТАТОК ПО КАЖДОМУ АРТИКУЛУ{/post}{/quote}  
На Ваших примерах Pivote Table идеально красиво работает,  
(сам всегда похожим методом делаю отчёты)    
но к сожалению не решает самой главной поставленой задачи,  
т.к. данные о:  
"ОСТАТКЕ" НЕ ОБНОВЛЯЮТСЯ АВТОМАТИЧЕСКИ ПРИ ИЗМЕНЕНИЯХ ДАННЫХ ПРИХОДА И УХОДА АРТИКУЛОВ!  
Придётся каждый раз вручную запускать Pivote Table, что бы получить последние данные о "ОСТАТКЕ" после любых изменений в столбцах с данными "ПРИХОДА И УХОДА" :-)  
 
R Dmitry,  
Сделал, как Вы рекомендовали:  
1лист: "справочник артикулов".  
2лист: "продажи и поступления".  
3лист: "сводная таблица".  
 
Только из: 1лист Excel 2003 не разрешает брать данные для  
раскрывающегося списка для: лист2.  
Data-->Validation-->List-->Source---без шансов...  
Но я всё равно сделал, как Вы сказали:  
<если excel то лучше один лист справочник артикулов, второй продажи и поступления, третий сводная таблица>  
 
1лист: "справочник артикулов".  
Я сделал так:  
Data-->List-->Create List...  
Правда, как связать "справочник артикулов" из:  
1лист с 2лист: "продажи и поступления" пока не знаю :-)  
 
Сылочка на обещаный мной пример БД :)  
http://ifolder.ru/18986325
 
 
 
#23
22.08.2010 19:49:41
"Только из: 1лист Excel 2003 не разрешает брать данные для раскрывающегося списка для: лист2.  
Data-->Validation-->List-->Source---без шансов..."  
Создайте именованный диапазон, и будет Вам счастье (выпадающий список с данными с другого листа).
 
 
 
#24
22.08.2010 22:00:53
я бы предложил такой вариант  
вычисления я оставил в сводной, раз в сто быстрее работать  будет при больших объемах  
насчет автоматического пересчета это к формулистам, но это уже будет не сводная.  
Исходная таблица им понравиться там правильно все организовано  
 
хотя сводная наверное лучше и для автоматического вычисления отделяет один клик мышкой "обновить данные"  
я могу конечно сделать кнопку или на автомат но это уже макросы.  
А Вы писали что без них  
Во общем чем смог тем помог.  
6.8 кб  
Удачи Вам!  
_______  
66094
Спасибо
 
 
 
#25
22.08.2010 22:21:05
здесь сводная с динамическим диапазоном  
26.5 кб против ваших на стороннем ресурсе 9.44 мб
Спасибо
 
 
 
#26
23.08.2010 00:57:50
{quote}{login=R Dmitry}{date=22.08.2010 10:00}{thema=}{post}  
для автоматического вычисления отделяет один клик мышкой "обновить данные"  
{/post}{/quote}  
Земной поклон, Вам Всем, ОООгромнейший и Мегареспект!!!  
До Pivote Table я несколько лет назад дошёл... И полюбил его сразу и навсегда :-)  
Вот только для чего там есть волшебный значёк <!> с простым и понятным названием:  
Refresh Data (Обновить Данные) я и раньше не осознавал...  
Нет слов, чтоб выразить свои восторг, экстаз и эмоци!!!!!!!!!!!!!!!!!  
Как всё просто и великолепно!!! И всё работает!!! Конечно мне очень легко и удобно после любых изменений в приходах/уходах просто кликнуть по волшебному восклицательному знаку и.... В О Т  О Н О  М О Ё  С Ч А С Т Ь Е !!!!!!!!  
УРА!!! УРА!! УРА!!!  
СЛАВА СОЗДАТЕЛЯМ И УЧАСТНИКАМ planetaexcel.ru!!!!!!!!!  
Такого замечательный праздник, как сегодня в моей жизни последний раз был,  
когда Ув. KIM меня научили функцией =COUNTA($A1:$C1)<2 пользоваться!!!!!!!  
:-) :-) :-) :-) :-) :-)  
 
P.S.  
До конца, конечно пока не всё осознал... Буду на сл. неделе внимательно изучать и осваивать преподавательский учебный материал в Ваших файлах :-)  
Уважаемые Гуру,  
можно в этой же теме в будущем ещё несколько вопросов Вам задать по БД ?..  
(В самом крайнем случае, если мозг мой закипит и не смогу всё осознать :( ?..)  
Спасибо огромное!!!
 
 
 
#27
23.08.2010 01:05:06
Рады были помочь!  
спрашивайте если что тут планетяне добрые
Спасибо
 
 
 
#28
23.08.2010 01:15:10
да кстати сводные там обновляются по одной, сначала одну обновить, затем вторую  
ставим курсор в любую ячейку сводной и жмем !  
затем так же вторую, в 2007 есть обновить все  
 
66633!  
крутая капча!
Спасибо
 
 
 
#29
23.08.2010 12:25:55
{quote}{login=dmdon}{date=22.08.2010 07:39}{thema=}{post}R Dmitry, KL спасибо!  
Ваши примеры открылись, прекрассно работают...  
К сожалению они не решают моей самой главной задачи:  
...  
...  
"ОСТАТКЕ" НЕ ОБНОВЛЯЮТСЯ АВТОМАТИЧЕСКИ ПРИ ИЗМЕНЕНИЯХ ДАННЫХ ПРИХОДА И УХОДА АРТИКУЛОВ!  
... и т.д.  
 
Сылочка на обещаный мной пример БД :)  
http://ifolder.ru/18986325{/post}{/quote}  
 
А можно ведь, всё-таки, написать малюсеньки макрос (5 строк кода), и все сводные таблицы будут автоматически обносляться при переходе на лист с этимим самыми сводными таблицами ☺ ...
 
 
 
#30
23.08.2010 14:37:46
{quote}{login=гость}{date=23.08.2010 12:25}{thema=Re: }{post}{quote}{login=dmdon}{date=22.08.2010 07:39}{thema=}{post}R Dmitry, KL спасибо!  
Ваши примеры открылись, прекрассно работают...  
К сожалению они не решают моей самой главной задачи:  
...  
...  
"ОСТАТКЕ" НЕ ОБНОВЛЯЮТСЯ АВТОМАТИЧЕСКИ ПРИ ИЗМЕНЕНИЯХ ДАННЫХ ПРИХОДА И УХОДА АРТИКУЛОВ!  
... и т.д.  
 
Сылочка на обещаный мной пример БД :)  
http://ifolder.ru/18986325{/post}{/quote}  
 
А можно ведь, всё-таки, написать малюсеньки макрос (5 строк кода), и все сводные таблицы будут автоматически обносляться при переходе на лист с этимим самыми сводными таблицами ☺ ...{/post}{/quote}  
 
можно и одной строчкой но автор не захотел
Спасибо
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх