Как подсчитать в Excel, преобразовав гистограмму в диаграмму подсчета

8 апреля 2022 г.

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

Почему важно подсчитывать в Excel?

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

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

Как подсчитать в Excel, используя график подсчета

Если вы хотите создать итоговую диаграмму в Microsoft Excel, вы можете сделать это, выполнив следующие действия:

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

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

После ввода метки категории добавьте конкретные названия подкатегорий в каждую из ячеек ниже A1. В предыдущем примере вы можете вставить имена людей в ячейки от A2 до A5. Назовите следующий столбец справа «Количество». Этот столбец может содержать ваши данные. В примере с посещаемостью введите количество дней, в течение которых каждый человек посещал занятия, в столбец B, особенно в ячейку рядом с его именем.

2. Добавьте столбец с 5 баллами

Затем разделите данные столбца на группы по пятеркам и единицам, чтобы помочь Excel интерпретировать данные для итогового графика. Для этого добавьте новый заголовок столбца в C1 и назовите его «5-Mark». Найдите верхнюю ячейку, содержащую точку данных, и щелкните соседнюю ячейку в новом столбце. Затем введите формулу “=FLOOR.MATH([Letter][Number],5).” В этой формуле “[Letter][Number]” представляет расположение соседней ячейки. В нашем примере верхняя ячейка, содержащая точку данных, – это B2, поэтому вы должны щелкнуть C2 и ввести “=FLOOR.MATH(B2,5)”.

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

3. Добавьте столбец с 1 оценкой

Затем повторите этот процесс с формулой 1-Mark, чтобы создать запись дополнительных целых чисел от одного до четырех для каждой точки данных. Пометьте следующий столбец справа «1-Mark», а затем щелкните ячейку справа от той, в которой вы записали формулу 5-Mark. Здесь введите формулу “=MOD([Letter][Number],5).” В этой формуле “[Number][Letter]” представляет расположение ячейки в столбце “Количество” в той же строке. Наконец, нажмите “Ввод”, чтобы сгенерировать количество одиночных чисел, оставшихся для этой точки данных, и перетащите ячейку формулы, как описано выше, чтобы она применялась к каждому соответствующему человек в вашем наборе данных.

Чтобы лучше понять это, рассмотрим пример посещаемости. Чтобы создать столбец 1-Mark для этого примера, пометьте ячейку D1 как «1-Mark», а затем щелкните ячейку D2 и введите формулу «=MOD(B2,5)». Если B2 имеет значение 24, C2 теперь имеет значение 20, а D2 имеет значение четыре. Если бы вы сложили вместе значения столбцов C и D для данного человека, вы бы получили общее количество дней, в течение которых они посещали занятия. Отделив пятерки от единиц, вы подготовили Excel для создания итогового графика.

4. Создайте гистограмму

После того, как вы ввели свои данные, вы можете создать итоговую диаграмму в Excel, преобразовав гистограмму. Сначала выберите столбцы с 5 метками и 1 меткой, включая метки столбцов с 5 и 1 метками. Затем перейдите на вкладку «Вставка» на панели инструментов и найдите раздел «Графики». Этот раздел содержит коллекцию значков диаграмм. Значок в верхнем левом углу содержит небольшую гистограмму, состоящую из синей, желтой и коричневой полос.

Если щелкнуть и навести указатель мыши на этот значок, появится всплывающее окно с заголовком «Вставить столбец или гистограмму». Нажмите на маленькую стрелку вниз справа от этого значка и выберите параметр в разделе «2D Bar» с заголовком «Stacked Bar», чтобы создать график. Это создает график, который представляет данные в горизонтальных полосах, где синяя полоса представляет 5 баллов, а оранжевая полоса представляет 1 балл.

5. Переверните ось Y

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

