Инструменты Exel. Управление запасами

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

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

Инструменты анализа Excel

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

Средства анализа «Что-если»:

1. «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
2. «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
3. «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
4. «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.

Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных .

Другие инструменты для анализа данных:

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



Сводные таблицы в анализе данных

Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.

Программа будет воспринимать введенную/вводимую информацию как таблицу, а не простой набор данных, если списки со значениями отформатировать соответствующим образом:

К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).

Составить отчет можно с помощью «Сводной таблицы».

Создание сводной таблицы – это уже способ анализа данных. Более того, пользователь выбирает нужную ему в конкретный момент информацию для отображения. Он может в дальнейшем применять другие инструменты.

Анализ «Что-если» в Excel: «Таблица данных»

Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» - «Таблица данных».

Важные условия:

• данные должны находиться в одном столбце или одной строке;
• формула ссылается на одну входную ячейку.

Процедура создания «Таблицы данных»:

Анализ предприятия в Excel: примеры

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

Вероятно, вы замечали в разделе Главная → Буфер обмена ленты маленькую кисточку, под которой скрывается команда Формат по образцу . Этот инструмент позволяет легко скопировать форматирование ячейки — он предоставляет больше возможности, чем многие полагают.

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

Основы форматирования по образцу

В упрощенном виде инструмент Формат по образцу используется следующим образом.

1. Выделите ячейку, форматирование которой хотите скопировать.
2. Выполните команду Главная → Буфер обмена → Формат по образцу . Указатель мыши станет похожим на кисть, напоминая, что начинается работа в режиме Формат по образцу — см. рисунок 1.
3. Нажав кнопку мыши, выделите другой диапазон.
4. Отпустите кнопку мыши, и исходное форматирование будет скопировано.

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

Обратите внимание: эта команда зависит от мыши. Форматирование по образцу не может быть выполнено с клавиатуры.

Варианты применения форматирования по образцу

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

Далее, если дважды щелкнуть кнопкой мыши на кнопке Формат по образцу, Excel продолжит работать в таком режиме, пока вы сами это не отмените. Таким образом, вы можете скопировать желаемый формат в несколько диапазонов ячеек. Чтобы выйти из режима форматирования по образцу, нажмите Esc или снова щелкните на значке Формат по образцу .

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

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

Инструмент Формат по образцу можно использовать с фигурами и другими объектами, например с картинками. Просто выделите объект, нажмите кнопку Формат по образцу , а потом щелкните на другом объекте.

При всей своей многофункциональности инструмент «Формат по образцу» не работает с диаграммами.

Как бы ни был удобен инструмент Формат по образцу, он не позволяет совершать некоторые операции, и они выполняются иными способами. Например, нельзя скопировать один диапазон, выделить другой и перенести в последний только форматирование, воспользовавшись командой Главная → Вставить → Сохранить исходное форматирование .

Часто при расчетах в Excel у нас возникают ситуации, когда нужно просчитать формулу при изменении переменной или переменных. За примерами далеко ходить не надо:

1. Какой будет ежемесячный платеж, если берется кредит на 100 тыс. руб. на 3 года при различных процентных ставках.
2. Сколько необходимо платить в месяц, если взять в кредит на год, два, три, четыре, пять при различных процентных ставках банка.

Задача 1. А ведь ничего сложного

На самом деле, ничего сверхъестественного в вычислениях нет. Для расчета ежемесячного платежа по аннуитету используется финансовая функция Excel . Соответственно, зная желаемую сумму займа и срок кредитования, можно рассчитать ежемесячный платеж.

Общая сумма выплат рассчитывается как ежемесячный платеж умноженный на количество периодов (всего 36 месяцев), проценты переплаты – это общая сумма выплат минус сумма займа.

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

Решение первой задачи с помощью инструмента «Таблица данных».

Шаг 1. Делается первый необходимый расчет и создается заготовка под заполнение данными остальных расчетов. Здесь, кстати, абсолютно не важно каким типом ссылок пользоваться, поскольку автозаполнение применятся не будет.

При использовании «Таблица данных» не важен тип ссылок в формуле

Шаг 2. Выделяется диапазон с тестовым расчетом и заготовкой под остальные данные (в нашем случае это диапазон B5:G8), дальше выбирается инструмент «Таблица данных» на вкладке Данные, группа «Прогноз», команда «Анализ что если» (для версии Excel 2016, если у вас версия 2013, то таблица данных находится в группе «Работа с данными»).

Выбор инструмента «Таблица данных» на ленте интерфейса

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

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

После нажатия «ОК» мы получи результат, а при выборе любого из значений, которое было рассчитано, в строке формул увидим формулу массива «{=ТАБЛИЦА(B5;)}», а не формулы расчета, как в случае с автозаполнением.

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

Задача 2. Рассчитать ежемесячный платеж при различных сроках займа и различных процентных ставках.

Подготовительная таблица выглядит следующим образом.

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

Результат работы инструмента «Таблица данных»

Результат работы инструмента «Таблица данных» с двумя входами

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

Больше примеров работы с финансовыми функциями, инструментом «Таблица данных» в .

Умеете ли Вы работать в программе Excel ? Этот вопрос вызывает недоумение, ведь Excel входит в перечень основных офисных программ. И тем не менее, огромное количество офисных сотрудников, для удобства которых эта программа и создавалась, не имеют представления о том, как выглядит работа в Excel . Давайте попробуем вместе укротить этого «страшного зверя» современного офиса.

Работа в Excel: откуда он взялся

Программа Excel входит в стандартный набор офисных программ Microsoft Office. Почему-то считается, что работа в Excel необходима экономистам и бухгалтерам, поскольку работает как большой калькулятор и позволяет делать массу вычислений. Однако, это весьма односторонний взгляд.

Работа в Excel – это огромная таблица, которая позволяет создавать целые массивы информации. Вы можете обрабатывать собранные Вами данные любого формата (числового или буквенного) таким образом, как это необходимо Вам: составляя диаграммы, делая вычисления любого порядка, составляя отчеты различной сложности. А это можно использовать для любых презентаций, для систематизации данных одного или многих форматов, для лучшей иллюстрации и более полного использования статистических данных…

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

Работа в Excel: давайте начнем с интерфейса

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

Являясь важным элементом программы Excel, панель вкладок горизонтальной лентой проходит вдоль всего окна, которое мы видим на мониторе. Обратите внимание на то, что все вкладки представляют собой набор команд по определенной теме. Так, например, если Вы наведете курсор на вкладку «Главная», то Ваша работа в Excel начнется со знакомства с разделами программы, которые отвечают за ввод текста в документе, его расположение не только на странице, но и, главным образом, в определенной ячейке. Вы сможете форматировать документ: по мере необходимости менять шрифт, выравнивать текст или цифру, стоящую в клеточке таблицы таким образом, чтобы она не выбивалась из формата документа.

Над вкладками меню во многих вариантах программы находится панель быстрого доступа. Чаще всего на ней отображены значки действий, которые Вам позволяют сохранить документ, над которым Вы работаете. А также значки, что помогут Вам отменить одно или несколько предыдущих действий либо вернуться к последним введенным данным… Работа в Excel требует правильно организовать свое рабочее пространство: если Вам нужны какие-либо функции на панели быстрого доступа, то Вы всегда можете их там закрепить, выбрав из ниспадающего перечня команд при помощи галочки.

Посмотрите внимательно и Вы увидите, что под лентой вкладок находится еще одна строчка. Она состоит из двух частей:

• Та, что находится с левой стороны, называется строка имени. Работа в Excel предполагает работу с таблицей, так рассмотрите ее внимательнее. Вы увидите, что строки, расположенные горизонтально имеют нумерацию, а столбцы – обозначены буквами. Если сложить букву с цифрой, то получится имя той ячейки, где Вы сейчас работаете. Например, А1. Нашли? Не сложно? Благодаря этой функции при большом массиве информации можно всегда с легкостью определить, в каком месте документа Вы находитесь. Или очень быстро найти необходимые данные.
• Та половина, что находится с правой стороны, называется строка формул. При помощи этой строки можно увидеть содержимое каждой ячейки.

Работа в Excel: как вводить данные?

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

• Числовые значения . Цифры от 0 до 9 и различные символы (знак + или -, $, %...) вводятся следующим образом. Сначала нужную ячейку выделяют, а затем, используя клавиатуру, вводят нужные цифры или символы. Вы увидите вводимые знаки и в строке формул, и в ячейке. По завершении ввода обязательно нажмите Enter, чтобы число сохранилось именно в этой конкретной ячейке. Учтите, что есть нюансы ввода дробных чисел, десятичных, формата времени и даты. Работа в Excel предполагает, что Вы знаете их.

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

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

• Изменения в ячейке. Если Вы еще не фиксировали значения в ячейке при помощи клавиши Enter, то просто при помощи Delete и Backspace удалите неверные цифры или текст и напишите верное значение. В том случае, когда данные уже зафиксированы в ячейке, то на нее нужно навести курсор и щелкнуть два раза. После этого в ячейке появится курсор, как знак того, что работа в Excel может быть отредактирована. В любом случае по окончании изменения данных необходимо нажать клавишу Enter, чтобы сохранить все правки. Возможен вариант, когда Вы ошиблись, изменяя что-то. Все можно вернуть при помощи кнопочки “отменить”.

Работа в Excel: что Вам сказать про формулы...

Формулы, которые используются при построении таблиц, больше всего вызывают трепет у пользователей программы Excel. А между тем, именно ради них все и затевалось. Представьте себе, что у Вас целый столбец цифр и Вам надо сосчитать сумму. Вся надежда на калькулятор! А если такой столбец не один? Работа в Excel позволяет решить эту проблему, нужно лишь правильно создать и записать формулу, по которой и будет произведен расчет

Чтобы программа поняла, что Вы вносите алгоритм действий (формулу), а не цифровые данные, вводится знак равенства. Рассмотрим на примере. Выделим любую ячейку, скажем B1. Там, где у нас строка формул введем следующее: = 5+3. Далее - Enter: в ячейке мы увидим 8, а в строке формул =5+3 - формула. Вы можете потренироваться с различными математическими операциями: умножение, деление, вычитание. Но не забывайте о приоритете одних математических операций над другими. Работа в Excel станет значительно легче, если Вы будете пользоваться скобками. Вы никогда не забудете про приоритеты, так как действия в скобках выполняются в первую очередь.

Чтобы не быть голословными, давайте рассмотрим пример. Давайте внесем в ячейку B1 число 21, а в ячейку В2 - цифру 9. В ячейку В3 мы поместим формулу = В1+В2. В этом случае здесь мы увидим цифру 30. Если Вы измените цифры в ячейках В1 и В2, то сумма в ячейке В3 тоже изменится. Можете поэкспериментировать!

Если у Вас нет желания ошибиться при вводе адресов ячеек, то используйте компьютерную мышь. Работа в Excel предполагает следующий алгоритм действий:

1. необходимо выделить ячейку В3, а в строке формул нужно поставить знак равенства;
2. выделить ячейку В1 и поставить знак плюс;
3. щелкнуть по В2 и важно не забыть нажать Enter.

Заключение

Мы попытались показать Вам лишь самые основные аспекты работы в программе Excel. Возможности Excel неисчерпаемы, и использовать их можно на многих уровнях, ведь специалисты компании Что такое Google Документы и как с ними работать? [Гид для новичков]

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

Свойства

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

Подготовить к общему доступу

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

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

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

Защитить книгу

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

Зашифровать паролем - документ шифруется так, что увидеть его содержимое можно только после ввода пароля. Шифруя книгу паролем, будьте очень внимательны к текущим регистру букв и раскладке клавиатуры, иначе вы рискуете сами лишиться доступа к собственной книге.

Ограничить разрешения для пользователей - эта команда обычно доступна только для автора документа и пользователей корпоративной сети или Интернета. Она использует сервер управления правами пользователей. Это может быть бесплатный сервер Microsoft или корпоративный сервер. Каждый раз, когда пользователь пытается открыть документ, на сервер отправляется запрос, и он возвращает свойства данного пользователя, в частности информацию о том, что в открываемом документе он может видеть, а что должно быть от него скрыто. При попытке выполнить эту команду, вам будет предложено пройти процедуру регистрации на сервере Microsoft. Если вам интересно посмотреть, как работают ограничения, пройдите эту процедуру, зарегистрируйте несколько пользователей с разными правами и протестируйте эту команду.

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

Пометить как окончательный - после выполнения этой команды в документ будет невозможно внести изменения.

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

Защитить структуру книги - запрещает менять структуру книги (добавлять или удалять листы).

Версии

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