mymamalama

В этом примере мы разберем несколько инструментов Excel, а именно:

- вложенные формулы;

- выпадающие списки;

- логическая формула ЕСЛИ;

- формула блока «ссылки и массивы» ВПР.

Шаг 0 – введение.

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

Шаг 1 – работа с прайсом.

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

Для этого в ячейке C2 ставим наценку, допустим 1,47 (увеличение цены по сравнению с ценой закупки – 47%). Ее обязательно вывести в отдельную ячейку, потому что «а вдруг кризис» и придется все цены пересчитывать, не будем же мы всю таблицу заново просчитывать. Да и на будущее - в случае, если все строчки будут производить некоторые действия (умножение, сложение, вычитание, деление) с одной единственной ячейкой, то легче ее вывести отдельно и зафиксировать. Опять же, если мы захотим изменить наценку на все товары, то нам достаточно поменять значение только в одной этой ячейке, и вся наша таблица автоматически пересчитается.

А чтобы каждый раз в формуле вручную не ссылаться на одну и ту же ячейку (C2), то ее необходимо зафиксировать. Для этого необходимо поместить курсор в строку формул после знака умножить на C2 и нажать F4. Если в формуле появилось два (!) знака $, то ячейка зафиксирована.

Первоначальная формула в ячейке D5 будет выглядеть следующим образом =C5*$C$2

С первого взгляда все хорошо. Но это не так. В полученном результате в ячейке D5 больше двух знаков после запятой. Избавляемся от концов с помощью формулы ОКРУГЛ. Это наше первое знакомство с вложенной формулой. Формула в формуле. Самый простой способ «вложить» одно в другое – скопировать полученную формулу и следовать по инструкции ниже.

В ячейке D5 в строке формул пишем =ОКРУГЛ и открываем Аргументы функции (элемент Fx).

В поле «Число» вставляем скопированную формулу без знака «=».

В поле «Число_разрядов» ставим цифру 2, так как нам надо 2 знака после запятой.

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

Готово. Вы великолепны!

Шаг 2 – заполнение карточки заказа.

Начнем с выпадающего списка.

Выделяем диапазон, где хотим видеть выпадающий список (диапазон C8:C14). Переходим на вкладку Данные, группа Работа с данными, элемент Проверка данных.

Тип данных – список, источник – список товаров с листа «прайс» (диапазон B5:B16).

Таким образом, мы получаем выпадающий список по товарам. Набросаем в корзину заказа некоторые позиции из прайса, поставим произвольное количество и сделаем нумерацию для карточки заказа.

Есть два способа сделать нумерацию, но изначально в первую ячейку списка ставим цифру 1.

- щелкаем 2 раза за маркер автозаполнения и в появившемся окошке выбираем значение «Заполнить» - подходит при нумерации большого списка.

- тянем за маркер автозаполнения, параллельно зажав клавишу CTRL – может работать даже тогда, когда ячейка начинается не с 1, а с числа 824789, удобно продолжать нумерацию в середине списка.

Ура. Осталось два крупных шага (ВПР и логическая формула ЕСЛИ).

Для разгона давайте на ячейке F8 пропишем простую формулу умножения =D8*E8

Переходим к ВПР. Если по-простому, то формула «вертикальный поиск результата/вертикальный просмотр» берет ячейку со значением (товар в карточке заказа) и ищет ее в предложенном списке (в нашем случае в прайсе). После того, как формула нашла это значение в списке, она пробегает по этой строчке в прайсе и забирает оттуда нужное нам значение.

Если же говорить на языке формул, то все выглядит следующим образом:

Искомое значение - что ищем. Мы ищем блокнот (ячейка C8)

Таблица - где ищем. Ищем в прайсе. Указываем диапазон всей таблицы прайса (внимание: именно с ячейки B4 до ячейки D16).

Номер_столбца - из какого столбца указанной выше таблицы надо брать значения. В нашем случае мы хотим "притащить" финальную цену. См.картинку ниже и ставим цифру 3.

