Что такое PS в Excel

Обновлено: 21.11.2024

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

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

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

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

Вы познакомитесь с приемами, необходимыми для эффективной работы с процентами в Excel.

Скринкаст

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

1. Введите исходные данные в Excel

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

Данные рабочего листа Excel в процентах

2. Рассчитать процентное увеличение

Допустим, вы ожидаете, что расходы в следующем году будут на 8 % выше, поэтому хотите узнать, каковы они будут.

Прежде чем писать какие-либо формулы, полезно знать, что Excel достаточно гибок, чтобы вычислять одинаково, независимо от того, вводите ли вы проценты со знаком процента (например, 20%) или в виде десятичной дроби (например, 0,2 или просто 0,2). В Excel символ процента — это просто форматирование.

Мы хотим показать общую предполагаемую сумму, а не только увеличение.

Шаг 1

В формате A18 введите заголовок с увеличением на 8 %. Поскольку у нас есть число, смешанное с текстом, Excel будет рассматривать всю ячейку как текст.

Шаг 2

Нажмите Tab, затем в поле B18 введите процентную формулу Excel: =B17 * 1,08

В качестве альтернативы вы можете ввести формулу следующим образом: =B17 * 108%

Сумма составляет 71 675, как показано ниже:

Расчет процентного увеличения в Excel

3. Рассчитать процентное снижение

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

Шаг 1

В поле A19 введите заголовок с уменьшением на 8%.

Шаг 2

Нажмите Tab, затем в поле B19 введите эту процентную формулу в Excel: =B17 * 0,92

В качестве альтернативы вы можете ввести формулу следующим образом: =B17 * 92%

Сумма составляет 61 057.

Расчет процентного уменьшения в Excel

Шаг 3

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

Для увеличения на 8% введите следующую формулу в поле B18: =B17 + B17 * 0,08

Шаг 4

Для уменьшения на 8 % введите процентную формулу Excel в поле B19: =B17 – B17 * 0,08

С помощью этих формул вы можете просто изменить 0,08 на другое число, чтобы получить новый результат из другого процента.

4. Рассчитать процентную сумму

Теперь поработаем с формулой Excel для процентной суммы. Что, если вы хотите увидеть саму сумму 8%, а не новую сумму? Для этого умножьте общую сумму в ячейке B17 на 8 процентов.

Шаг 1

В A20 введите заголовок 8% от общего количества.

Шаг 2

Нажмите Tab, затем в поле B20 введите формулу: =B17 * 0,08

В качестве альтернативы вы можете ввести формулу следующим образом: =B17 * 8%

Сумма составляет 5 309.

Вычисление итогового процента в Excel

5. Вносите коррективы, не переписывая формулы

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

Шаг 1

В A22 введите Корректировка. Нажмите Enter.

Шаг 2

В поле A23 введите Сумма выше. Нажмите Enter.

Шаг 3

В A24 введите Меньше всего. Нажмите Enter.

Теперь рабочий лист должен выглядеть следующим образом:

Лист Excel с дополнительными заголовками строк

Теперь введите процент и формулы справа от заголовков.

Шаг 4

В поле B22 введите 8%. Нажмите Enter.

Шаг 5

В поле B23 введите следующую формулу, чтобы получить общую сумму плюс еще 8%: =B17 + B17 * B22

Шаг 6

В поле B24 введите следующую формулу, чтобы получить итоговое значение за вычетом 8 %: =B17 * (100 % - B22)

Когда вы вводите 8% в B22, Excel автоматически форматирует ячейку как процент. Если вы наберете 0,08 или 0,08, Excel оставит его таким. Вы всегда можете отформатировать его в процентах позже, нажав кнопку «Процентный стиль» на ленте:

Кнопка Excel Percent Style и ее действие

Совет. Вы также можете отформатировать числа в процентном стиле с помощью сочетания клавиш: нажмите Control-Shift-%< /em> в Windows или Command-Shift-% на Mac.

6. Рассчитать процентное изменение

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

Общее правило расчета процентного изменения:

=(новое значение - старое значение) / новое значение

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

Шаг 1

Чтобы рассчитать первое процентное изменение, введите следующую формулу процентного изменения в ячейку C5: =(B5-B4)/B5

Шаг 2

Excel отображает это как десятичное число, поэтому нажмите кнопку «Стиль процентов» на ленте (или используйте вышеупомянутые сочетания клавиш), чтобы отформатировать его как проценты.

Кнопка Excel Percent Style для процентных изменений

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

Шаг 3

Наведите указатель мыши на точку в правом нижнем углу ячейки, которая показывает -7%.

Точка автозаполнения Excel

Шаг 4

Когда указатель мыши примет вид перекрестия, дважды щелкните его.

Если вы не знакомы с функцией автозаполнения, см. метод 3 в моей статье о мощных методах работы с Excel:

Шаг 5

Нажмите ячейку B3 (заголовок «Сумма»).

Шаг 6