Для этого дважды щелкните диаграмму, чтобы открыть окно сбоку от электронной таблицы Excel с заголовком «Формат области диаграммы». Нажмите «Параметры диаграммы» и выберите «Вертикальная (категория) ось» в раскрывающемся меню. Затем щелкните значок «Параметры оси» справа от списка параметров значка и нажмите «Параметры оси». Наконец, прокрутите вниз и установите флажок рядом с «Категории в обратном порядке», чтобы перевернуть ось Y.

6. Уменьшите ширину зазора

Вы можете устранить пустое пространство между столбцами на графике, уменьшив ширину зазора. Это полезный шаг в создании внешнего вида итогового графика. Чтобы уменьшить ширину, дважды щелкните любой из столбцов на графике, чтобы открыть меню «Формат точки данных». В нижней части «Параметры серии» есть параметр «Ширина зазора» со скользящей шкалой. Сдвиньте маркер до упора влево, чтобы уменьшить зазор по ширине до нуля. Вы также можете сделать это, введя «0» в поле процентов справа от шкалы и нажав «Ввод».

7. Удалите ненужные визуальные элементы и измените положение графика.

Чтобы продолжить процесс визуального преобразования гистограммы в итоговую диаграмму, вы можете удалить ненужные элементы диаграммы, включая оси X и Y, маркер области графика, фоновые линии и заголовок диаграммы. Для этого выберите каждый элемент по отдельности, нажав на каждый, а затем нажмите клавишу «Удалить» на клавиатуре. Когда вы удаляете эти элементы, график расширяется, чтобы заполнить новое пустое пространство. Результатом является чистое отображение баров.

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

8. Настройте свои символы подсчета

Прежде чем приступить к последнему шагу, полезно создать свои контрольные символы в отдельной области рабочей книги Excel. Сначала создайте символ подсчета с 1 баллом, щелкнув пустую ячейку, открыв вкладку «Вставка» на панели инструментов и выбрав «Символы». Во всплывающем меню «Символ» перейдите на страницу «Viner Hand ITC» и прокручивайте, пока не увидите вертикальную линию. Вы также можете найти символ вертикальной линии, введя код символа «007C». Найдя его, выберите его, нажмите кнопку «Вставить» и закройте окно «Символ».

Чтобы создать символ с 5 знаками, повторите описанные выше шаги в новой ячейке, но на этот раз вставьте четыре вертикальные линии. Затем перейдите к «Вставка», нажмите «Иллюстрации», выберите «Фигуры» и нажмите «Линия». Теперь вы можете использовать курсор, чтобы провести диагональную линию через четыре вертикальные линии, чтобы создать символ для пяти контрольных отметок. Если вы хотите изменить цвет итоговой отметки с синего по умолчанию на черный, вы можете сделать это, дважды щелкнув строку, чтобы открыть меню «Формат формы», изменив цвет на черный.

9. Заполните счетные метки

Последним шагом является заполнение гистограммы контрольными отметками. После создания символа 5-Mark щелкните ячейку, содержащую его, и скопируйте значение в «Буфер обмена», щелкнув правой кнопкой мыши и выбрав «Копировать». Затем дважды щелкните синие полосы на графике, чтобы открыть окно «Формат ряда данных», и щелкните значок ведра с краской, чтобы открыть параметры форматирования «Заливка и линия». В разделе «Заливка» выберите параметр «Заливка изображением или текстурой», а затем нажмите «Буфер обмена» под источником изображения под параметрами заливки. Это заполняет каждую полосу одним символом подсчета с 5 знаками.

Прокрутите вниз в меню «Формат ряда данных» и выберите параметр «Стекировать и масштабировать с». Измените число «Единицы/изображение» на пять. Это создает соответствующее количество символов 5-Mark в каждом баре. Теперь вы можете повторить этот процесс с символом 1-Mark, за исключением того, что на этот раз нажмите «Сложить и масштабировать с помощью» и убедитесь, что номер «Единицы/изображение» равен единице. Ваша гистограмма теперь представляет собой итоговую диаграмму, и если вы добавите пять и один символ, каждая строка будет отражать соответствующую точку данных в столбце «Количество».

Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.