Девять малоизвестных фишек для бизнеса в Excel

Graphicloads-Filetype-Excel-xlsработа в excelExcel — это, пожалуй, одна не из самых дружелюбных программ на свете. Обычный пользователь зачастую использует всего 5% всех ее возможностей, при этом плохо представляя, какие сокровища спрятаны в ее недрах. Если вы прислушаетесь к советам Excel-гуру,то работа в excel станет для Вас приятной, Вы сможете научиться сравнивать любые прайс-листы, составлять различные аналитические отчеты, а также прятать любую секретную информацию от посторонних всего в несколько кликов (ну, хорошо, иногда этих кликов может быть и 15).

  1. Супертайный лист.

Представим, что вы хотели бы скрыть несколько листов Excel от пользователей, которые работают над книгой. Если прибегнуть к классическому способу: кликнуть правой кнопкой мышки по ярлыку листа, а затем нажать «Скрыть», другой пользователь все равно будет видеть имя вашего скрытого листа.

Для того чтобы лист стал невидимым другим людям, следует действовать так:

— Нажмите кнопки ALT+F11.
— Слева сразу же появится вытянутое окошко.

— Выберите в верхней части окошка номер листа, который бы вам хотелось скрыть.

— Далее необходимо найти свойство Visible, которые будет находиться в нижней части в конце списка, и сделать его xlSheetVeryHidden.

После проведения вышеуказанных действий ваш лист никто не увидит.

  1. Запрет на проведение изменений задним числом.

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

— Наведи курсор на ячейку с указанием даты, а затем выберите пункт «Данные» в меню.

— После того, как нажмете на кнопочку «Проверка данных», перед вами появится таблица.

— Выбираем «Другой» в списке «Тип данных».

— Затем пишем =А2=СЕГОДНЯ() в графе с названием «Формула».

—  С «Игнорировать пустые ячейки» следует убрать галочку.

— После этого нужно нажать кнопку «Ок».

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

— Можно также запретить пользователям менять цифры в столбике «Кол-во». Для этого вам нужно поставить курсор на ячейку, где указано количество и повторить весь алгоритм действий.

  1. Запрет на ввод дублей.

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

— Следует выделить ячейки А1:А10, на которые будет действовать запрет.

— Нажимаем кнопку «Проверка данных», которую вы найдете во вкладке «Данные».

— Затем в «Параметрах» выбираем вариант «Другой» из выпадающего списка «Тип данных».

— Выбиваем в графе «Формула» =СЧЁТЕСЛИ($A$1:$A$10;A1)<=1.

— Переходит в этом же самом окне на вкладку «Сообщение об ошибке» и вводим там свой текст, который после будет каждый раз появляться, если кто-то захочет ввести дубликаты.

— Нажимаем кнопку «Сохранить».

  1. Выборочное суммирование.
    представим себе таблицу, из которой понятно, что совершенно разные заказчики покупали несколько раз разные товары у вас и платили определенные суммы. Вам нужно узнать, на какую именно сумму заказчик Вася купил у вас, к примеру, крабовых палочек «Krabi».

Для этого делаем так:

— Вводим имя Вася в ячейку G4.

— В ячейке G5 нужно прописать название приобретенного продукта. В нашем случае это «Krabi».

— Встаем на ячейку G7, где будет просчитываться сумма, и пишем для нее следующую формулу: {=СУММ((С3:С21=G4)*( B3:B21=G5)*D3:D21)}. Не стоит пугаться объемов этой формулы, смысл ее будет понятен, если писать символы постепенно.