Наведите указатель мыши на точку автозаполнения и перетащите одну ячейку вправо, в ячейку C3. Это дублирует заголовок, включая форматирование.

Шаг 7

В C3 введите % Change, заменив уже существующий текст.

Рассчитаны все процентные изменения

7. Вычислить процент от общего

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

Будь то в Excel или с помощью карандаша и бумаги, процент от общей суммы можно рассчитать простым делением:

Количество компонентов/всего

… и отформатируйте его в процентах.

В этом примере мы делим каждый месяц на общую сумму в нижней части столбца B.

Шаг 1

Нажмите на C3 и выполните автозаполнение одной ячейки до D3.

Шаг 2

Измените D3 на % от общего количества.

Шаг 3

В D4 введите эту формулу, но пока не нажимайте Enter: =B4/B17

Шаг 4

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

Убедитесь, что текстовый курсор все еще находится в формуле на знаменателе «B17».

Шаг 5

Нажмите клавишу F4 (на Mac нажмите Fn-F4).

Это вставляет знаки доллара перед столбцом и строкой, превращая знаменатель в $B$17. $B означает, что столбец B не увеличится до столбца C и т. д., а $17 означает, что строка не увеличится до $18 и т. д.

Шаг 6

Убедитесь, что процентная формула в Excel теперь выглядит так: =B4/$B$17 .

Процент от общей формулы

Шаг 7

Теперь мы можем ввести и автозаполнить.

Нажмите Control-Enter (на Mac нажмите Command-↩), чтобы ввести формулу, не перемещая курсор вниз к следующей строке.

Шаг 8

Нажмите кнопку «Процентный стиль» на ленте или используйте сочетание клавиш Control-Shift-% (Command-Shift-%).

Шаг 9

Наведите указатель мыши на точку автозаполнения в правом нижнем углу D4.

Шаг 10

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

Теперь для каждого месяца будет отображаться процент от общей суммы.

Процент заполненных итогов

8. Процентное ранжирование

Ранжирование чисел по процентам — это статистический метод. Вы, вероятно, знакомы с этим со школы: у вас и ваших одноклассников есть средние баллы, поэтому каждый из вас ранжируется в процентиле. Чем выше ваши оценки, тем выше ваш процентиль.

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

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

Посмотрите на вторую вкладку рабочего листа: Оценки.

Процентное ранжирование в Excel — рабочий лист с оценками

Здесь показан список из 35 средних баллов, отсортированных в порядке возрастания. Первое, что мы хотим сделать, это найти процентильный ранг для каждого из них. Для этого мы будем использовать функцию =PERCENTRANK.INC. Часть «INC» этой функции означает «включающий», поскольку она будет включать первую и последнюю оценки в списке. Если вы хотите исключить первое и последнее числа в массиве, вы должны использовать функцию =PERCENTRANK.EXC.

Функция принимает два обязательных аргумента и имеет следующий синтаксис: =PERCENTRANK.INC(массив, запись)

  • массив — это диапазон ячеек, содержащий список (в данном примере это B3:B37).
  • запись – любое число или ячейка в списке.

Шаг 1

Нажмите C3, первый в списке.

Шаг 2

