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

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

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

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

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

[DmitriC]

Цитата: Сообщение от MuhaZ И чем Вам ВПР не угодила? Индекс хорошо применять когда номенклатура на одном листе, а цены на другом . Но ну их, такие прайсы...

Почему не угодила? Можно и ВПР использовать. Никто не против

Но если в таблице наименования и цены находятся не в соседних столбцах (даже, если и на одном листе), применение ВПР, на мой взгляд, смотрится не особо красиво.

[konstruktor]

очередная "мега" задачка

дано:
прайс лист, колонка "наименование", колонка "цена за ед.", колонка "кол-во", колонка "итого" - внизу столбца ячейка "всего" (сумм(колонки "итого")

цена берется с другого листа (например "цены") где имеется четыре колнки - "розница", "-5%", "-10%", "-15%"
заковырка в чем, % скидки зависит от ячейки "всего" (например менее 50 000 = "розница", 150 000 = 5% и т.п.
если делать через "если" (если "всего" <50 000; то столбик "розница"; если( и т.п. ) то ексель "циклится" (ругается на ссылку само на себя) как обойти?

нужно чтоб в ячейке "цена" - цена менялась в зависимости от общей суммы.

> файлик <

[DmitriC]

Цитата: Сообщение от konstruktor нужно чтоб в ячейке "цена" - цена менялась в зависимости от общей суммы.

А почему ты не хочешь просто скрыть столбец D, в котором считаются розничные цены, а рядом сделать аналогичный столбец, но уже при расчетах опирающийся на сумму ВСЕГО, взятую из столбца D?

[konstruktor]

вариант... просто думал можно так.. напрямую (брать уже высчитанные цены) а не высчитывать на лету).

[The_Prist]

Можно сделать все в одном листе. Достаточно включить интеративные вычисления:
2007 Excel:
Меню-Параметры Excel-Формулы-Включить интеративные вычисления.
2003 Excel:
Сервис-Параметры-вкладка Вычисления-Итерации.

Предельное число интераций - 1.

Файл прилагаю

[MuhaZ]

Цитата: Сообщение от konstruktor очередная "мега" задачка дано: прайс лист, колонка "наименование", колонка "цена за ед.", колонка "кол-во", колонка "итого" - внизу столбца ячейка "всего" (сумм(колонки "итого") цена берется с другого листа (например "цены") где имеется четыре колнки - "розница", "-5%", "-10%", "-15%" заковырка в чем, % скидки зависит от ячейки "всего" (например менее 50 000 = "розница", 150 000 = 5% и т.п. если делать через "если" (если "всего" <50 000; то столбик "розница"; если( и т.п. ) то ексель "циклится" (ругается на ссылку само на себя) как обойти? нужно чтоб в ячейке "цена" - цена менялась в зависимости от общей суммы. > файлик <

Почему не сделать
- Наименование
- Количество
- Базовая цена за ед.
- Базовая сумма итого с ИТОГОМ и размером скидки
- Цена с учетом скидки
- Сумма с учетом скидки.

Базовую цену и базовую стоимость можно скрыть.
Размер скидки вывести в отдельную ячейку и окончательную сумму считать
СуммаБазовая*(1-РазмерСкидки).

P.S. При расчете скидки не считайте
ЦенаБазовая*(1-РазмерСкидки)=ЦенаОкончательная
ЦенаОкончательная*Количество=СуммаОкончательная

а надо
СуммаБазовая*(1-РазмерСкидки)=СуммаОкончательная
СуммаОкончательная/Количество=ЦенаОкончательная

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

[konstruktor]

рассказываю по чему.

"базовая цена" и цены со скидкой считаются на п/я... а данная табличка выдается дилерам (им никчему видеть что и как считается)- должны быть "готовые" цены которые подставляются в зависимости от суммы. (по крайней мере я так думаю)

[MuhaZ]

Цитата: Сообщение от konstruktor рассказываю по чему. "базовая цена" и цены со скидкой считаются на п/я... а данная табличка выдается дилерам (им никчему видеть что и как считается)- должны быть "готовые" цены которые подставляются в зависимости от суммы. (по крайней мере я так думаю)

Ок, как я понимаю, диллерам должны уйти голые цифры без формул?

Тогда у нас 2 этапа, три пути

Этап 1
Расчет итговой суммы, расчет с учетом скидки

Этап 2
Формирование отдельного документа для диллера

Путь 1.
1.Рассчитать в табличке с БазовойСтоимостью-ОкСтоимстью
2. Скрыть базовые показатели;
3. Скопировать - спец.вставка-только значения на отдельный лист.

Путь 2.
1. Рассчитать в табличке с БазовойСтоимостью-ОкСтоимстью;
2. На отдельном листе сделать ссылки на лист с расчетами, но без ссылок на базовые показатели.

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

[Sseerrggeeii]

Как в exell посчитать сумму всех столбцов таблицы

[konstruktor]

Цитата: Сообщение от MuhaZ Ок, как я понимаю, диллерам должны уйти голые цифры без формул?

ну практически, у них будет две формулы: сумма заказа, и "выбор" цены из трех колонок в зависимости от суммы заказа.

[The_Prist]

Цитата: Сообщение от Sseerrggeeii Как в exell посчитать сумму всех столбцов таблицы

=СУММ(D:F)

Вместо D:F укажите адреса всех столбцов простым выделением мышки. Или прям в формулу забейте границы.

[domokozyavka]

Товарищи специалисты! Помогите, кто знает. Имеется телефонный справочник организации, набранный в excel. В нем 92 листа. Можно ли сделать так, чтобы в оглавлении автоматически проставлялся номер страницы, на котором находится номер? Помогите, pleeeease! Ну очень лениво вручную выставлять. Заранее спасибо.

[andrey_k]

domokozyavka, The_Prist уже давал в этой ветке пример обхода неограниченного количества листов в книге, так что пройдитесь по ветке и быстро все найдете

[domokozyavka]

Упс, ошибочка вышла. На одном листе 92 страницы. Надо в оглавлении автоматически расставить страницы, чтобы если еще добавятся номера, можно было не перебивать ручками все оглавление.

[andrey_k]

domokozyavka, тогда Вам проще будет перебросить Ваш телефонный справочник в Word, настроить там стиль для нужных номеров (или для всех, если нужно) и создать оглавление (формируется автоматически на основании анализа стилей абзацев, может автоматически обновляться). Скорее всего, перенос можно сделать элементарно через Copy-Paste.