— Сразу вводим {=СУММ и открываем круглые скобки, в которых пропишем три множителя.

— Самый первый множитель — это (С3:С21=G4) будет искать клиентов с именем Вася в указанном списке.

— Второй по счету множитель (B3:B21=G5) будет делать все тоже самое с названием продукта.

— Третий множитель, состоящий из D3:D21, будет отвечать за столбец с указанием стоимости. После этого множителя следует закрыть скобки.

— Вводим Ctrl + Shift + Enter вместо Enter во время написания формул в Excel.

  1. Сводная таблица.
    В таблице, в которой указано, какой именно товар, какому из заказчиков и на какую сумму, продал менеджер, после ее разрастания очень сложно выбрать отдельные данные. К примеру, вам нужно знать, на какую сумму было продано свеклы или кто именно из менеджеров выполнил в вашей компании больше заказов. В Excel для решения подобных задач есть специальные сводные таблицы.

Чтобы вы смогли создать подобную таблицу необходимо:

— Найдите вкладку «Вставка» и нажмите кнопочку «Сводная таблица».

— Появится окошко, в котором нужно нажать «Ок».

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

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

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

Выделим ячейку С7 — Введем =СУММ ( — Выделим диапазон В2:В5 — Введем звездочку, которая Excel является знаком умножения — Выделим затем диапазон C2:C5 и закроем круглую скобку.

Отметим, что при написании в Excel формул вместо Enter следует вводить Ctrl + Shift + Enter.

  1. Сравнение прайсов.
    Этот пример подойдет для продвинутых пользователей Excel. К примеру, у вас имеется два прайса и вам нужно сравнить их цены. В этих двух прайсах за 6 и 13 мая 2015 года не совпадает часть товаров — как можно узнать, какие это товары.

Для этого:

— В книге необходимо создать еще один лист, а затем скопировать в него имеющиеся списки товаров из первого и второго прайсов.

— Чтобы не было дублей товаров, выделим весь список вместе с названием.

— В меню наживаем кнопку «Данные», затем «Фильтр», а после «Расширенный фильтр».

— В окне, которое появляется, отметим 3 вещи: результат скопировать в другое место; поместить результат в диапазоне — выберите то место, куда после запишете результат, например, ячейка D5; поставить галочку на функции «Только уникальные записи».

— Щелкаем на кнопку «Ок» и, начиная с ячейки D5, получаем нужный список без дублей.

— Удаляем изначальный список.

— Добавляем несколько колонок для загрузки значений вашего прайса за 6 и 13 мая, а также колонку для их сравнения.

— Вводим формулу =D5-C5 в колонку сравнения. Эта формула и вычислит разницу.

— Осталось лишь автоматически загрузить в колонки «6 мая» и «13 мая» значения из ваших прайсов. Чтобы это сделать нужно использовать функцию : =ВПР( искомое_значение; таблица; номер_столбца; интервальный _просмотр).

— «Искомое_значение» — это строка, которую нужно искать в табличке прайса. Легче всего искать нужные товары по их наименованиям.

— «Таблица» — массив данных, где нам нужно будет искать необходимое значение. Массив должен ссылаться на ту таблицу, которая содержит прайс от 6 мая.

— «Номер_столбца» — порядковый номер столбика в диапазоне, который был задан для поиска необходимых данных. Для поиска нами была выбрана таблица из 2-х столбцов. Цена указана во втором из них.

— Интервальный_просмотр. В отсортированной по убыванию или возрастанию таблице, следует ставить значение ИСТИНА. Если же таблица не отсортирована, то нужно писать ЛОЖЬ.

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

— В итоговом столбце будет отражаться разница в ценах по всем тем позициям, которые имеются в обоих прайсах. Если же в вашем итоговом прайсе будет отражаться #Н/Д это будет означать, что указанный товар имеется всего в одном из двух прайсов, поэтому и разницу вычислить не удастся.

  1. Оценка инвестиций.
    Можно посчитать в Excel чистый дисконтированный доход, который назовем NPV — иными словами сумму дисконтированных значений потока платежей на сегодня. В примере мы попытаемся рассчитать величину NPV на основе всего одного периода инвестиций, а также 4-х периодов получения дохода.

— С помощью финансовой функции =ЧПС($B$4;$C$3:$E$3)+B3 можно вычислить NPV  формулой в ячейке В

— В пятой строчке можно найти расчет дисконтированного потока в каждом периоде при помощи двух совершенно разных формул.

— Благодаря формуле =C3/((1+$B$4)^C2) в ячейке С5 будет получен результат.

— Тот же результат будет получен через формулу {=СУММ(B3:E3/((1+$B$4)^B2:E2))}.
в ячейке С6.

  1. Сравним инвестиционные предложения.
    Excel позволяет сравнить, какое именно из двух предложений, касающихся инвестирования, выгоднее всего. Для этого вам придется выписать в 2 столбца требуемый объем инвестиций, а также суммы их поэтапного возврата и указать отдельно в процентах учетную ставку инвестирования. Эти данные помогут вычислить чистую приведенную стоимость, которую обозначим, как NPV.

— Введите в свободную ячейку формулу =npv(b3/12,A8:A12)+A7, где b3 является учетной ставкой, 12 — это число месяц в одном году, A8:A12 считается столбцом с цифрами, означающими поэтапный рост инвестиций, а также А7, означающая необходимую сумму вложений.

— По аналогичной формуле можно также рассчитать чистую приведенную стоимость любого другого инвест-проекта.

— После проекты можно сравнить: проект с большим NPV будет считать выгодным.