MS Excel - решение практических задач

В этой теме размещаем вопросы и ответы, а также советы и рекомендации по решению конкретных задач, которые требуют создания формулы, применения макроса, написания кода VBA, т.е. Практикум.

------------------------------

Вопросы и ответы, а также советы и рекомендации по настройке MS Excel, ошибки в работе программы, решение задач по импорту/экспорту документов, ссылки и обсуждение надстроек для Excel, обучающие материалы и т.п. размещаем в теме MS Excel - настройка, импорт/экспорт.
Если решение задачи не требует применения формул и макросов (VBA), то это обсуждение будет перенесено в тему по настройкам.

Все пожелания по принципам размещения сообщений по этим темам рассматриваются - пишите в личку Andrey_k.

[andrey_k]

Цитата: Сообщение от Woorms Нет мне надо сделать не на 3 компьютера, а на 1. Просто я знаю место где такое сделано для 3 компьютеров (правда пока нет возможности посмотреть как это все сделано)... Я так понимаю автоматическое списание не возможно сделать?

Уточните, как работает тот магазин, который Вы увидели.

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

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

Если знаете, что количество строк товара никогда не превысит какое-то число, например, не больше 10, то Вам останется занести формулы для 10 строк накладной, а затем вставить формулы расчета итогов по столбцам СУММ() для расчета общей стоимости товара по накладной.
Удачи