Интервальный_просмотр - 0. Ставим 0 для получения точного результата (чтобы формула нашла конкретно "Блокнот А5", а не "блакнот а 5".

Далее осталось протянуть получившуюся формулу вниз, до строки итогов.

Пара простых шагов для финального штриха. Ставим автосумму в ячейке D15 и F15. Для упрощения действия можно запомнить следующее сочетание клавиш «ALT» и «=» (горячая клавиша для автосуммы).

В ячейке D16 считаем скидку с помощью логической формулы ЕСЛИ. Примем за правило, что если заказ собран на сумму более 5 000 руб., то скидка будет 10%.

Финальным аккордом в нашей и так уже затянувшейся песни будет простая формула

=F15-(F15*F16)

Все! С официальной частью закончили.

Небольшие советы:

1. Если Вы очистите все заполняемые ячейки в карточке заказа, то у Вас в колонках с ценой и суммой появятся значения #Н/Д (нет данных), так как непонятно какое значение искать (мы же удалили все значения с товарами в колонке C). Для того чтобы эта ошибка нас не смущала, воспользуемся специально написанной для этого командой ЕСЛИОШИБКА. Достаточно просто скопировать полученную формулу, вставить формулу ЕСЛИОШИБКА и туда вложить формулу ВПР.

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

Обращаю Ваше внимание, что моей задачей в этом посте было просто показать, как работают разные формулы Excel. Скорее всего, в мире уже есть множество написанных программ для решения таких задач (та же 1С), возможно существуют разного рода макросы на выполнение всего, что написано в посте. Я же просто делюсь своими знаниями и надеюсь, что они Вам были полезны.

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

Когда рассказываешь про функции Excel, то возникает чувство, что все это никому не нужно и все и так все знают. Но если все же в этом посте было что-то полезное – дайте знать.

Задача: рассчитать необходимое кол-во кабелей.

Решение:

1. У меня программа 2007г, но этот инструмент есть в любом порядочном Excel, просто он прячется и нам надо его достать.

Заходим в Файл – Параметры – Надстройки – Управление Надстройки Excel - Перейти

В появившемся окне щелкаем на поле «Поиск решения» и нажимаем ОК. Готово!

Выбранный инструмент появился на вкладке Данные, группа Анализ.

2. Нажимаем на инструмент «Поиск решения» и в появившемся окне заполняем необходимые параметры для подбора значений.

Расшифровка:

Целевая ячейка $D$8 – ячейка, которая суммирует итоги.

Значение 160 – наше целевое значение, под которое надо подогнать длины кабелей.

Изменяя ячейки $C$3:$C$7 - тут диапазон ячеек, значения которых необходимо подбирать, чтобы выйти на заданное целевое значение (кол-во кабелей разной длины).

Ограничение $C$3:$C$7 = целое – это означает, что подобранное кол-во кабелей должно быть целым числом, а не дробным.

Ограничение $C$3:$C$7 >=0 – это означает, что подобранное кол-во кабелей должно быть больше нуля. Иногда инструмент «поиск решения» может находить отрицательные величины и считать, что он со своей задачей справился на 120%.

3. Нажимаем кнопку «Выполнить». Программа начинает подбирать варианты и иногда этот поиск может затянуться на некоторое время (в зависимости от написанных ограничений, диапазона данных, сложности задачи).

Спустя 0,5 мин. я получила такой результат.

Отмечу, что сам поиск решения не всегда срабатывает идеально и иногда он очень долго ищет то самое решение. В этом примере на одном компьютере все сработало идеально, а на другом он посчитал кол-во 2,000002. Погрешности в нем, все же, существуют.

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

Если кому-нибудь понадобится эта инструкция в формате pdf или мой исходный Excel-файл с примером, то пишите, вышлю на почту.

Так же если у Вас возникнут трудности с применением данного инструмента – готова пообщаться и подсказать :)

P.S.: Я пользователь новый, поэтому только начала въезжать в то, как тут писать посты. Внизу в графе "возможные дубликаты" появился совсем не дубликат, а скриншот переписки. К моему посту это отношения никакого не имеет. Писала сама, пруфы есть))