Начните вводить эту формулу, но пока не нажимайте Enter: =PERCENTRANK.INC(B3:B37

Шаг 3

Этот диапазон должен быть абсолютной ссылкой, чтобы мы могли выполнять автозаполнение до конца.

Нажмите F4 (на Mac нажмите Fn-F4), чтобы вставить знак доллара.

Теперь формула должна выглядеть так: =PERCENTRANK.INC($B$3:$B$37

Шаг 4

В каждом случае вниз по столбцу C мы хотим узнать процентный рейтинг записи в столбце B.

Нажмите B3 и закройте скобки.

Теперь формула выглядит так: =ПРОЦЕНТРАНГ.ВКЛ($B$3:$B$37,B3)

Функция Excel Percentrank

Теперь мы можем заполнить и отформатировать числа.

Шаг 5

При необходимости снова щелкните C3, чтобы выбрать его.

Шаг 6

Дважды щелкните точку автозаполнения на C3. Это автоматически заполняет столбец.

Шаг 7

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

Результат функции Excel Percentrank заполнен и отформатирован

Если бы у вас были очень хорошие оценки и средний балл 3,98, вы бы сказали, что относитесь к 94-му процентилю.

9. Нахождение процентиля

Вы также можете найти процентиль напрямую, используя функции ПРОЦЕНТИЛЬ. С помощью этих функций вы вводите процентный ранг, и он возвращает число из массива, соответствующее этому рангу. Если нужного вам числа нет в списке, Excel интерполирует результат и вернет число, которое «должно» там быть.

Когда бы вы использовали это? Допустим, вы планируете подать заявку на участие в программе магистратуры, и программа принимает только студентов, набравших 60-й процентиль или выше. Итак, вы хотите знать, что средний балл составляет ровно 60%. Глядя на этот список, мы видим, что 3,22 составляет 59 %, а 3,25 — 62 %, поэтому мы можем использовать функцию =PERCENTILE.INC, чтобы найти ответ.

Синтаксис: =PERCENTILE.INC(массив, ранг в процентах)

  • массив — это диапазон ячеек, содержащий список (то же, что и в предыдущем примере, B3:B37).
  • ранг – это процент (или десятичная дробь между 0 и 1 включительно)

Как и в случае с функциями процентного ранжирования, вы можете использовать =PERCENTILE.EXC, чтобы исключить первую и последнюю записи в массиве, но в этом примере это не то, что нам нужно.

Шаг 1

Нажмите B39 под списком.

Шаг 2

Введите эту формулу: =ПРОЦЕНТИЛЬ.ВКЛ(B3:B37,60%)

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

Шаг 3

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

В результате средний балл 3,23 в этом массиве соответствует 60-му процентилю. Теперь вы знаете, какие оценки нужны для принятия в программу.

Результат процентильной функции

Заключение

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

  1. Скобки
  2. Экспоненты
  3. Умножение
  4. Отдел
  5. Дополнение
  6. Вычитание

Я всегда помню это с мнемоникой «Прошу прощения, моя дорогая тетя Салли».

Вывод: приглашенный блогер Дуг Финке рассказывает о своем модуле PowerShell Excel.

Модуль PowerShell Excel — это совершенно новый, интересный и улучшенный способ взаимодействия с Microsoft Excel из Windows PowerShell. Кроме того, для бонусных баллов вам не нужно устанавливать Excel на целевом компьютере для создания электронной таблицы.Многие пользователи этого модуля создают электронные таблицы Excel на серверах, а затем другие сотрудники компании получают отчеты с центрального сервера. Или на последнем этапе сценария они могут отправить файл .xlsx по почте.

Вызов

До сих пор существовало несколько способов переноса данных в Excel. Один из способов — создать файл значений с разделителями-запятыми (.csv) с помощью команды Export-Csv, а затем открыть его в Excel, например:

Процесс получения | Export-Csv – NoType c:\Temp\ps.csv

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

Вот фрагмент, который создает Excel, делает его видимым, а затем добавляет книгу:

$xl = New-Object -ComObject Excel.Application

Войдите в Office, откройте XML

Что, если бы вы могли просто сделать это?

Процесс получения | Экспорт-Excel c:\temp\ps.xlsx – Показать

В этом примере создается файл ps.xlsx, рабочая книга, рабочий лист, строка заголовка и систематизируются все данные в строках и столбцах. Параметр –Show запускает Excel и открывает файл ps.xlsx.

Это здорово (и работает с любыми данными в PowerShell). Плоские данные важны, как и визуальные эффекты.

Поднимитесь на ступеньку выше

Что, если бы вы могли создать следующий визуальный образ? Модуль PowerShell Excel позволяет создавать сводные таблицы и диаграммы Excel на основе преобразованных данных. Из сгенерированных выше данных (и сохраненных в отдельной электронной таблице в той же книге) можно легко создать сводную таблицу и диаграмму.

Вот сценарий:

Процесс получения | Где Компания |

Экспорт-Excel C:\Temp\ps.xlsx-Показать `

-IncludePivotTable -PivotRows Company -PivotData @Handles="sum">`

-IncludePivotChart -ChartType PieExploded3D

Командлеты IncludePivotTable и IncludePivotChart создают сводную таблицу и диаграмму. ChartType позволяет вам выбрать, какой тип диаграммы вы хотите (есть много вариантов). Параметры PivotRows и PivotData описывают, как сводить данные в таблицы.

Если вы запустите Get-Process, вы увидите каждый процесс, запущенный в вашей системе. Сводная таблица в Excel группирует информацию с помощью PivotRows и вычисляет измерения с помощью PivotData. Здесь вы говорите ему суммировать количество ручек. На предыдущем изображении количество дескрипторов суммируется из Get-Process и группируется по компаниям. Теперь вы можете видеть, что процессы, запущенные корпорацией Microsoft, имеют почти 50 тыс. дескрипторов.

Это краткий обзор

Я написал модуль Excel для подключения к экосистеме PowerShell, чтобы вы могли легко экспортировать любые данные в Excel точно так же, как из файла .csv. Вы можете передать результаты запроса SQL Server или REST API — список можно продолжать и продолжать.

Есть еще много интересного в этом модуле. Попробуйте функцию Import-Excel, которая позволяет читать электронную таблицу Excel и преобразовывать ее в объекты PowerShell. Он подходит для использования существующих электронных таблиц, применения вычислений (или добавления дополнительных данных из поиска) и быстрого создания новой электронной таблицы. Вот пример:

Где взять?

Существует несколько способов загрузки и установки этого модуля.

Галерея PowerShell Если вы используете Windows PowerShell 5.0, вы можете использовать новую команду Install-Module ImportExcel. Он вытащит модуль из галереи.

GitHub Вы также можете получить его с GitHub: dfinke/ImportExcel.

Несколько слов о GitHub…

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

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

Читайте также: