Как изменить прайс в excel

Содержание

В прайсах Excel очень часто приходится изменять цены. Особенно в условиях нестабильной экономики.

Рассмотрим 2 простых и быстрых способа одновременного изменения всех цен с увеличением наценки в процентах. Показатели НДС будут перечитываться автоматически. А так же мы узнаем, как посчитать наценку в процентах.

Как сделать наценку на товар в Excel?

В исходной таблице, которая условно представляет собой расходную накладную нужно сделать наценку для всех цен и НДС на 7%. Как вычисляется Налог на Добавленную стоимость видно на рисунке:

«Цена с НДС» рассчитывается суммированием значений «цены без НДС» + «НДС». Это значит, что нам достаточно увеличить на 7% только первую колонку.

Способ 1 изменение цен в Excel

  1. В колонке E мы вычислим новые цены без НДС+7%. Вводим формулу: =B2*1,07. Копируем это формулу во все соответствующие ячейки таблицы колонки E. Так же скопируем заголовок колонки из B1 в E1. Как посчитать процент повышения цены в Excel, чтобы проверить результат? Очень просто! В ячейке F2 задайте процентный формат и введите формулу наценки на товар: =(E2-B2)/B2. Наценка составляет 7%.
  2. Копируем столбец E и выделяем столбец B. Выбираем инструмент: «Главная»-«Вставить»-«Специальная вставка» (или нажимаем CTRL+SHIFT+V). В появившимся окне отмечаем опцию «значения» и нажимаем Ок. Таким образом, сохранился финансовый формат ячеек, а значения обновились.
  3. Удаляем уже ненужный столбец E. Обратите внимание, что благодаря формулам значения в столбцах C и D изменились автоматически.

Вот и все у нас прайс с новыми ценами, которые увеличенные на 7%. Столбец E и F можно удалить.

Способ 2 позволяет сразу изменить цены в столбце Excel

  1. В любую отдельную от таблицы ячейку (например, E3) введите значение 1,07 и скопируйте его.
  2. Выделите диапазон B2:B5. Снова выберите инструмент «Специальная вставка» (или нажимаем CTRL+SHIFT+V). В появившемся окне, в разделе «Вставить» выберите опцию «значения». В разделе «Операции» выберите опцию «умножить» и нажмите ОК. Все числа в колонке «цена без НДС» увеличились на 7%.

Внимание! Заметьте, в ячейке D2 отображается ошибочное значение: вместо 1,05 там 1,04. Это ошибки округлений.

Чтобы все расчеты били правильными, необходимо округлять числа, перед тем как их суммировать.

В ячейке C2 добавим к формуле функцию: =ОКРУГЛ(B2*0,22;2). Проверяем D2 = B2 + C2 (1,05 = 0,86 + 0,19). Теперь скопируем содержимое C2 в целый диапазон C2:C5.

Примечание. Так как у нас только 2 слагаемых нам не нужно округлять значения в колонке B. Ошибок уже 100% не будет.

Ошибки округлений в Excel вовсе не удивляют опытных пользователей. Уже не раз рассматривались примеры по данному вопросу и еще не рас будут рассмотрены на следующих уроках.

Читайте также:  Как использовать morphvox pro

Неправильно округленные числа приводят к существенным ошибкам даже при самых простых вычислениях. Особенно если речь идет о больших объемах данных. Всегда нужно помнить об ошибках при округлении, особенно когда нужно подготовить точный отчет.

Предположим, что мы имеем ситуацию:

Мало того, что нам нужно на основе имеющегося прайс-листа сделать новый повышенный прайс, но еще сделать это так, чтобы клиент не догадался, что мы производили какие-то действия над базовым прайс-листом. Мы ведь дорожим своими клиентами!

Итак, наша компания работает с неким прайс-листом:

Наименование товара Цена
Товар 1 356
Товар 2 257
Товар 3 578

Мы планируем предоставить нашему клиенту скидку 15% и при этом выйти на указанные цены.

х = 356 * 100 / (100-15)

Открываем имеющийся прайс-лист в Excel или составляем новую таблицу в формате прайс-листа.

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

и очистить их содержимое с помощью Delete. Таким образом, нам не придется форматировать новые области.

А теперь приступим к заведению формул.

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

Наименование товара Базовая цена Скидка для покупателя Цена с наценкой
Товар 1 356 15 ?
Товар 2 257 15 ?
Товар 3 578 15 ?

Итак, мы ищем цену с наценкой. Начнем с Товара 1.

Произведем вычисления в Excel

Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:

