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

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

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

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

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

[poet]

Цитата: Сообщение от andrey_k =ЕСЛИ(ЕНД(ВПР(D1+K;'РабочиеДни'!A:A;1;ЛОЖЬ));ЕСЛИ( ДЕНЬНЕД(D1+K;2)<6;D1+K;ЕСЛИ(И(ДЕНЬНЕД(D1+K;2)=6;НЕ (ЕНД(ВПР(D1+K+1;'РабочиеДни'!A:A;1;ЛОЖЬ))));D1+K+1 ;D1+K+8-ДЕНЬНЕД(A10;2)));D1+K) где D1 - предыдущая дата K- смещение в рабочих днях 'РабочиеДни'!A:A - ссылка на масив в виде списка рабочих дней

ух... ммм... а можно по прилагаемому примеру, а то я что-то непонимать в этой "трехэтажной" формуле...

to AlexeyVik: VBA совсем не знаю - проще намутить еще несколько "служебных" строк но решить формулами.

Добавлено через 1 час 8 минут
andrey_k навел меня на мысль как упростить задачу и использовать существующие рабочие наработки: что если на самом деле на анализе списка дат и флагов
01.01.09 1
02.01.09 0
03.01.09 1
04.01.09 1
05.01.09 0
и т.д.
на отдельном листе создать строкою исключительно даты рабочих дней, а в них уже использовать готовую рабочую формулу с переносом. Тем самым добиваемся результата перенесения числа на заданное количество отступа по ячейкам уже заведомо не беря во внимание логику рабочих дней!

Вот теперь и вопрос. Как написать вольно-протягивающуюся формулку с перебором и подстановкой значений? что-то мне подсказывает на использование функции ВПР, но к сожалению на этом "подсказывания" и заканчиваются

[EpS]

poet,

То, что Вам придется вести отдельный справочник выходных дней - это даже не обсуждается.
Я предлагаю следующий вариант. Считать кол-во рабочих дней от фиксированной даты и сравнивать со "смещением в рабочих днях". Если совпало, то это наша ячейка. Я и файлик примерный сваял, но не пойму как его прикрепить.

[poet]

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

[EpS]

poet,
_http://ifolder.ru/11981258

Несколько коряво т.к. за минуту сделал, но идею поймете.

[andrey_k]

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

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

Код на VBA не всегда писать целесообразно: "пуганные" пользователи, формы аналитики в процессе доработки. В итоге тратить время на переписывание кода VBA бывает не эффективно.

[poet]

Цитата: Сообщение от EpS poet, _http://ifolder.ru/11981258 Несколько коряво т.к. за минуту сделал, но идею поймете.

совсем не то, те более если вы зададите праздниками 1, 3, 7 увидите сразу же "грабли", + не то - т.к. суммы идут статическими, а ведь заранее не известно какому дню они будут пренадлежать.

Добавлено через 11 минут

Цитата: Сообщение от andrey_k Могу выложить и весь файл со структурой (лист учета и прогнозирования платежей, лист сводных движений по всем счетам по всем рабочим дням из заданного интервала, CF, баланс и т.п.), но Вам нужен был срочный ответ, поэтому выкладывать не стал (тем более, он у меня с моими данными). В оригинале у меня нет списка выходных - нет необходимости.

очень бы хотелось посмотреть и что-нибудь оттуда слямзить под свои нужды

[andrey_k]

Цитата: Сообщение от poet совсем не то, те более если вы зададите праздниками 1, 3, 7 увидите сразу же "грабли", + не то - т.к. суммы идут статическими, а ведь заранее не известно какому дню они будут пренадлежать. Добавлено через 11 минут очень бы хотелось посмотреть и что-нибудь оттуда слямзить под свои нужды

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

[EpS]

poet,

Грабли убираются внесением еще одного ЕСЛИ.
Какие суммы идут статические?
В той трактовке, в которой Вы ставили задачу в предыдущих постам, решение подходит, т.к. вы вводите число смещения и сумму и в зависимости от количества выходных под нужной датой получаете Вашу сумму.

Уточните плж условия задачи: что мы вводим, какой информацией обладаем и что хотим получить на выходе?

[poet]

Цитата: Сообщение от andrey_k в ближайшие пару дней сделаю

очень, ОЧЕНЬ будемс ждать

Добавлено через 3 минуты

Цитата: Сообщение от EpS poet, Грабли убираются внесением еще одного ЕСЛИ. Какие суммы идут статические? В той трактовке, в которой Вы ставили задачу в предыдущих постам, решение подходит, т.к. вы вводите число смещения и сумму и в зависимости от количества выходных под нужной датой получаете Вашу сумму. Уточните плж условия задачи: что мы вводим, какой информацией обладаем и что хотим получить на выходе?

я уже одурел втыканием и перечитыванием толмудов по этому Excelю с выцарапыванием хоть чего-то под себя.
Задача расписана с приведенным примером в постах № 429, 431
В примере и видно что если сумма натыкается на 1 выходной день она перескакивает на слудующую ячейку, а если и следующая идет выходным начинаются различные сельхос-приспособления.

[EpS]

poet,

Осталось еще чуть-чуть и я тожу вкурю Вашу проблему, но пока не догоняю.
Давайте рассмотрим 4 сточку с клиентом 1. У него перенос по дням - 4. Сумма 600 (тоже вносится руками) стоит на 03.01.09.
В результате мы хотим, чтобы сумма 600 появилась через 4 рабочих дня, т.е. 09.01.09?

[poet]

Цитата: Сообщение от EpS poet, Осталось еще чуть-чуть и я тожу вкурю Вашу проблему, но пока не догоняю. Давайте рассмотрим 4 сточку с клиентом 1. У него перенос по дням - 4. Сумма 600 (тоже вносится руками) стоит на 03.01.09. В результате мы хотим, чтобы сумма 600 появилась через 4 рабочих дня, т.е. 09.01.09?

все верно - следует учитывать "логику" банковских дней... *с грустью понял что мой "пример" вовсе недоделанный

[EpS]

poet,

Почему Вы не рассматриваете вариант вбивать дату руками, например, A2 -отсрочка, B2 - сумма инвойса, С2 - дата инвойса? (например сумму 600 вбивают не под датой 03.01.09, а рядом в области вноса, но при этом указанную дату тоже заносят)
Все последующие ячейки будут пересчетными, но при этом все реализуемо на уровне формул.
Конечно, нужно определенное внимание со стороны пользователя, чтобы правильно дату вбивали - это некоторый компромисс, зато без VBA и на коленке.

[andrey_k]

Цитата: Сообщение от poet очень, ОЧЕНЬ будемс ждать

poet, мой файлик можно взять здесь http://www.rapidshare.ru/1035499.
Все листы в файле имеют свое назначение.
Применение формулы, которую я давал ранее, Вы найдете на листе "Свод" - так считаются все даты. Если нужна инструкция по работе, сообщу отдельно :-)
У меня такая табличка держала больше 3,5 тыс платежей и пересчитывалась на Duo2 2ГГц за 10-20 секунд, т.е. скорость была аналогичной какой-нибудь СУБД или работе с макросами.

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

[Narus]

Помогите с таким вопросом. Есть файл со Сводной таблицй, когда её формируешь типа:
Строки: Группа товара, товар, дата, Количество
Фильтр: Выбираем 1 определенную группу Х,
и тут самое интересное вмето того, что б показать мне номенлатуру группы Х список отображает все данные. Вопрос как сделать так что б отображались только товары группы Х

[andrey_k]

Цитата: Сообщение от Narus Помогите с таким вопросом. Есть файл со Сводной таблицй, когда её формируешь типа: Строки: Группа товара, товар, дата, Количество Фильтр: Выбираем 1 определенную группу Х, и тут самое интересное вмето того, что б показать мне номенлатуру группы Х список отображает все данные. Вопрос как сделать так что б отображались только товары группы Х

Товарищ Tora подсказал верно - примените фильтр по столбцу Группа.