Поиск, диаграммы, статистика и сводные таблицы
Изучив основные функции, ссылки на ячейки и функции даты и времени, мы теперь рассмотрим некоторые из более продвинутых функций Microsoft Excel. Мы представляем методы для решения классических проблем в области финансов, отчетов о продажах, стоимости доставки и статистики.
ШКОЛЬНАЯ НАВИГАЦИЯ- Зачем вам формулы и функции?
- Определение и создание формулы
- Относительная и абсолютная ссылка на ячейку и форматирование
- Полезные функции, которые вы должны узнать
- Поиск, диаграммы, статистика и сводные таблицы
Эти функции важны для бизнеса, студентов и тех, кто просто хочет узнать больше.
VLOOKUP и HLOOKUP
Вот пример, иллюстрирующий функции вертикального поиска (VLOOKUP) и горизонтального поиска (HLOOKUP). Эти функции используются для перевода числа или другого значения в нечто понятное. Например, вы можете использовать VLOOKUP, чтобы взять номер детали и вернуть описание товара.
Чтобы исследовать это, давайте вернемся к нашей электронной таблице «Decision Maker» в части 4, где Джейн пытается решить, что надеть в школу. Она больше не интересуется тем, что на ней надето, поскольку у нее появился новый парень, поэтому теперь она будет носить случайные наряды и обувь..
В электронной таблице Джейн она перечисляет наряды в вертикальных столбцах и обувь, горизонтальные столбцы.
Она открывает электронную таблицу, и функция RANDBETWEEN (1,3) генерирует число между или равным одному и трем, что соответствует трем типам одежды, которые она может носить.
Она использует функцию RANDBETWEEN (1,5), чтобы выбрать среди пяти типов обуви.
Поскольку Джейн не может носить число, нам нужно преобразовать его в имя, поэтому мы используем функции поиска.
Мы используем функцию VLOOKUP для перевода номера экипировки в название снаряжения. HLOOKUP переводит с номера обуви на различные типы обуви в ряду.
Электронная таблица работает следующим образом:
Excel выбирает случайное число от одного до трех, так как у нее есть три варианта одежды.
Затем формула переводит число в текст, используя = VLOOKUP (B11, A2: B4,2), который использует случайное число значение из B11 для просмотра в диапазоне A2: B4. Затем он дает результат (C11) из данных, перечисленных во втором столбце.
Мы используем ту же технику, чтобы выбрать обувь, но на этот раз мы используем VOOKUP вместо HLOOKUP.
Пример: базовая статистика
Почти все знают одну формулу из статистики - среднюю - но есть другая статистика, которая важна для бизнеса: стандартное отклонение.
Например, многие люди, поступившие в колледж, испытывали муки из-за их оценки по SAT. Они могут захотеть узнать, как они оцениваются по сравнению с другими студентами. Университеты тоже хотят это знать, потому что многие университеты, особенно престижные, отказывают студентам с низким баллом SAT.
Итак, как бы мы, или университет, измерили и интерпретировали результаты SAT? Ниже приведены результаты SAT для пяти студентов в диапазоне от 1870 до 2230.
Важные цифры для понимания:
Средний - Среднее также называют «средним».
Стандартное отклонение (STD или σ) - Это число показывает, насколько широко разбросан набор чисел. Если стандартное отклонение велико, то числа находятся далеко друг от друга, а если оно равно нулю, все числа одинаковы. Можно сказать, что стандартное отклонение представляет собой среднюю разницу между средним значением и наблюдаемым значением, т. Е. 1 998 и каждым баллом SAT. Обратите внимание, что обычно сокращают стандартное отклонение, используя греческий символ сигма «σ».
Процентиль - Когда ученик получает высокий балл, он может похвастаться тем, что находится в верхнем 99-процентном процентиле или что-то в этом роде. «Процентильный ранг» означает, что процент оценок ниже, чем один конкретный балл.
Стандартное отклонение и вероятность тесно связаны. Можно сказать, что для каждого стандартного отклонения вероятность или вероятность того, что это число находится внутри этого числа стандартных отклонений, равна:
| STD | Процент баллов | Диапазон баллов SAT |
| 1 | 68% | 1,854-2,142 |
| 2 | 95% | 1,711-2,285 |
| 3 | 99,73% | 1,567-2,429 |
| 4 | 99,994% | 1,424-2,572 |
Как вы можете видеть, вероятность того, что какой-либо балл SAT окажется за пределами 3 ЗППП, практически равна нулю, поскольку 99,73% баллов находятся в пределах 3 ЗППП..
Теперь давайте снова посмотрим на таблицу и объясним, как она работает..
Теперь объясним формулы:
= СРЗНАЧ (В2: В6)
Среднее из всех баллов за диапазон B2: B6. В частности, сумма всех баллов, деленная на количество людей, которые прошли тест.
= STDEV.P (В2: В6)
Стандартное отклонение в диапазоне B2: B6. «.P» означает, что STDEV.P используется для всех баллов, то есть для всего населения, а не только для подмножества..
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, В2)
Это вычисляет совокупный процент в диапазоне B2: B6 на основе оценки SAT, в данном случае B2. Например, 83 процента баллов ниже оценки Уокера.
График результатов
Помещение результатов в график облегчает понимание результатов, плюс вы можете показать их в презентации, чтобы сделать вашу точку зрения более четкой.
Студенты находятся на горизонтальной оси, и их баллы SAT показаны в виде синей гистограммы по шкале (вертикальная ось) от 1600 до 2300.
Процентное ранжирование является правой вертикальной осью от 0 до 90 процентов и представлено серой линией.
Как создать диаграмму
Создание диаграммы - это сама по себе тема, однако мы кратко объясним, как была создана вышеуказанная диаграмма..
Сначала выберите диапазон ячеек на графике. В этом случае A2 до C6, потому что мы хотим, чтобы номера, а также имена учеников.
В меню «Вставка» выберите «Графики» -> «Рекомендованные диаграммы»:
Компьютер рекомендует диаграмму «Clustered-Column, Secondary Axis». Часть «Вторичная ось» означает, что она рисует две вертикальные оси. В данном случае этот график - тот, который нам нужен. Нам не нужно больше ничего делать.
Вы можете перемещать график и изменять его размер, пока не получите размер в нужном вам положении. Когда вы будете удовлетворены, вы можете сохранить диаграмму в электронной таблице..
Если вы щелкнете правой кнопкой мыши по диаграмме, то выберите «Выбор данных», и вы увидите, какие данные выбраны для диапазона..
Функция «Рекомендуемые диаграммы» обычно освобождает вас от необходимости иметь дело с такими сложными деталями, как определение, какие данные включать, как назначать метки и как назначать левую и правую вертикальные оси..
В диалоговом окне «Выбор источника данных» нажмите «Оценка» в разделе «Записи легенды (серии)», нажмите «Изменить» и измените его на «Оценка».
Затем измените серию 2 («процентиль») на «процентиль».
Вернитесь к вашему графику и нажмите «Название графика» и измените его на «SAT Scores». Теперь у нас есть полный график. У него две горизонтальные оси: одна для оценки SAT (синяя) и одна для совокупного процента (оранжевая).
Пример: проблема транспортировки
Проблема транспортировки является классическим примером математического типа, называемого «линейное программирование». Это позволяет максимизировать или минимизировать значение с учетом определенных ограничений. У него много приложений для решения широкого круга бизнес-задач, поэтому полезно узнать, как это работает.
Прежде чем мы начнем с этим примером, мы должны включить «Excel Solver».
Включить Solver Add-In
Выберите «Файл» -> «Параметры» -> «Надстройки». В нижней части опций надстроек нажмите кнопку «Перейти» рядом с «Управление: надстройки Excel».
В появившемся меню установите флажок, чтобы включить «Надстройка солвера», и нажмите «ОК».
Пример: вычисление самой низкой стоимости доставки iPad
Предположим, мы отправляем iPad и пытаемся заполнить наши распределительные центры, используя самые низкие транспортные расходы. У нас есть соглашение с компанией по перевозкам грузов и авиакомпаниями об отправке iPad из Шанхая, Пекина и Гонконга в представленные ниже распределительные центры..
Цена доставки каждого iPad - это расстояние от завода до распределительного центра до завода, разделенное на 20 000 километров. Например, расстояние от Шанхая до Мельбурна составляет 8 024 км, что составляет 8 024 на 20 000 долларов или 0,40 долл. США за iPad.
Вопрос в том, как мы доставляем все эти iPad с этих трех заводов в эти четыре пункта назначения с наименьшими затратами.?
Как вы можете себе представить, понять это может быть очень сложно без какой-либо формулы и инструмента. В этом случае мы должны отгрузить 462 000 (F12) iPad. Установки имеют ограниченную мощность 500 250 единиц (G12).
В электронной таблице, чтобы вы могли увидеть, как она работает, мы ввели 1 в ячейку B10, что означает, что мы хотим отправить 1 iPad из Шанхая в Мельбурн. Поскольку транспортные расходы по этому маршруту составляют 0,40 долл. США за iPad, общая стоимость (B17) составляет 0,40 долл. США..
Число было рассчитано с использованием функции = SUMPRODUCT (затраты, отгруженные). «Затраты» - это диапазоны B3: E5..
И «поставляется» диапазон B9: E11:
SUMPRODUCT умножает «затраты» на количество «отгруженного» диапазона (B14). Это называется «умножение матриц».
Для правильной работы SUMPRODUCT две матрицы - стоимость и отгрузка - должны быть одинакового размера. Вы можете обойти это ограничение, добавив дополнительные расходы и отправив столбцы и строки с нулевым значением, чтобы массивы имели одинаковый размер и не влияли на общие затраты..
Использование Солвера
Если бы все, что нам нужно было сделать, это умножить матрицы «затраты» на «отправленные», это не было бы слишком сложным, но мы также должны иметь дело с ограничениями..
Мы должны отправить то, что требуется каждому распределительному центру. Мы помещаем эту константу в решатель следующим образом: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Это означает, что сумма того, что отправлено, то есть суммы в ячейках $ B $ 12: $ E $ 12, должны быть больше или равны тому, что требует каждый центр распределения ($ B $ 13: $ E $ 13).
Мы не можем отправить больше, чем производим. Мы пишем, что ограничения как это: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.
Теперь перейдите в меню «Данные» и нажмите кнопку «Солвер». Если кнопка «Solver» отсутствует, необходимо включить надстройку Solver..
Введите два ограничения, подробно описанные ранее, и выберите диапазон «Отгрузки», который представляет собой диапазон чисел, который мы хотим вычислить в Excel. Также выберите алгоритм по умолчанию «Simplex LP» и укажите, что мы хотим «минимизировать» ячейку B15 («общая стоимость доставки»), где написано «Установить цель».
Нажмите «Решить» и Excel сохранит результаты в таблицу, что мы и хотим. Вы также можете сохранить это, чтобы вы могли поиграть с другими сценариями.
Если компьютер говорит, что не может найти решение, значит, вы сделали что-то нелогичное, например, вы, возможно, запросили больше iPad, чем заводы могут произвести.
Здесь Excel говорит, что нашел решение. Нажмите «ОК», чтобы сохранить решение и вернуться к таблице..
Пример: чистая приведенная стоимость
Как компания решает, стоит ли инвестировать в новый проект? Если «чистая приведенная стоимость» (NPV) будет положительной, они будут инвестировать в нее. Это стандартный подход, принятый большинством финансовых аналитиков.
Например, предположим, что горнодобывающая компания Codelco хочет расширить медный рудник Andinas. Стандартный подход к определению того, стоит ли двигаться вперед с проектом, заключается в расчете чистой приведенной стоимости. Если NPV больше нуля, тогда проект будет прибыльным, учитывая два входа (1) время и (2) стоимость капитала.
Говоря простым языком, стоимость капитала означает, сколько бы заработали эти деньги, если бы они просто оставили их в банке. Вы используете стоимость капитала для дисконтирования денежных значений до приведенной стоимости, другими словами, 100 долларов за пять лет сегодня могут составить 80 долларов..
В первый год 45 миллионов долларов выделяется в качестве капитала для финансирования проекта. Бухгалтеры должны определить, что их стоимость капитала составляет шесть процентов.
Когда они начинают добывать, деньги начинают поступать, когда компания находит и продает произведенную медь. Очевидно, что чем больше они добывают, тем больше денег они зарабатывают, и их прогноз показывает, что их денежный поток увеличивается, пока не достигнет 9 миллионов долларов в год..
Через 13 лет NPV составит 3 945 074 долл. США, поэтому проект будет прибыльным. По мнению финансовых аналитиков, «срок окупаемости» составляет 13 лет.
Создание сводной таблицы
«Сводная таблица» - это в основном отчет. Мы называем их сводными таблицами, потому что вы можете легко переключать их с одного типа отчета на другой, не создавая новый отчет полностью. Поэтому они стержень на месте. Давайте покажем базовый пример, который учит основные понятия.
Пример: отчеты о продажах
Продавцы очень конкурентоспособны (это часть того, чтобы быть продавцом), поэтому они, естественно, хотят знать, как они живут друг с другом в конце квартала и в конце года, а также, сколько их комиссионных будет.
Предположим, у нас есть три продавца - Карлос, Фред и Джули - все продают нефть. Их продажи в долларах за финансовый квартал за 2014 год показаны в таблице ниже..
Чтобы сгенерировать эти отчеты, мы создаем сводную таблицу:
Выберите «Вставить -> Сводная таблица», она находится на левой стороне панели инструментов:
Выберите все строки и столбцы (включая имя продавца), как показано ниже:
Диалоговое окно сводной таблицы появляется в правой части электронной таблицы..
Если щелкнуть все четыре поля в диалоговом окне сводной таблицы (Квартал, Год, Продажи и Продавец), Excel добавит в электронную таблицу отчет, который не имеет смысла, но почему?
Как видите, мы выбрали все четыре поля для добавления в отчет. Поведение Excel по умолчанию группирует строки по текстовым полям, а затем суммирует все остальные строки.
Здесь она дает нам сумму 2014 + 2014 + 2014 + 2014 = 24 168, что является ерундой. Кроме того, он дал сумму четвертей 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Нам не нужна эта информация, поэтому мы отменили выбор этих полей, чтобы удалить их из нашей сводной таблицы.
«Сумма продаж» (общий объем продаж) уместна, поэтому мы исправим это.
Пример: продажа продавцом
Вы можете отредактировать «Сумму продаж», чтобы сказать «Общий объем продаж», что является более понятным. Кроме того, вы можете форматировать ячейки как валюту так же, как и любые другие ячейки. Сначала нажмите «Сумма продаж» и выберите «Настройки поля значения».
В появившемся диалоговом окне мы меняем имя на «Общий объем продаж», затем нажимаем «Числовой формат» и меняем его на «Валюта».
Затем вы можете увидеть свою работу в сводной таблице:
Пример: продажи по продавцу и кварталу
Теперь давайте добавим промежуточные итоги для каждого квартала. Чтобы добавить промежуточные итоги, просто щелкните левой кнопкой мыши на поле «Квартал», удерживайте и перетащите его в раздел «Строки». Вы можете увидеть результат на скриншоте ниже:
Пока мы это делаем, давайте удалим значения «Сумма четверти». Просто нажмите на стрелку и нажмите «Удалить поле». На снимке экрана вы можете видеть, что мы добавили строки «Квартал», которые разбивают продажи каждого продавца на квартал.
Имея в виду эти навыки, теперь вы можете создавать сводные таблицы из ваших собственных данных!
Заключение
В заключение мы показали вам некоторые функции формул и функций Microsoft Excel, которые вы можете применять в Microsoft Excel для своих деловых, академических или других нужд..
Как вы уже видели, Microsoft Excel - это огромный продукт с таким количеством функций, что большинство людей, даже опытных пользователей, не знают всех из них. Некоторые люди могут сказать, что это усложняет; мы считаем, что это более всеобъемлющее.
Надеемся, что, представив вам множество реальных примеров, мы продемонстрировали не только функции, доступные в Microsoft Excel, но и научили вас кое-чему о статистике, линейном программировании, создании диаграмм, использовании случайных чисел и других идеях, которые вы теперь можете принять и использовать в вашей школе или где вы работаете.
Помните, что если вы хотите вернуться и снова пройти урок, вы можете начать с Урока 1 заново.!