= БАЗОВАЯ ЦЕНА * 100 / (100 — СКИДКА ДЛЯ ПОКУПАТЕЛЯ) ENTER

Если Вы все сделали правильно, то должно получиться 418,82.

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

Ну, а если всё правильно, можно смело копировать готовую формулу и вставлять ее в следующие ячейки. Причем, если Вам нужно сохранить формат, можно произвести операцию через cпециальную вставку, выбрав для вставки «формулу».

Если же Вас мало волнует формат, можно просто «протянуть» формулу. Для этого нужно поставить курсор на левый нижний край ячейки с формулой. Появится крестик. Нажмите на этот крестик левой клавишей мышки и тяните не отпуская пальца вниз до конца списка товаров. Не забудьте отпустить палец!

Итак, мы закончили расчет цен с наценкой к прайс-листу и получили следующие результаты:

Наименование товара Базовая цена Скидка для покупателя Цена с наценкой
Товар 1 356 15 418,82
Товар 2 257 15 302,35
Товар 3 578 15 680,00

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

А теперь, для того, чтобы быть уверенными в правильности расчетов, проведем проверку.

Сделаем заданную скидку от полученной цены.

Могу предложить 2 способа на выбор.

В обоих случаях скидку примем за «x».

1 способ: с использованием процентов.

Формула будет такой (как обычно БЕЗ ПРОБЕЛОВ в следующем порядке):

В этом случае ВАЖНО умножить процент на число, из которого он вычитается. Таким образом мы находим ту процентную часть числа, которую от него нужно отнять. В противном случае получится абракадабра.

Проверим нашу цену с наценкой:

418,82 — 15% * 418,82 = 356

2 способ: без использования процентов.

Формула будет такой (как обычно БЕЗ ПРОБЕЛОВ в следующем порядке):

= ЦЕНА * (1 — x / 100)

Читайте также:  Как обнаружить глушилку сотовой связи

Проверим нашу цену вторым способом:

418,82 * (1 — 15 / 100) = 418,82 * 0,85 = 356

Проверка показала, что мы направильном пути!

Осталась филигранная работа. Будем заметать следы.

Наша задача — избавиться от столбцов «Базовая цена» и «Скидка». Но как это сделать, если на них ссылается наша новая цена с наценкой?

Копируем весь столбец с новой ценой и вставляем в то же место «Значение» через «Специальную вставку»:

Вроде бы все получилось! Формула исчезла, осталось только значение. Теперь можно удалить ненужные столбцы. Но…

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

Остался всего один шаг до совершенства!

Давайте учиться округлять значение.

Ставим курсор рядом с ячейкой, которую собираемся округлить и заходим в «Функции». Находим в математических функциях округление и задаем число цифр после запятой:

Осталось скопировать округление и вставить «значения» через «специальную вставку».

Вот теперь все в порядке! Как будто так и было! Можно удалять все лишние столбцы и отсылать новый прайс-лист клиенту.

&nbsp&nbsp&nbsp&nbsp&nbspБывают домовые, а я — ОФИСНЫЙ!

&nbsp&nbsp&nbsp&nbsp&nbspОблегчаю трудовые будни!

Как офисному работнику выжить без кондиционера

ПУТЕШЕСТВУЕМ ВМЕСТЕ

Как вывезти ребенка на отдых за границу

Как не потерять качество бизнеса в погоне за количеством

Надоели убытки? Выявляем нерентабельных клиентов

EXCEL ДЛЯ "ЧАЙНИКОВ" И НЕ ТОЛЬКО

ВСЕ УРОКИ

Как внести изменения в готовый прайс-лист

Закрепление областей в Excel

Начнем с того, что закрепим области, чтобы нам было удобно работать.

Делается это очень просто: активизируем ячейку, которая находится слева и внизу от закрепляемой области заходим в "Окно" главного меню в верху страницы и выбираем "Закрепить области":

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

Если нам потребуется снять закрепление после проведения работ над документом, зайдем опять же в "Окно" и выберем "Снять закрепление областей".

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

С какими проблемами мы можем столкнуться в процессе? Например:

1. Старый прайс-лист сделан в Word.

2. В связи с изменением наименований нужно произвести много одинаковых изменений.

3. Заводим формулы, а они не считаются.

Начнем по порядку.

Импорт в Excel

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

и вставляем выделенную область в Excel.

Следующим шагом форматируем таблицу, расширяя нужные столбцы для более удобного чтения.

C копированием таблицы мы справились, теперь приступим к внесению изменений.

Поиск и замена значений в Excel