Магазин где видел это, занимается продажей запчастей (там узнавали у продавца, он сказал что у них база именно на эксел и не 1с и другие программы не используются.

Спасибо большое, сегодня буду пытаться сделать так, Базу уже сделал на 3.500 товаров, этого должно хватить

[andrey_k]

Цитата: Сообщение от Woorms Магазин где видел это, занимается продажей запчастей (там узнавали у продавца, он сказал что у них база именно на эксел и не 1с и другие программы не используются. Спасибо большое, сегодня буду пытаться сделать так, Базу уже сделал на 3.500 товаров, этого должно хватить

Если формулы сделаете аккуратно (не будет вторичных поисков по всей базе, например, поисковые запросы будут не по всему столбцу/строке, а только по данным), то для машины класса Core 2Duo для такой базы все должно работать в реалтайме. Я не стал давать Вам пример готовой базы, т.к. Вы сказали, что какой-то опыт в Экселе у Вас есть, а там все решается простыми методами. По оптимизации поисковых запросов и интерфейса можем позже поговорить (когда ядро системы у Вас будет).

По работе с одной таблицей в Excel на нескольких компьютерах - у нас так справочник по складу работает - завели простую шахматку, ведет ее один сотрудник, остальные в режиме чтения открывают файл и смотрят. Основная база по продажам в 1С (Розница + Торговля), а для быстрого поиска по остаткам в разрезе нужной номенклатуры этого файла вполне хватает (общая номенклатура больше 20 тыс.наименований, при этом в нашем файле все можно быстро отбирать по размеру, типу и т.п.). Мы даже не стали на рабочих местах MS Office ставить - LibreOffice бесплатный стоит.

[Nazhdak]

Подскажите, пожалуйста, мне, чайнику, по Excel 2010. Вроде простейшая задача, а сделать не могу.
Есть два столбца: в одном - наименование продукта, а в другом - его цена. Как создать такие же два столбца, чтобы в одном был выпадающий список, а во втором, при выборе товара из первого, автоматом появлялась цена?

[regist]

Цитата: Сообщение от Nazhdak Подскажите, пожалуйста, мне, чайнику, по Excel 2010. Вроде простейшая задача, а сделать не могу. Есть два столбца: в одном - наименование продукта, а в другом - его цена. Как создать такие же два столбца, чтобы в одном был выпадающий список, а во втором, при выборе товара из первого, автоматом появлялась цена?

http://www.youtube.com/watch?v=IVVNX...layer_embedded

[Nazhdak]

Цитата: Сообщение от regist http://www.youtube.com/watch?v=IVVNX...layer_embedded

За видео спасибо. Выпадающий список то сделать получается, но вот как связать его именно с соседним столбцом, в котором будут указаны цифры. В видео этого нету, а именно в этом у меня возникли затруднения.

[andrey_k]

Цитата: Сообщение от Nazhdak За видео спасибо. Выпадающий список то сделать получается, но вот как связать его именно с соседним столбцом, в котором будут указаны цифры. В видео этого нету, а именно в этом у меня возникли затруднения.

По ссылке, указанной regist, есть ссылки на другие примеры создания списков. Также указан первоисточник с текстовыми примерами.
Посмотрите, думаю, найдёте ответ на свой вопрос.

Дополнение для Woorms.
Рекомендую Вам также ознакомиться с материалами по ссылке. Там есть и пример для торговой базы, и списки по динамическому диапазону.

[Nazhdak]

Цитата: Сообщение от andrey_k По ссылке, указанной regist, есть ссылки на другие примеры создания списков. Также указан первоисточник с текстовыми примерами. Посмотрите, думаю, найдёте ответ на свой вопрос.

Ага, увидел ссылку, почитал. Разобрался. Помог вариант с элементом ActiveX. Огромное спасибо.

andrey_k Спасибо огромное за помощь, с накладными разобрался (сделал 1 лист со списком накладных, там же и забивается товар, 2 лист печатной накладной, где выбирается номер накладной, и вся информация накладной с этим номером переносится автоматически, там еще добавил сумму прописью с НДС
Со складом пока не могу ничего сделать, в голову ничего не приходит как сделать списание.

Сейчас у меня другая проблема
В "Списке накладных" хочу сразу сделать базу на 10.000 накладных, чтобы хватило на долго, но при копировании формулы, автоматически меняются 3 данных, а мне надо чтобы менялись данные только под №1 (на картинке), а под №2 оставались прежние. Данные №1 совпадают с №строки листа


Если не сложно можно написать как это сделать, а лучше формулу или образец. А то моих умений на это не хватает

[regist]

Цитата: Сообщение от Woorms но при копировании формулы, автоматически меняются 3 данных, а мне надо чтобы менялись данные только под №1 (на картинке), а под №2 оставались прежние. Данные №1 совпадают с №строки листа

не совсем понял, что 3 (три) данных меняется, но для того чтобы при копирование формулы не менялся адрес ячейки надо прописать абсолютный адрес, тоесть написать спереди знак $

К примеру, чтобы в вашем случае при копирование ссылалось на тот же диапазон его надо прописать так $A$5:$C$300

ЗЫ. более подробно почитать про это можно набрав в гугле Абсолютные и относительные адреса ячеек в excel.

[andrey_k]

Цитата: Сообщение от Woorms ...Со складом пока не могу ничего сделать, в голову ничего не приходит как сделать списание.

можно в листе накладных (где опись расхода поставить доп.поле-индикатор Приход/Расход. По операциям, которые идут в накладную, указывайте там "расход", а для отражения пополнения склада указывайте "приход". На листе склада (он же прайс) в поле количество ставьт формулу СУММЕСЛИ() по этому товару на листе накладных по типу СУММЕСЛИ(..;"приход";..)-СУММЕСЛИ(..;"расход";..). Или иначе - приход ставбье со знаком "плюс", а расход со знаком "минус", тогда можно обойтись одной СУММЕСЛИ() сразу по полю количество.

Цитата: Сообщение от Woorms Сейчас у меня другая проблема В "Списке накладных" хочу сразу сделать базу на 10.000 накладных, чтобы хватило на долго, но при копировании формулы, автоматически меняются 3 данных, а мне надо чтобы менялись данные только под №1 (на картинке), а под №2 оставались прежние...

Попробуйте так называемые формулы-заморозки - символы $ перед номером строки (если надо зафиксировать строку) или столбца (если столбец фиксируем), или у обоих сразу. Я это описывал в этой теме.
пример A1 - > $A1 при копировании номер столбца не меняется. Также это можно менять автоматом при редактировании формулы: встаете курсором на адрес ячейки в формуле и жмите "F4".

Цитата: Сообщение от andrey_k Попробуйте так называемые формулы-заморозки - символы $ перед номером строки (если надо зафиксировать строку) или столбца (если столбец фиксируем), или у обоих сразу. Я это описывал в этой теме. пример A1 - > $A1 при копировании номер столбца не меняется. Также это можно менять автоматом при редактировании формулы: встаете курсором на адрес ячейки в формуле и жмите "F4".

Еще раз ОГРОМНОЕ спасибо...помогло F4 База из 10.000 накладных почти готова.

А про списание я чего то не понимаю, или уже голова не варит сейчас, а несложно будет сделать образец?

[Nazhdak]

Рано я обрадовался. С большим количеством элементов ActiveX часто крашится Excel, а после восстановления документа содержание колонок не печатается на принтере или просто не выбирается из списка. Помогает только закрытие документа и открытие его ранее сохранённой копии заново, при этом, естественно, пропадает часть несохранённых данных.
Пробовал создавать зависимые списки с помощью ДВССЫЛ, но там может быть только текст без пробелов. Заменять подчёркиваниям не вариант. Поделитесь мнениями, что ещё можно придумать для решения такой задачи:

Цитата: Есть два столбца: в одном - наименование продукта, а в другом - его цена. Как создать такие же два столбца, чтобы в одном был выпадающий список, а во втором, при выборе товара из первого, автоматом появлялась цена?

[andrey_k]

Цитата: Сообщение от Nazhdak ..Пробовал создавать зависимые списки с помощью ДВССЫЛ, но там может быть только текст без пробелов.

чтобы в ДВССЫЛ вставлять любой текст, берите его в кавычки.

Добавлено через 1 час 29 минут

Цитата: Сообщение от Nazhdak Рано я обрадовался. С большим количеством элементов ActiveX часто крашится Excel, а после восстановления документа содержание колонок не печатается на принтере или просто не выбирается из списка. Помогает только закрытие документа и открытие его ранее сохранённой копии заново, при этом, естественно, пропадает часть несохранённых данных.

Проверьте свой код ActiveX на ошибки. Возможно также, что какие-то ошибки есть в системных библиотеках. Запустите Средство восстановления офиса (через меню Пуск или файл-Параметры-Центр безопасности).

Nazhdak
Я если правильно понял, то Вам надо чтобы был вот такой вид: (Инет тупит, фотографию не могу загрузить)
1 столбец: Список продуктов
2 столбец: Цена
3 столбец: Выпадающий список
4 столбец: Автоматически показывается цена товара, какой выбран в выпадающем списке

Извеняюсь что туплю, просто уже мозги кипят со своей базой