|
Здравствуйте, друзья.
С вами Андрей. Это вторая часть видео по созданию в Excel простой сметной программы для ремонта квартир. Ссылка на первую часть в правом верхнем углу экрана и в описании. В первой части мы разместили в таблицу список работ и ввели в неё формулы для автоматического расчёта общей стоимости при вводе объёмов.
Наша таблица практически закончена. Но, неплохо дополнить её такой возможностью, как присвоение скидки на стоимость работ при большой сумме заказа. Мы это сделаем при помощи логической функции с романтическим наименованием “ЕСЛИ”.
После строки “Всего”, добавляем ещё одну надпись: “Скидка”.
Размер скидки можно записать в формулу, но, мы же знаем, как переменчив этот мир.
Захотим изменить скидку, и придётся изменять формулу. А это, знаете, игры с острой бритвой, не нужно трогать то, что хорошо работает.
Поэтому, предусмотрим для скидки отдельную ячейку, вот эту. Для примера напишем 0,1 – Десять процентов.
А в ячейку перед ней напишем “Скидка”, что бы не забыть, откуда взялась эта цифра.
Итак, если сумма заказа более ста тысяч рублей, будем давать скидку в 10%. Размер суммы поместим в ячейку выше, а перед ней напишем “От суммы”.
Выделяем ячейку справа от слова скидка под таблицей и нажимаем на кнопку “Вставить функцию” перед строкой формул.
Открывается форма, в которой нам нужно выбрать функцию.
Выбираем категорию логические, в появившемся списке выделяем функцию "ЕСЛИ" и нажимаем “ОК”.
В следующем окне нужно заполнить три поля.
Первое: “Логическое выражение”. В него мы разместим наше условие. Оно звучит так: если значение ячейки с суммой больше значения нашей ячейки, то… Загадочное “то” мы разместим во второй строке, а пока напишем условие.
Нажимаем на кнопку в правом конце первого поля. Появилась строка, которая позволяет выбирать ячейки на листе. Выделяем мышкой ячейку с суммой и её адрес сразу появляется в строке, на клавиатуре нажимаем клавишу больше и выделяем мышкой ячейку, где написано сто тысяч.
Условие ввели, нажимаем на кнопку в правом конце строки и оно попадает в исходную форму. Можно было нажать клавишу “Enter”, результат был бы тот же.
Теперь заполняем следующее поле, а именно, что будем делать, если наше условие соблюдено.
Как пишет Excel, если это истина. В таком случае мы должны поместить в ячейку значение скидки.
Нажимаем на кнопку в правом конце поля. Выделяем ячейку со значением скидки. Нажимаем клавишу “Enter”, вторая строка заполнена.
И наконец, заполняем последнее поле, в котором указываем, что делать, если наше условие не соблюдено.
Как пишет Excel, если это ложь.
В этом случае скидка равна нулю. Так и напишем – ноль. Нажимаем “ОК”, формула готова.
Кстати, кто смотрит ролик исключительно ради логической формулы "если", не торопитесь закрывать видео.
Далее я буду рассказывать, как расширить эту функцию и сделать несколько условий проверки.
Ну а сейчас, что бы логическая функция работала, нужно ввести две простых формулы.
Первая будет вычислять значение скидки.
Под ячейкой сумма вводим знак равенства, выделяем ячейку с суммой, нажимаем клавишу умножить и выделяем ячейку с логической формулой. Нажимаем “Enter”.
Вторая формула будет вычислять итоговое значение.
В строке ниже пишем “Итого:”.
Под значением скидки нажимаем знак Равно, выделяем ячейку с суммой, на клавиатуре нажимаем знак минус и мышью выделяем ячейку со значением скидки. Нажимаем клавишу “Enter”.
Что бы всё было красиво, сделаем ещё пару штрихов.
Нажимаем на ячейке с логической формулой правой кнопкой мыши, и в контекстном меню выбираем команду “формат ячеек”.
Устанавливаем “Процентный” формат, если нужно меняем знаки после запятой и нажимаем “ОК”.
Теперь скидка представлена в процентном виде, так вроде понятней.
Давайте её протестируем.
Поставим объём последней работы, например сто. Итоговая сумма шестьдесят одна тысяча.
Скидка равна нулю.
Увеличим объём до двухсот. Сумма выросла до ста двадцати двух тысяч и появилась скидка двенадцать тысяч. Всё работает так, как мы и задумывали.
Следующим шагом мы добавим возможность отображать только те строки, у которых установлен объём работы. Как Вы уже наверное догадались, сделаем мы это при помощи функции фильтр.
Установим несколько значений объёмов работ.
Затем выделим ячейку строки с наименованиями столбцов, откроем вкладку "Данные" и нажмём огромную кнопку "Фильтр".
Программа поместила в верхнюю строку кнопки, с помощью которых мы можем выбирать разные данные из таблицы. Нажимаем на кнопку столбца количество, выбираем подменю числовые фильтры и применяем команду "больше". В открывшейся форме, справа от слова больше вводим значение 0.
Это значит, что после нажатия на кнопку "ОК", Excel отобразит нам только строки, объём которых больше нуля. Давайте её нажмём.
Точно, отобразил. Но, у нас появилась небольшая проблема, он скрыл итоговые суммы и скидку.
Возвращаем всё назад. Опять нажимаем кнопку со стрелкой и применяем команду "Удалить фильтр". Все строки на месте.
А теперь, совершенно варварским способом мы решим эту проблему.
Ниже строк с объёмами, в те строки, которые мы хотим видеть, устанавливаем значение один.
Учитывая, что это больше нуля, то Excel их покжет. Но в документе эти единицы не нужны, поэтому установим в данных ячейках шрифт белого цвета. Выделяем их. Выбираем вкладку "главная", нажимаем на стрелочку возле кнопки "цвет текста" и выбираем белый цвет. Единицы стали невидимыми.
Давайте посмотрим, как работает. Опять применяем фильтр к столбцу количество, ставим больше нуля, и нажимаем "ОК". Вот теперь всё красиво.
Можно выделить необходимый диапазон, перейти на вкладку "Файл" и применить команду "Печать". Установив в настройках "Печать выделенного фрагмента", нажать на кнопку с принтером. Документ будет распечатан в нужном нам виде.
Мы составили смету и распечатали её, с чувством исполненного долга возвращаемся на вкладку "Главная" и снимаем фильтр с выполняемых работ.
Всё это отлично, но нам нужна ещё одна смета, что делать?
Нужно выполнить два действия. Первое, это удалить все имеющиеся объёмы работ. Второе, это сохранить файл под другим именем при помощи вкладки "Файл", командой "Сохранить как".
С сохранением всё просто, а вот удалить значения из трёхсот позиций займёт некоторое время. Поэтому, что бы в дальнейшем не тратить своё время на удаление объёмов, мы запишем свои действия, а во всех дальнейших сметах будем просить Excel их воспроизвести. Данная процедура называется запись макроса.
В первую очередь выбираем вкладку "Разработчик", затем нажимаем на кнопку "Запись макроса".
В открывшейся форме нужно ввести его наименование, оно должно быть информативным и без пробелов.
Например "УдалитьОбъёмыРабот".
Если Вы составляете сметы каждый день, то для выполнения макроса можно назначить сочетание клавиш, например Ctrl + d, если работаете с таблицей реже, то смысла в этом не вижу, забудете сочетание через два дня.
Что бы макрос был доступен в любом файле, сохраним его в "личной книге макросов".
Для записи всё готово, нажимаем "ОК". С этого момента, Excel будет записывать все наши действия.
Выделяем первую ячейку столбца "Количество", опускаем страницу до последней строки.
Нажимаем клавишу “Shift” и выделяем последнюю ячейку в таблице. Все ячейки столбца выделены.
Нажимаем клавишу “Delete” и опять выделяем первую ячейку. Это всё, что нам нужно было записать, нажимаем кнопку "Остановить запись".
Теперь установим пару значений и протестируем наш макрос.
Как сейчас помню, я присвоил ему сочетание клавиш Ctrl + d, нажимаю сочетание на клавиатуре.
Макрос выполнен, объёмов нет.
Что делать, если я забыл сочетание? Опять установим пару значений.
Нажимаем на кнопку "Макросы", в открывшейся форме выделяем макрос "Удалить объёмыРабот" и нажимаем "Выполнить". Значения удалены.
В названии ролика есть словосочетание "сметная программа", а в программе, как известно, всегда найдётся кнопка, на которую нужно нажимать. Рас у нас есть процедура в виде макроса, так давайте запускать её с кнопки. На вкладке "разработчик", нажимаем команду "Вставить элементы управления". И выбираем самый первый элемент. Наш курсор превратился в маленький крестик. Предполагаю, что его нужно поставить в левый верхний угол будущей кнопки, например вот сюда. Нажимаем левую кнопку мыши, и, не отпуская ведём в место, где должен быть правый нижний угол. Отпускаем кнопку мыши.
Открывается форма, в которой нам предлагают назначить будущей кнопке выполнение макроса. Выбираем "УдалитьОбъёмыРабот" и нажимаем "ОК". Перед нами появляется кнопка. Тут же меняем её наименование на "Удалить объёмы"……, что бы не забыть через неделю, зачем она нужна.
Теперь пробуем её в действии.
Последний раз устанавливаем тестовое значение…… и нажимаем кнопку…… Всё исправно работает.
В завершении ролика, хочу сделать нашу функцию "ЕСЛИ" более гибкой.
Например, при сумме более ста тысяч рублей делать скидку 10 процентов, а при сумме более пятисот тысяч делать скидку пятнадцать процентов. Значения суммы и скидки мы записывали в столбец “J”, в следующий столбец запишем дополнительное условие, в строку "От суммы" вводим - пятьсот тысяч, в строку скидка - ноль целых, пятнадцать сотых.
Теперь выделим нашу формулу и внимательно на неё посмотрим в строке формул.
Мы видим, что после слова "ЕСЛИ" расположены скобки, внутри которых, через точку с запятой, записаны те три поля, которые мы заполняли при вводе формулы. Если забыли, то я Вам напомню. Сначала идёт условие. Если значение ячейки всего, больше чем значение ячейки с заданным числом, то, и после точки с запятой написано какое значение брать в этом случае. И ещё после точки с запятой, написано значение, которое нужно брать, если условие не выполнено.
Так вот, я предлагаю, вместо значения, которое записывается при выполнении условия, у нас это значение ячейки J304, вставить ещё одну функцию ЕСЛИ. Что бы это осуществить, мы совершенно тупо скопируем готовую функцию, которая у нас уже есть, а потом, немного её подправим. Итак, выделяем строку формул, всё кроме знака равно и копируем в буфер обмена. Затем, стираем адрес J304 и вместо него вставляем нашу формулу.
А теперь пройдём по ней и изменим нужные значения. Если значение ячейки всего больше нашего числа, а оно теперь живёт в ячейке правее, соответственно изменяем символ J на символ K. Если верно, то берём значение ячейки k304, а если неверно, то сумма меньше пятисот тысяч, но при этом больше чем сто тысяч, соответственно берём значение J304. Записываем и нажимаем "Enter".
Формула готова, давайте проверять.
Поставим объём последней работы сто. Итоговая сумма шестьдесят одна тысяча.
Скидка равна нулю.
Увеличим объём до двухсот. Сумма выросла до ста двадцати двух тысяч и появилась скидка двенадцать тысяч.
И наконец, объём равен тысяче. Сумма стала более пятисот тысяч, и скидка увеличилась до пятнадцати процентов. Всё работает.
Пока я буду делать следующий ролик по этой теме, Вы подумайте вот над чем.
Почему в столбце стоимость единицы стоят именно такие цифры?
Кто их придумал, и подходят ли они Вам?
А в следующем видео мы научимся брать эти цифры не из интернета, а вычислять просто и самостоятельно.
Надеюсь, что информация была полезной.
Описание программы DefSmeta вы можете получить на сайте defsmeta.com
Ссылка в описании.
Желаю всем удачи.
До новых встреч на уроках по программе.
| |
|
Главная