Предположим, что наш производитель кардинально изменил наименование товара. Если раньше все игрушки назывались "куклы", то теперь пришли в новым наименованием "пупсы". Неужели мы будем заходить в каждую ячейку и менять все значения? Конечно же нет!

Мы воспользуемся функцией замены , которую предлагает нам Excel.

Для этого мы выделим область, где будет происходить замена и вызовем эту функцию.

Вызвать функцию поиска и замены в Excel можно тремя способами:

1. Одновременным нажатием SHIFT и F5;

2. "Правка" в главном меню -> "Найти"

3. Бинокликом в меню правки вверху страницы.

Если Вы не видите у себя биноклика, его можно добавить в панель и он всегда будет там виден. Т.е. Вы можете самостоятельно выбрать нужные Вам функции (см. как это сделать).

Итак, выделяем область замены, вызываем функцию правки, заходим в закладку "Заменить", пишем в строке "Найти" старое значение, в строке "Заменить на" — новое и нажимаем на кнопочку "заменить все" :

Читайте также:  Как выглядят светодиодные лампы фото

Вот мы и избавили себя от долгой и нудной работы!

Поиск и устранение причин, по которым Excel отказывается считать

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

1. Внутри ячейки помимо цифр есть текст;

2. Задан неверный формат ячейки;

3. Документ скопирован из Word и имеет принудительный пробел между разрядами;

4. Вместо (,) копейки отделены от рублей (.) (или наоборот в зависимости от настроек).

Идем по пунктам.

1. Необходимо удалить из ячейки текcт и все посторонние знаки кроме цифр. Если Вам нужно указать валюту расчета, это можно сделать, применив "денежный" или "финансовый" формат.

2. Для того, чтобы задать правильный формат ячеек, нужно вызвать "Формат ячеек" и выбрать в закладке "Число" "числовой", "денежный" или "финансовый" формат.

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

Если и это не помогло, разбираемся дальше.

3. Если мы знаем, что документ скопирован из Word и цифры разделены принудительными пробелами, нам нужно избавиться от этих пробелов. Чтобы не заходить в каждую ячейку и не удалять их вручную воспользуемся правкой.

Выделяем нужную область, вызываем "Правку" и в поле "Найти" пишем пробел, а поле "Заменить на" очищаем от всяких значений. После нажатия кнопочки "заменить все" все пробелы должны исчезнуть.

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

ВАЖНО ДЛЯ 2-4 ПУНКТОВ: активизируйте для замены только ту область, где хотите произвести изменения, иначе Вы рискуете изменить весь текст на странице.

После проведения этих мероприятий, формулы должны считаться!

Этим мы и займемся. Сделаем наценку 10% на старую цену нашего прайс-листа.

Формула будет такой:

Старая цена + 10% = Новая цена

Могу предложить два варианты заведения формулы добавления процента к числу в Excel.

Активизируем соответствующую ячейку и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем соответствующие ячейки в следующем порядке:

= СТАРАЯ ЦЕНА + 10% * СТАРАЯ ЦЕНА ENTER

= СТАРАЯ ЦЕНА * (1 + наценка/100) ENTER

= СТАРАЯ ЦЕНА * 1,1 ENTER

Теперь нам остается только перенести новые цены в колонку со старыми. Однако, если мы это сделаем простым копированием-вставкой, у нас ничего не получится.. вернее получится абракадабра. Почему? Да потому, что мы скопируем не значения, а формулы. Эти формулы будут ссылаться на текст.

Обратите внимание, что цена за упаковку пересчиталась автоматически.

Нам остается только удалить столбец "Новая цена" и наш новый прайс-лист готов!

Если после прочтения статьи у Вас остались вопросы или вы хотели бы видеть в данном разделе определенные темы напишите мне письмо с пометкой "эксель" по адресу: ngt@inbox.ru

Испытательный срок — испытание не только для работника

Как не упустить удачу

Дольмены — одна из величайших тайн третьего тысячелетия

Проверьте, соблюдается ли Трудовой Кодекс в вашей компании

Мир нейтрален. От нас зависит, какими цветами мы его окрасим

Быть или не быть. собой? Вот в чем вопрос!

Ежегодный оплачиваемый отпуск. На что вы имеете право?

Использование силы мысли — залог здоровья

Где живут нассамихи

Как стать хорошим руководителем

Воспоминания о будущем

Как провести презентацию и заслужить доверие аудитории

&nbsp&nbsp2011-2017 &nbsp&nbspПерепечатка материалов возможна лишь при указании прямой индексируемой гиперссылки

Adblock
detector