ШКОЛЬНАЯ НАВИГАЦИЯ
- Зачем нужны формулы и функции?
- Определение и создание формулы
- Относительная и абсолютная ссылка на ячейки и форматирование
- Полезные функции, которые вы должны знать
- Поиск, диаграммы, статистика и сводные таблицы
Эти функции важны для бизнеса, студентов и тех, кто просто хочет узнать больше.
VLOOKUP и HLOOKUP
Вот пример, иллюстрирующий функции вертикального поиска (VLOOKUP) и горизонтального поиска (HLOOKUP). Эти функции используются для перевода числа или другого значения во что-то понятное. Например, вы можете использовать VLOOKUP для принятия номера детали и возврата описания элемента.
Чтобы исследовать это, вернемся к нашей электронной таблице «Решения» в части 4, где Джейн пытается решить, что носить в школе. Она больше не интересуется тем, что она носит, так как она приземлилась на нового парня, поэтому теперь она будет носить случайные наряды и обувь.
В электронной таблице Джейн она перечисляет наряды в вертикальных колоннах и ботинках, горизонтальные колонны.
Она использует функцию RANDBETWEEN (1,5), чтобы выбрать среди пяти типов обуви.
Поскольку Джейн не может носить номер, нам нужно преобразовать его в имя, поэтому мы используем функции поиска.
Мы используем функцию VLOOKUP, чтобы перевести номер экипировки в название экипировки. HLOOKUP переводит от номера обуви к различным типам обуви в ряду.
Электронная таблица работает следующим образом:
Затем формула преобразует число в текст, используя = 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 STD, практически равна нулю, потому что 99,73 процента баллов находятся в пределах 3 STD.
Теперь давайте посмотрим на таблицу и объясним, как она работает.
= СРЗНАЧ (В2: В6)
= STDEV.P (В2: В6)
Стандартное отклонение в диапазоне B2: B6. «.P» означает, что STDEV.P используется во всех показателях, т. Е. Всей совокупности, а не только подмножестве.
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6, В2)
Графирование результатов
Помещение результатов на графике облегчает понимание результатов, плюс вы можете показать его в презентации, чтобы сделать вашу точку более четкой.
Ранжирование процентиля - это правая вертикальная ось от 0 до 90 процентов и представлена серой линией.
Как создать диаграмму
Создание диаграммы - это тема для себя, однако мы кратко объясним, как был создан вышеприведенный график.
Во-первых, выберите диапазон ячеек, которые будут в диаграмме. В этом случае от A2 до C6, потому что нам нужны номера, а также имена учеников.
Функция «Рекомендуемые диаграммы», как правило, сводит вас с ума от необходимости иметь дело с такими сложными деталями, как определение того, какие данные следует включать, как назначать метки и как назначать левую и правую вертикальные оси.
В диалоговом окне «Выбор источника данных» нажмите «оценка» в разделе «Записи легенды (серии)» и нажмите «Изменить» и измените его, чтобы сказать «Оценка».
Пример: проблема транспортировки
Транспортная проблема - классический пример типа математики, называемой «линейное программирование». Это позволяет вам максимизировать или минимизировать значение, зависящее от определенных ограничений. Он имеет множество приложений для широкого круга бизнес-задач, поэтому полезно узнать, как это работает.
Прежде чем мы начнем с этого примера, мы должны включить «Excel Solver».
Включить надстройку Solver
Выберите «Файл» -> «Параметры» -> «Надстройки». В нижней части дополнительных надстроек нажмите кнопку «Перейти» рядом с «Управление: надстройки Excel».
Пример: подсчитайте самые низкие цены на доставку iPad
Предположим, что мы отправляем iPads, и мы пытаемся заполнить наши распределительные центры, используя самые низкие транспортные издержки. У нас есть соглашение с транспортной компанией и авиакомпанией о доставке iPads из Шанхая, Пекина и Гонконга в распределительные центры, показанные ниже.
Цена для отправки каждого iPad - это расстояние от завода до распределительного центра до завода, разделенного на 20 000 километров. Например, это 8 024 км от Шанхая до Мельбурна, что составляет 8 024/20 000 или $ 0,40 за iPad.
Как вы можете себе представить, выяснение этого может быть очень трудным без какой-либо формулы и инструмента. В этом случае мы должны отправить 462 000 (F12) всех iPad. Установки ограничены вместимостью 500 250 (G12) единиц.
В электронной таблице, чтобы вы могли видеть, как это работает, мы набрали 1 в ячейку B10, что означает, что мы хотим отправить 1 iPad из Шанхая в Мельбурн. Поскольку транспортные расходы по этому маршруту составляют 0,40 доллара США за iPad, общая стоимость (B17) составляет 0,40 доллара США.
Использование решения
Если бы все, что нам нужно было сделать, это умножить количество «затраченных» времен «отгруженных» матриц, что было бы не слишком сложным, но мы также должны иметь дело с ограничениями.
Мы должны отправить то, что требуется каждому распределительному центру. Мы помещаем эту константу в решатель следующим образом: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Это означает, что сумма того, что отправляется, т. Е. Суммы в ячейках $ B $ 12: $ E $ 12, должна быть больше или равна тому, что требует каждый центр распределения ($ B $ 13: $ E $ 13).
Введите два указанных выше ограничения и выберите диапазон «Отгрузки», который представляет собой диапазон чисел, который мы хотим вычислить Excel. Также выберите алгоритм по умолчанию «Simplex LP» и укажите, что мы хотим «свести к минимуму» ячейку B15 («общая стоимость доставки»), где говорится «Задайте цель».
Если компьютер говорит, что не может найти решение, то вы сделали то, что нелогично, например, вы могли запросить больше iPads, чем могут произвести растения.
Здесь Excel говорит, что нашел решение. Нажмите «ОК», чтобы сохранить решение и вернуться в таблицу.
Пример: Чистая текущая стоимость
Как компания решает, инвестировать ли в новый проект? Если «чистая приведенная стоимость» (NPV) будет положительной, они будут инвестировать в нее. Это стандартный подход, применяемый большинством финансовых аналитиков.
Например, предположим, что горнодобывающая компания Codelco хочет расширить медную шахту Andinas. Стандартный подход для определения того, следует ли продвигать проект, - это рассчитать чистую приведенную стоимость. Если NPV больше нуля, то проект будет прибыльным с учетом двух входных (1) времени и (2) стоимости капитала.
На простом английском языке стоимость капитала означает, сколько денег будут зарабатывать, если они просто оставят его в банке. Вы используете стоимость капитала для дисконтирования денежных значений до текущей стоимости, другими словами, $ 100 за пять лет может составлять 80 долларов США сегодня.
В первый год 45 миллионов долларов выделены в качестве капитала для финансирования проекта. Бухгалтеры определяют, что их стоимость капитала составляет шесть процентов.
После 13 лет NPV составляет 3 945 074 долларов США, поэтому проект будет прибыльным. По мнению финансовых аналитиков, «срок окупаемости» составляет 13 лет.
Создание сводной таблицы
«Сводная таблица» - это в основном отчет. Мы называем их сводными таблицами, потому что вы можете легко переключать их один тип отчета на другой, не создавая совершенно новый отчет. Поэтому они стержень на месте. Давайте покажем основной пример, который учит основным понятиям.
Пример: отчеты о продажах
Продажи людей очень конкурентоспособны (это часть того, чтобы быть продавцом), поэтому они, естественно, хотят знать, как они гибнут друг против друга в конце квартала и конца года, а также о том, сколько их комиссий будет.
Предположим, у нас есть три продавца - Карлос, Фред и Джули - все продают нефть. Их продажи в долларах за финансовый квартал на 2014 год показаны в таблице ниже.
Выберите «Insert -> Pivot Table», она находится в левой части панели инструментов:
Если мы щелкнем все четыре поля в диалоговом окне сводной таблицы (Quarter, Year, Sales и Salesperson), Excel добавит отчет в электронную таблицу, который не имеет смысла, но почему?
Здесь он дает нам сумму 2014 года + 2014 + 2014 + 2014 = 24 168, что является бессмыслицей. Также он дал сумму кварталов 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Нам не нужна эта информация, поэтому мы снимаем эти поля, чтобы удалить их из нашей сводной таблицы.
Пример: продажа продавцом
Вы можете отредактировать «Сумма продаж», чтобы сказать «Итоговые продажи», что яснее. Кроме того, вы можете отформатировать ячейки как валюты так же, как и форматировать любые другие ячейки. Сначала нажмите «Сумма продаж» и выберите «Настройки полей поля».
Пример: продажи продавцом и кварталом
Теперь давайте добавим промежуточные итоги за каждый квартал. Чтобы добавить промежуточные итоги, просто щелкните левой кнопкой мыши по полю «Квартал» и удерживайте и перетащите его в раздел «Строки». Вы можете увидеть результат на скриншоте ниже:
Заключение
Приобретая, мы показали вам некоторые из особенностей формул и функций Microsoft Excel, которые вы можете применить Microsoft Excel к своим бизнес-задачам, академическим или другим потребностям.
Как вы видели, Microsoft Excel - это огромный продукт с таким количеством функций, что большинство людей, даже продвинутых пользователей, не знают их всех. Некоторые люди могут сказать, что это усложняет ситуацию; мы считаем, что он более всеобъемлющий.
Надеемся, что, представив вам много примеров в реальной жизни, мы продемонстрировали не только функции, доступные в Microsoft Excel, но и научили вас чему-то о статистике, линейном программировании, создании диаграмм, использовании случайных чисел и других идеях, которые вы теперь можете принять и использовать в своей школе или где вы работаете.
Помните, что если вы хотите вернуться и снова взять класс, вы можете начать с урока 1!
