Что такое PS в Excel

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 и опубликуйте, что вы хотели бы увидеть и как вы с этим работаете.

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

  
• Рейд Даздурка, что надеть
  
• Сколько видеопамяти необходимо для хранения двух страниц изображения при условии, что разрешение
  
• Как подключить xim apex к ps4
  
• В Директе вы можете настроить таргетинг объявлений на основе данных, собранных с помощью пикселя
  
• Выберите из следующего набора устройств, из которых вы можете собрать компьютер