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

Использование формул и функций

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

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

1. Формула всегда начинается со знака равенства. Если знак равенства опустить, Excel предположит, что в данную ячейку введен текст, и отобразит в ячейке именно то, что было в нее введено.
2. В формуле можно использовать любые арифметические операторы: сложение (+), вычитание (-), умножение (*), деление (/), вычисление процентов (%) или возведение в степень (^).
3. В любую часть формулы можно включить адрес ячейки. При вычислениях Excel вместо адреса подставит значение, содержащееся в соответствующей ячейке так, словно оно было введено непосредственно в формуле.

Функция Excel - это всего лишь специализированная готовая формула. Каждая функция состоит из двух частей: имени (например, СРЗНАЧ) и набора определенных значений, используемых функцией для вычисления результата. Эти значения известны как аргументы, а порядок, в котором нужно ввести имя функции и аргументы, именуется синтаксисом функции. В зависимости от функции, ее аргументы могут представлять собой текст, число, логическое значение либо адрес ячейки или диапазона ячеек. В качестве аргументов функции можно также использовать другие функции или формулы. Аргументы расположены всегда справа от имени функции и заключены в круглые скобки. При использовании в одной функции нескольких аргументов, они разделяются точкой с запятой.

Абсолютные ссылки

Некоторые ссылки в формулах записываются в абсолютной форме - например, $С$3.

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

Для того, чтобы превратить относительную ссылку в абсолютную, достаточно в режиме редактирования формулы установить курсор непосредственно за ссылкой и нажать клавишу <F4>. 
Впрочем, можно вставить в ссылку знаки доллара и при помощи обычных приемов редактирования. При помощи символа абсолютной адресации можно легко изменять способ адресации ячеек. Например, $B11 обозначает, что при копировании формул будет изменяться только адресация строки ячейки, а при обозначении B$11 - только столбца. Такая адресация называется смешанной.

При вводе формулы в  строке формул, можно быстро перебрать  по кругу относительный, смешанный  и абсолютный адреса. Просто указать на какой-нибудь адрес и нажимать <F4>, чтобы по кругу перебрать все четыре варианта.

Использование имен для абсолютной адресации

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

Сортировка и  фильтрация

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

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

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

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

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

Промежуточные итоги

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

Обычно проще создать  список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Синтаксис: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции,ссылка1,[ссылка2],...])

Номер_функции - Число от 1 до 11, которое указывает, какую функцию следует использовать при вычислении итогов внутри списка. Например: 4 –МАКС; 5 – МИН; 9 – СУММ.

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

 

Присвоение имён ячейкам

Чаще гораздо удобнее  работать с ячейками или массивами  ячеек, когда им присвоены имена, особенно если они находятся на разных листах рабочей книги, то бывает легче  от руки вписать имя в формулу, чем выделять их каждый раз на других листах. Теперь вместо выделения ячеек  в формулы можно вводить их имена. Диаграммы Диаграммы используются для  представления рядов числовых данных в графическом виде. Они призваны облегчить восприятие больших объемов  данных и взаимосвязей между различными рядами данных.Чтобы создать диаграмму  в приложении Excel, сначала требуется ввести данные, которые будут использоваться для ее построения, на листе.  Приложение Microsoft Excel поддерживает различные типы диаграмм, позволяя представить данные в наиболее понятном для целевой аудитории виде. При  создании новой диаграммы или  изменении существующей можно выбрать  любой тип или подтип диаграммы. Кроме того, можно создать смешанную диаграмму, используя несколько типов диаграмм. Круговые диаграммы показывают размер элементов в одном ряду данных, пропорционально сумме элементов. Значения данных в круговой диаграмме показываются как доля целого круга. Так как в виде круговой диаграммы можно представить только один ряд данных, данные листа должны быть упорядочены в виде одного столбца или одной строки. Можно также добавить столбец или строку названий категорий, если этот столбец или строка являются первым столбцом или первой строкой выбранного диапазона. Диаграмма состоит из множества  элементов. Некоторые из них отображаются по умолчанию, а другие можно добавлять  при необходимости. Отображение  элементов диаграммы можно изменить путем их перемещения в другое место диаграммы, изменения их размера  или формата. Кроме того, ненужные элементы можно удалить. Поле со списком Представляет собой сочетание  текстового поля и раскрывающегося  списка. Поле со списком компактнее обычного списка, однако для того чтобы  отобразить список элементов, пользователь должен щелкнуть стрелку. Поле со списком  следует использовать, когда требуется  обеспечить возможность ввести в  список или выбрать в нем только один элемент. В этом элементе управления отображается текущее значение, независимо от того, каким образом оно было введено. В свойствах формат объекта, формируется список по диапазону. В поле Связь с ячейкой вводится ссылка на ячейку, в которой хранится выбранный элемент списка. Связанная ячейка возвращает номер выбранного элемента списка. Первый элемент в диапазоне ячеек возвращает значение 1, второй — 2 и так далее. Полоса прокрутки Используется для просмотра  диапазона значений с помощью  кнопок со стрелками для прокрутки  или путем перетаскивания ползунка полосы прокрутки. Можно перемещаться по странице значений (с заранее  заданным интервалом), щелкая область  между ползунком и одной из стрелок для прокрутки. Как правило, пользователь также может вводить текст непосредственно в связанную ячейку или текстовое поле. Полосу прокрутки удобно использовать для ввода или изменения большого диапазона значений, а также в тех случаях, когда точность не важна. Сводные таблицы Сводные таблицы – это  мощный инструмент для анализа данных. Они позволяют представить итоговые значения, вычисленные на основе данных в выбранных столбцах, в удобном  для анализа виде. С помощью  сводных таблиц можно быстро объединять и сравнивать большие объемы информации. Можно менять местами строки и  столбцы для получения различных  итогов по исходным данным, а также  отображать детальные сведения в  нужных областях. Любая сводная таблица  содержит четыре области: область страницы, область столбцов, область строк  и область данных. Каждой из этих областей соответствует область  макета таблицы в диалоговом окне Макет из Мастера сводных таблиц и диаграмм. 1.5 Технология  решения задач анализа деятельности фирмы с помощью Microsoft Excel

Задание №1. Рассчитать стоимость товаров для всех складов без учёта доставки и "утруски".

Для решения данной задачи воспользуемся методом консолидации и автоматизируем расчёт с помощью макроса (Разработчик – Запись макроса):

 

 

 

 

 

 

Используем прием «консолидация по расположению» (Данные – Консолидация):

Запись макроса для отмены предыдущих действий:

Задание №2.Рассчитать сумму, минимальное и максимальное значение  для каждого товара по всем складам, для  доставки и "утруски".

Для решения данной задачи воспользуемся функциями СУММ, МИН, МАКС. Автоматизация расчёта с  помощью макроса:

Запись макроса для отмены предыдущих действий:

Аналогичные действия применяются  для определения максимума функции  и суммы. В результате получим максимальную стоимость по каждому товару:

 

Задание №3. Вычислить итоговую сумму всех товаров на всех складах с учетом доставки и утруски.

Для решения данной задачи воспользуемся функцией СУММ:

Задание №4.Перевести итоговую сумму в иены по курсу 37,1. Для решения данной задачи используем абсолютную адресацию:

Перевести итоговую сумму в белорусские рубли по курсу 12,7. Для решения данной задачи используем прием «имена» (Формулы – Присвоить имя): 

 

Задание №5.Вывести товары, не имеющие «утруски».Для решения данной задачи используем автофильтр (Данные – Фильтр). Условие выборки - столбец«стоимость «утруски»:

 

Результат решения задачи представлен в таблице. Мы вывели все товары, не имеющие «утруски»:

Задание №6.Рассчитать сумму, максимальную стоимость товаров и максимальное количество упаковок на складах за первый и четвертый квартал.

Для решения данной задачи используем функцию промежуточные итоги (ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции,ссылка1,[ссылка2],...])) и после этого применяем автофильтр:

 

Задание №7.Рассчитать стоимостьтоваров по каждому складу. Для решения данной задачи используем промежуточные итоги (Данные – Структура – Промежуточный итог)

Задание №8. Отсортировать значения стоимости товаров по складам по убыванию. Для решения данной задачи используем сортировку (Сортировка - Настраиваемая сортировка):

Результат решения задачи представлен на скриншоте. Мы отсортировали склады по уменьшению стоимости товаров:

Задание №9. Показать отчет за 1 квартал со складов, на которых запасы сахара равны 12 упаковкам или стоимость пшена не меньше 30 тыс.руб.Для решения данной задачи используем расширенный фильтр (Данные – Дополнительно). Результат решения задачи представлен на скриншоте:

Задание №10. Просмотреть общую стоимость и количество упаковок пшена со всех складов по каждому кварталу. Для решения данной задачи воспользуемся сводной таблицей (Вставка – Сводная таблица) и выделим необходимые столбцы: «склад», «период хранения», «пшено» (стоимость и кол-во упаковок), «стоимость».

Результат решения задачи представлен в таблице. Мы получили сумму пшена по каждому кварталу в тысячах рублей (3 столбец) и в упаковках (4 столбец), а также общий итог:

Задание №11.Вывести «Москва, Стремянный пер. д.36 РЭА им.Г.В.Плеханова, имя студента, группа». Для решения данной задачи используем прием «имена»(Формулы – Присвоить имя):

Результат решения задачи представлен в таблице:

Задание №12.Показать процентное соотношение товаров по всем складам и общий доход по каждому продукту. Для решения данной задачи используем круговую диаграмму и гистограмму (Вставка – Диаграмма – Круговая/Гистограмма):

Результат решения задачи представлен на диаграммах:

Задание №13.Вывести связной список «номер склада – стоимость». Для решения данной задачи используеминструмент "поле со списком" (Разработчик – вставка – форма – поле со списком):

Для отображениязначения стоимости всех товаров в выбранном складе, воспользуемся функцией ИНДЕКС (ИНДЕКС(массив, номер_строки, [номер_столбца]).

Результат решения задачи представлен на скриншоте:

Задание №14.Вывести  отдельно от основной таблицы значение «утруски». Для решения данной задачи используеминструмент «полоса прокрутки» (Разработчик – вставка – форма – полоса прокрутки):

Для отображениязначения стоимости с учетом «утруски», воспользуемся функцией ЕСЛИ(ЕСЛИ(лог_выражение;значение_если_истина; значение_если_ложь):

Для отображениязначения«утруски», воспользуемся функцией ИНДЕКС:

Примечание: Если J3 – истина, то прибавляем к стоимости со склада, значение«утруски», иначе получаем только значение стоимости.