Как сделать ряд в excel
Обновлено: 03.07.2024
Возвращает число, обозначающее ранг числа относительно других значений из списка.
Примеры использования функции РАНГ в Excel
- — число : указание на ячейку, позицию которой необходимо вычислить;
- — ссылка : указание на диапазон ячеек, с которыми будет производиться сравнение;
- — порядок : значение, которое указывает на тип сортировки: 0 – сортировка по убыванию, 1 – по возрастанию.
Функция РАНГ.РВ() не отличается по работе от общей функции РАНГ(). Как и было указано выше, если программа обнаружит несколько элементов, значения которых будут равны, то присвоит им высший ранг – например, при совпадении результатов им всем будет присвоено одно место.
Функция РАНГ.СР() указывает, что при совпадении результатов им будет присвоено значение, соответствующее среднему между номерами ранжирования.
Как ранжировать список по возрастанию в Excel
Пример 1. Есть документ, в котором записаны фамилии учеников и суммы баллов по оценкам за четверть. Требуется определить ранжирование учеников по успеваемости.
Используем для ячейки C2 формулу =РАНГ(B2;$B$2:$B$7;0), для ячейки D2 – формулу =РАНГ.РВ(B2;$B$2:$B$7;0), а для ячейки E2 – формулу =РАНГ.СР(B2;$B$2:$B$7;0). Протянем все формулы на ячейки ниже.
Таким образом, видно, что ранжирование по функциям РАНГ() и РАНГ.РВ() не отличается: есть два ученика, которые заняли второе место, третьего места нет, а также есть два ученика, которые заняли четвёртое место, пятого места также не существует. Ранжирование было произведено по высшим из возможных вариантов.
В то же время функция РАНГ.СР() присвоила совпавшим ученикам среднее значение из мест, которые они могли бы занимать, если бы сумма баллов, например, была с разницей в один балл. Для второго и третьего места среднее значение – 2,5; для четвёртого и пятого – 4,5.
Функция РАНГ.РВ
Оператор РАНГ.РВ производит обработку данных и выводит в указанную ячейку порядковый номер заданного аргумента из совокупного списка. Если несколько значений имеют одинаковый уровень, то оператор выводит высший из перечня значений. Если, например, два значения будут иметь одинаковую величину, то им обоим будет присвоен второй номер, а уже следующее по величине значение будет иметь четвертый. Кстати, полностью аналогично поступает и оператор РАНГ в более старых версиях Эксель, так что данные функции можно считать идентичными.
Синтаксис этого оператора записывается следующим образом:
Данную формулу можно записывать вручную, в ту ячейку, где вы хотите, чтобы выводился результат обработки, но для многих пользователей удобнее задавать вводные через окно Мастера функций.
После этого нужно установить курсор в нижний правый угол ячейки, и дождаться появления маркера заполнения в виде небольшого крестика. Затем зажать левую кнопку мыши и протянуть маркер параллельно вычисляемой области.
Как видим, таким образом, формула скопируется, а ранжирование будет произведено на всем диапазоне данных.
Функция РАНГ.СР
Второй функцией, которая производит операцию ранжирования в Экселе, является РАНГ.СР. В отличие от функций РАНГ и РАНГ.РВ, при совпадении значений нескольких элементов данный оператор выдает средний уровень. То есть, если два значения имеют равную величину и следуют после значения под номером 1, то им обоим будет присвоен номер 2,5.
Синтаксис РАНГ.СР очень похож на схему предыдущего оператора. Выглядит он так:
Формулу можно вводить вручную или через Мастер функций. На последнем варианте мы подробнее и остановимся.
- Число (адрес ячейки содержащей элемент, уровень которого следует определить);
- Ссылка (координаты диапазона, ранжирование внутри которого выполняется);
- Порядок (необязательный аргумент).
Как видим, в Экселе существует две функции для определения ранжирования конкретного значения в диапазоне данных: РАНГ.РВ и РАНГ.СР. Для более старых версий программы используется оператор РАНГ, который, по сути, является полным аналогом функции РАНГ.РВ. Главное отличие формул РАНГ.РВ и РАНГ.СР состоит в том, что первая из них указывает наивысший уровень при совпадении значений, а вторая выводит средний показатель в виде десятичной дроби. Это единственное различие между этими операторами, но его нужно учитывать при выборе того, какой именно функцией пользователю лучше воспользоваться.
Функция РАНГ() в MS EXCEL
Функция РАНГ(), английский вариант RANK(), возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. Например, в массиве число 5 будет иметь ранг 1, т.
к. это наименьшее число, число 10 — ранг 2, а 20 — ранг 3 (это ранг по возрастанию, когда наименьшему значению присваивается ранг 1). Если список отсортировать, то ранг числа будет его позицией (если нет повторов).
Нечисловые значения в ссылке игнорируются. Числам, сохраненным в текстовом формате, ранг также не присваивается, функция воспринимает их как текст.
Порядок — число, определяющее способ упорядочения.
- Если порядок равен 0 (нулю) или опущен, то MS EXCEL присваивает ранг=1 максимальному числу, меньшим значениям присваиваются большие ранги.
- Если порядок — любое ненулевое число, то то MS EXCEL присваивает ранг=1 минимальному числу, большим значениям присваиваются большие ранги.
Примечание: Начиная с MS EXCEL 2010 для вычисления ранга также используются функции РАНГ.СР() и РАНГ.РВ(). Последняя функция аналогична РАНГ().
Определяем ранг в списке без повторов
Если список чисел находится в диапазоне A7:A11, то формула =РАНГ(A7;$A$7:$A$11) определит ранг числа из ячейки А7 (см. файл примера).
Т.к. аргумент порядок опущен, то MS EXCEL присвоил ранг=1 максимальному числу (50), а максимальный ранг (5 = количеству значений в списке) — минимальному (10).
Связь функций НАИБОЛЬШИЙ()/НАИМЕНЬШИЙ() и РАНГ()
Функции НАИБОЛЬШИЙ() и РАНГ() являются взаимодополняющими в том смысле, что записав формулу =НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11)) мы получим тот же исходный массив A7:A11.
Определяем ранг в списке с повторами
Если список содержит повторы, то повторяющимся значениям (выделено цветом) будет присвоен одинаковый ранг (максимальный, если использована функция РАНГ() или РАНГ.РВ()) или среднее значение, если РАНГ.СР()).
Наличие повторяющихся чисел влияет на ранги последующих чисел.
Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).
Иногда это не удобно и требуется, чтобы ранги не повторялись (например, при определении призовых мест, когда нельзя занимать нескольким людям одно место).
В этом нам поможет формула =РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1
Предполагается, что исходный список с числами находится в диапазоне А37:А44.
Примечание. В MS EXCEL 2010 добавилась функция РАНГ.
РВ(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается наивысший ранг этого набора значений (присваивает повторяющимся числам одинаковые значения ранга).
В файле примера дается пояснение работы этой функции. Также добавилась функция РАНГ.СР(число;ссылка;[порядок]) Если несколько значений имеют одинаковый ранг, возвращается среднее.
Массив рангов
Для построения некоторых сложных формул массива может потребоваться массив рангов, т.е. тот же набор рангов, но в одной ячейке.
Как сортировать данные в таблицах Excel (правильный способ)
Итак, нам вручили электронную таблицу Excel с тысячами строк внутри нее и вы понимаете, что все данные в неправильном порядке. Возможно, вам придется сортировать её на основе имен столбцов или путем сортировки данных от большего к меньшему.
На первый взгляд, сортировка данных в Excel задача простая, и приложение, безусловно, упрощает сортировку. Однако, более важно то, как вы можете отсортировать и изменить порядок данных в ваших листах. Вот три метода сортировки данных Excel, о которых вы узнаете:
- Сортировка данных всего в несколько кликов
- Установка нескольких, ступенчатых правил сортировки, таких как сортировка по алфавиту по состоянию, а затем по всё вместе.
- Создать полностью свои настройки сортировки, чтобы отсортировать данные с помощью любого установленного вами правила.
Как сортировать данные в электронной таблице Excel (короткое видео)
Этот скринкаст охватывает несколько методов сортировки ваших данных. Просмотрите этот трехминутный видеоролик, чтобы быстро изучить эти профессиональные техники работы Excel. Мы начнем с простой сортировки и перейдем к более продвинутым методам, чтобы вы всегда смогли отсортировать данные так, как вам нужно.
Примеры данных (бесплатная загрузка рабочей книги Excel)
В рамках этого урока я создал книгу, с которой вы можете работать, изучая сортировку данных. Загрузите книгу бесплатно и используйте её во время изучения сортировки в Excel.
Простая сортировка в Excel
Сортировка может быть очень простой, всего пара кликов для перестановки данных в ваших таблицах. Давайте узнаем как.
В книге Excel, начните с нажатия на ячейку столбца, который вы хотите отсортировать. Теперь, убедитесь, что вы находитесь на вкладке Главная на ленте Excel’я и найдите кнопку Сортировка и фильтр на самой правой стороне этой панели.
Кнопка Сортировка и фильтр обитает в самой правый части вкладки Главная.
В зависимости от данных, которые вы выбрали, можете произвести сортировку по алфавитному или числовому порядку. Если столбцы содержат числовые данные, вы можете отсортировать в порядке от малого к большому числу, текстовые данные сортируются в алфавитном порядке.
В примере выше, варианты сортировки изменились, потому что я выбрал столбец с цифрами.
Выполнить обычную сортировку на самом деле так просто. Просто кликните по данным, выберите вариант сортировки и Excel перестроит данные в таблице.
Я отсортировал данные в этой таблице на основе клиента всего в несколько кликов.
Дельный совет: попробуйте также сортировать, щелкнув правой кнопкой мыши внутри столбца и выбрав Сортировка, а затем указать способ сортировки исходных данных.
Как НЕ нужно сортировать данные в Excel
Не менее важно узнать, о самом опасном способе сортировки данных в Excel, такой метод может испортить ваши исходные данные.
Проблема возникает если в таблице много данных, а вы случайно отсортировали только один столбец данных. Каждая строка с данными в Microsoft Excel действительно похоже на запись, которая должна быть такой же по всей строке.
Я задал цвета строкам в этом примере, чтобы мы могли убедиться, что наши данные отсортированы правильно. Цвета должны проходить полностью через каждую строку без перерывов, если данные сортируются правильно.
Большая ошибка пользователей Excel заключается в выборе только одного столбца при сортировке и выборе неправильного параметра в следующем окне.
Excel даже пытается предупредить нас, показывая окно Обнаруженны данные вне указанного диапазона. Во всплывающем окне можно выбрать автоматически расширить выделенный диапазон (выберите это!) и сортировать в пределах указанного выделения.
Я всегда думал, что варианты, которые дает вам это окно, не совсем ясны. Просто знайте, что вы захотите использовать автоматически расширить выделенный диапазон, чтобы убедиться, что Excel затронет все столбцы при сортировке данных.
Для тестирования давайте посмотрим, что произойдет, если мы выберем один столбец и выберем тип сортировки сортировать в пределах указанного выделения.
Использование вариант сортировать в приделах указанного диапазона сортирует только один столбец данных, который обязательно разрушит вашу исходную электронную таблицу.
На скриншоте ниже вы можете видеть, насколько проблематичен этот тип сортировки. Так как столбец Amount Billed был отсортирован от наименьшего до наибольшего, все остальные столбцы остались на месте. Это означает, что наши данные больше не верны.
Как вы видите из несоответствия цветов, были отсортированы только данные в столбце Amount Billed, поэтому теперь таблица некорректна.
- Не начинайте, выделив один столбец в своей электронной таблице.
- Не используйте вариант сортировать в приделах указанного диапазона, если вы работаете не с одним столбцом, убедитесь, что вы расширили выделенный диапазон.
Расширенная сортировка данных Excel
До сих пор простая сортировка позволяла нам сортировать данные однотипно. Что, если мы хотим два типа данных в нашей сортировке?
Что, если мы хотим…
- Сортировка в алфавитном порядке по состоянию, а затем по области.
- Сортировка в алфавитном порядке по имени клиента, а затем по каждому типу проекта, который мы сделали для них.
- Сортировка клиентов в список по алфавиту, а затем по количеству для каждого отдельного проекта, от наибольшего до наименьшего.
Ответ на всё это — расширенная сортировка, при которой вы можете установить несколько уровней сортировки данных. Давайте рассмотрим последний пример, используя образцы данных.
Чтобы начать работу, щелкните где-нибудь внутри своих данных и найдите параметр Сортировка и фильтр, а затем выберите Настраиваемая сортировка.
Перейдите к расширенным параметрам сортировки, выбрав Сортировка и фильтр > Настраиваемая сортировка.
В этом окне мы можем добавить несколько уровней сортировки. Начните с нажатия на раскрывающийся список рядом с Сортировка и выберите столбец, который вы хотите отсортировать.
В моем случае я выберу Client в раскрывающемся меню и оставлю значение Сортировка равным Значения, а Порядок — От А до Я. На простом языке это отсортирует электронную таблицу Excel на основе алфавитного порядка.
Теперь давайте нажмем Добавить уровень. Это создаст новую строку в параметрах сортировки и позволит нам добавить второй уровень организации.
Теперь я могу выбрать Amount billed во втором раскрывающемся списке. Комбинация этих двух правил начнется путем сортировки на основе имени клиента, а затем суммы, выставленного счёта за каждый проект.
Вы можете продолжить добавлять столько уровней, сколько хотите в это окно расширенной сортировки. Последовательность строк имеет значение, т.е. вы можете переместить строку вверх для сортировки сначала по выставленному счету, например, а затем по клиенту.
Как только мы нажмем OK, Excel отсортирует таблицу на основе правил, которые мы создали в этом окне.
Совет: Для более продвинутой сортировки попробуйте в раскрывающимся меню Сортировка изменить тип сортировки на расширенные функции, такие как сортировка на основе цвета ячейки.
Расширенная сортировка позволяет создавать два уровня организации данных в вашей таблице. Если сортировки по одному фактору недостаточно, используйте расширенную сортировку, чтобы добавить больше возможностей.
При изучении величины, принимающей случайные значения (результатов физических измерений в серии экспериментов, экономических показателей, параметров технологических процессов и т.п.), мы имеем дело с выборками. Выборочное наблюдение – это способ наблюдения, при котором обследуется не вся совокупность значений изучаемой величины, а лишь часть ее, отобранная по определенным правилам выборки и обеспечивающая получение данных, характеризующих всю совокупность в целом.
При выборочном наблюдении обследованию подвергается определенная, заранее обусловленная часть совокупности, а результаты обследования распространяются на всю совокупность.
Ту часть единиц, которая отобрана для наблюдения, принято называть выборочной совокупностью или выборкой, а всю совокупность единиц, из которых производится отбор, – генеральной совокупностью.
Число единиц (элементов) статистической совокупности называется ее объемом. Объем генеральной совокупности обозначается N, а объем выборочной совокупности п.
Качество результатов выборочного наблюдения зависит от того, насколько состав выборки представляет генеральную совокупность, иначе говоря, от того, насколько выборка репрезентативна (представительна).
Элементами выборки (x1 х2, . хп) являются числовые значения, называемые вариантами, которые могут быть дискретными, т.е. изолированными (например, целыми числами), или могут принимать значения из некоторого интервала (а, b).
Вариационный ряд получается из выборки упорядочением по возрастанию (или убыванию) и подсчетом частоты каждого значения. Если вариационный ряд содержит значения признака и соответствующие ему частоты,то такой ряд носит название дискретный вариационный ряд. Если нам известно, что исследуемый показатель может принимать любые значения из некоторого интервала, то строим интервальный вариационный.
Удобнее всего ряды распределения анализировать с помощью их графического изображения, позволяющего судить о форме распределения. Наглядное представление о характере изменения частот вариационного ряда дают полигон и гистограмма.
Пример 2.1.
Известны следующие данные о результатах сдачи студентами экзамена (в баллах):
| 18 | 16 | 20 | 17 | 19 | 20 | 17 |
| 17 | 12 | 15 | 20 | 18 | 19 | 18 |
| 18 | 16 | 18 | 14 | 14 | 17 | 19 |
| 16 | 14 | 19 | 12 | 15 | 16 | 20 |
Необходимо построить ряд распределения числа студентов по баллу, представить графически результаты.
Рисунок 2.6. Баллы успеваемости студентов
Определим наименьший и наибольший балл по выборке. Для этого введем в ячейках С1 и С2 соответственно введем формулы =МИН(A2:A29) и =МАКС(A2:A29). Получим значения 12 и 20 соответственно (рис.2.7).
Рисунок 2.7. Минимальный и максимальный балл
Построим вариационный ряд. Для каждого значения необходимо подсчитать частоту. Так как значения признака (балл) отличаются на единицу, то можно воспользоваться следующим способом. В ячейку С4 введем формулу =С1, в С5 соответственно С4+1. Ячейку С5 протянем маркером заполнения (правый нижний угол ячейки) вниз до С12. Результаты представлены на рисунке 2.8.
Рисунок 2.8. Значения признака
Вычислим частоту для каждого значения признака. В ячейку D4 введем формулу =СЧЕТЕСЛИ(A$2:A$29;C4) и протянем D4 маркером вниз до заполнения D12. В ячейке D13 просуммируем частоты с помощью формулы =СУММ(D4:D12).
Получим вариационный ряд (значения признака и соответствующие им частоты) на рисунке 2.9.
Рис.2.9. Частоты вариационного ряда
Вычислим частость (относительную частоту) для каждого значения признака. В ячейку Е4 введем формулу = D4/D$13. Протянем Е4 маркером заполнения вниз до Е12 (рис.2.10).
Рисунок 2.10. Частости ряда распределения
Вычислим накопленные частоты. В ячейку F4 введем формулу =D4, а в ячейку F5 – формулу = D5+F4. Протянем F5 маркером заполнения вниз до F12 (рис.2.11).
Рисунок 2.11. Накопленные частоты ряда
Построим эмпирическую функцию распределения, т.е. найдем наколенные частости. Выделим F4:F12 и маркером заполнения протянем вправо на соседний столбец (рис.2.12). В G4 получим формулу = Е4, в ячейке G5 формулу =Е5+ G4 и т.д.
Рисунок 2.12. Накопленные частости ряда
Рисунок 2.13. Полигон распределения частот
Рисунок 2.14. Полигон распределения частостей
Рисунок 2.15. Гистограмма распределения частостей
Рисунок 2.16. Кумулята
Пример 2.2.
В таблице 2.7 представлены значения процентных ставок по кредитам по 30 коммерческим банкам.
Банковские процентные ставки
| № Банка | Процентная ставка, % |
| 1 | 20,3 |
| 2 | 17,1 |
| 3 | 14,2 |
| 4 | 11,0 |
| 5 | 17,3 |
| 6 | 19,6 |
| 7 | 20,5 |
| 8 | 23,6 |
| 9 | 14,6 |
| 10 | 17,5 |
| 11 | 20,8 |
| 12 | 13,6 |
| 13 | 24,0 |
| 14 | 17,5 |
| 15 | 15,0 |
| 16 | 21,1 |
| 17 | 17,6 |
| 18 | 15,8 |
| 19 | 18,8 |
| 20 | 22,4 |
| 21 | 16,1 |
| 22 | 17,9 |
| 23 | 21,7 |
| 24 | 18,0 |
| 25 | 16,4 |
| 26 | 26,0 |
| 27 | 18,4 |
| 28 | 16,7 |
| 29 | 12,2 |
| 30 | 13,9 |
Построим интервальный вариационный ряд. Для этого вычислим границы интервалов (карманов) с использованием формулы Стэрджесса.
Введем данные в диапазоне A1:A31 (рис.2.17). Определим максимальное и минимальное значения (ячейки С2 и С3 соответственно) так же как и в примере 2.1. Определим число интервалов по формуле Стэрджесса, для чего в ячейку С6 введем формулу =ЦЕЛОЕ(1+3,322*LOG10(30)) (рис.2.18).
Рисунок 2.17. Процентные ставки банков
Рисунок 2.18. Число интервалов
Вычислим длину интервалов, для чего в ячейке С8 введем формулу =ОКРУГЛ((C3-C2)/C6;2) (рис.2.19).
Рисунок 2.19. Длина интервала
Определим нижние и верхние границы интервалов (карманы), для чего в ячейке Е2 запишем формулу =С2, в ячейке Е3 запишем ==E2+$C$8. Протянем Е3 маркером заполнения вниз до Е7 (рис.2.20).
Рисунок 2.20. Границы интервалов
Подсчитаем частоты – в интервал считаем те значения, которые больше нижней границы интервала или равны ей и меньше верхней границы.
Воспользуемся функцией ЧАСТОТА. Для этого в ячейке F2 введем формулу =ЧАСТОТА(A2:A31;E2:E7). Протянем F2 маркером заполнения вниз до F8.
Формулу в этом примере необходимо ввести как формулу массива. Выделим диапазон F2:F8, нажмем клавишу F2, а затем нажмем клавиши CTRL+SHIFT+ВВОД (рис.2.21).
Если формула не будет введена как формула массива, отобразится только одно ее значение в ячейке F2.
Рисунок 2.21. Частоты значений признака
Также можно воспользоваться средством Пакета анализа (Анализ данных в Office 2007) ГИСТОГРАММА (рис.2.22). Выберем входной интервал, интервал карманов, метки, интегральный процент, поместим результаты на этом же листе (укажем ячейку $H$2).
Рисунок 2.22. Построение гистограммы
Полученная гистограмма представлена на рис.2.23.
Рис.2.23. Гистограмма частот
Замечание. Если диапазон карманов не был введен, то набор отрезков, равномерно распределенных между минимальным и максимальным значениями данных, будет создан автоматически.
Вариационный ряд может быть:
– дискретным, когда изучаемый признак характеризуется определенным числом (как правило целым).
Рассмотрим пример построения дискретного вариационного ряда.
Пример 1. Имеются данные о количественном составе 60 семей.
Построить вариационный ряд и полигон распределения
Решение .
Алгоритм построения вариационного ряда:
1) Откроем таблицы Excel.
2) Введем массив данных в диапазон А1:L5. Если вы изучаете документ в электронной форме (в формате Word, например), для этого достаточно выделить таблицу с данными и скопировать ее в буфер, затем выделить ячейку А1 и вставить данные – они автоматически займут подходящий диапазон.
3) Подсчитаем объем выборки n – число выборочных данных, для этого в ячейку В7 введем формулу =СЧЁТ(А1:L5). Заметим, что для того, чтобы в формулу ввести нужный диапазон, необязательно вводить его обозначение с клавиатуры, достаточно его выделить.
4) Определим минимальное и максимальное значение в выборке, введя в ячейку В8 формулу =МИН(А1:L5), и в ячейку В9: =МАКС(А1:L5).
Рис.1.1 Пример 1. Первичная обработка статистических данных в таблицах Excel
5) Далее, подготовим таблицу для построения вариационного ряда, введя названия для столбца интервалов (значений варианты) и столбца частот. В столбец интервалов введем значения признака от минимального (1) до максимального (6), заняв диапазон В12:В17.
6) Выделим столбец частот, введем формулу =ЧАСТОТА(А1:L5;В12:В17) и нажмем сочетание клавиш CTRL+SHIFT+ENTER
Рис.1.2 Пример 1. Построение вариационного ряда
Построим полигон:
Рис.1.3. Пример 1. Построение полигона частот
В реальных социально-экономических системах нельзя проводить активные эксперименты, поэтому данные обычно представляют собой наблюдения за происходящим процессом, например: курс валюты на бирже в течение месяца, урожайность пшеницы в хозяйстве за 30 лет, производительность труда рабочих за смену и т.д. Результаты наблюдений — это в общем случае ряд чисел, расположенных в беспорядке, который для изучения необходимо упорядочить (проранжи- ровать).
Операция, заключающаяся в расположении значений признака по возрастанию, называется ранжированием опытных данных.
После операции ранжирования опытные данные можно сгруппировать так, чтобы в каждой группе признак принимал одно и то же значение, которое называется вариантом (х,). Число элементов в каждой группе называется частотой варианта («,).
Размахом вариации называется число
где хтах — наибольший вариант;
x min — наименьший вариант.
Сумма всех частот равна определенному числу л, которое называется объемом совокупности:
Отношение частоты данного варианта к объему совокупности называется относительной частотой, или частостью, этого варианта:
Последовательность вариант, расположенных в возрастающем порядке, называется вариационным рядом (вариация — изменение).
Вариационные ряды бывают дискретными и непрерывными. Дискретным вариационным рядом называется ранжированная последовательность вариант с соответствующими частотами и (или) частостями.
Пример 1. В результате тестирования группа из 24 человек набрала баллы: 4, 0, 3, 4, 1, 0, 3, 1, 0, 4, 0, 0, 3, 1, 0, 1, 1, 3, 2, 3, 1, 2, 1, 2. Построить дискретный вариационный ряд.
Решение. Проранжируем исходный ряд, подсчитаем частоту и частость вариант: 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3, 3, 4, 4, 4.
В результате получим дискретный вариационный ряд (табл. 3.10).
Ранжированный ряд успеваемости
Число студентов, л,
Относительная частота, А
В Excel проранжируем исходный ряд. Для этого введем все данные в диапазон А1 :А24 и воспользуемся кнопкой Щ (Сортировка по возрастанию).
Подсчитаем частоту и частость вариант. Построим таблицу в диапазоне D2:G7 (рис. 3.13).
Рис. 3.13. Контекстное меню строки состояния
Рассмотрим два варианта подсчета частот:
- 1) выделим диапазон, в котором находятся нули. Щелкнем в нижней правой части окна Excel правой кнопкой мыши и выберем в контекстном меню вид итога, который по умолчанию будет появляться в итоговой строке при выделении произвольного диапазона (см. рис. 3.13) — количество. Таким образом, последовательно выделяя диапазоны с одинаковыми значениями вариант, мы получим все частоты;
- 2) выполним команду Сервис — Анализ данных — Гистограмма. Заполним диалоговое окно в соответствии с рис. 3.14.
В результате получим таблицу с частотами вариантов и соответствующий график (рис. 3.15).
Рис. 3.15. Результаты применения инструмента «Гистограмма)
Найдем объем выборки, заполнив все частоты вариант в диапазоне ЕЗ:Е7, выделим его левой кнопкой мыши и щелкнем по кнопке ? (автосумма).
Предположим, что нам с вами требуется визуализировать данные из вот такой таблицы со значениями продаж автомобилей по разным странам в 2021 году (реальные данные, взятые отсюда, кстати):
Поскольку количество рядов данных (стран) велико, то попытка запихнуть их все сразу в один график приведёт либо к ужасной "спагетти-диаграмме", либо к построению отдельных диаграмм на каждый ряд, что весьма громоздко.
Изящным решением этой проблемы может стать построение диаграммы только по данным из текущей строки, т. е. строки, где стоит активная ячейка:
Реализовать такое очень легко - потребуется лишь две формулы и один крохотный макрос в 3 строки.
Шаг 1. Номер текущей строки
Первое, что нам потребуется - это именованный диапазон, вычисляющий номер строки на листе, где сейчас стоит наша активная ячейка. Открываем на вкладке Формулы - Диспетчер имен (Formulas - Name manager) , жмём на кнопку Создать (Create) и вводим туда следующую конструкцию:
- Имя - любое подходящее имя для нашей переменной (в нашем случае это ТекСтрока)
- Область - здесь и далее нужно выбрать текущий лист, чтобы создаваемые имена были локальными
- Диапазон - тут используем функцию ЯЧЕЙКА (CELL) , которая умеет выдавать кучу разных параметров для заданной ячейки, в том числе и нужный нам номер строки - за это отвечает аргумент "строка".
Шаг 2. Ссылка на заголовок
Для отображения выбранной страны в заголовке и легенде диаграммы, нам нужно получить ссылку на ячейку с её (страны) названием из первого столбца. Для этого создаём еще один локальный (т.е. Область = текущий лист, а не Книга!) именованный диапазон со следующей формулой:
Здесь функция ИНДЕКС выбирает из заданного диапазона (столбца А, где лежат наши страны-подписи) ячейку с номером строки, который мы до этого определили.
Шаг 3. Ссылка на данные
Теперь аналогичным образом давайте получим ссылку на диапазон со всеми данными по продажам из текущей строки, где стоит сейчас активная ячейка. Создаём ещё один именованный диапазон со следующей формулой:
Здесь третий аргумент равный нулю заставляет ИНДЕКС вернуть в качестве результата не отдельное значение, а всю строку.
Шаг 4. Подставляем ссылки в диаграмму
Теперь выделим шапку таблицы и первую строку с данными (диапазон ) и построим по ним диаграмму через Вставка - Диаграммы (Insert - Charts) . Если выделить на диаграмме ряд с данными, то в строке формул отобразится функция РЯД (SERIES) - специальная функция, которую Excel автоматически использует при создании любой диаграммы, чтобы сослаться на исходные данные и подписи:
Аккуратно подменим в этой функции первый (подпись) и третий (данные) аргументы названиями наших диапазонов с шагов 2 и 3:
Диаграмма начнет отображать данные по продажам из текущей строки.
Шаг 5. Макрос пересчета
Остался последний штрих. Microsoft Excel пересчитывает формулы только при изменении данных на листе или при нажатии на клавишу F9 , а мы хотим, чтобы пересчёт происходил при изменении выделения, т. е. при любом перемещении активной ячейки по листу. Для этого потребуется добавить в нашу книгу простой макрос.
Щёлкните правой кнопкой мыши по ярлычку листа с данными и выберите команду Исходный код (Source code) . В открывшееся окно введём код макроса-обработчика события изменения выделения:
Как легко сообразить, всё, что он делает - это запускает пересчет листа при любом изменении положения активной ячейки.
Шаг 6. Подсветка текущей строки
Для наглядности, можно добавить ещё и правило условного форматирования для выделения цветом страны, которая сейчас отображается на графике. Для этого выделим таблицу и выберем Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек (Home - Conditional formatting - New rule - Use a formula to determine which cells to format) :
Здесь формула проверяет для каждой ячейки в таблице совпадение её номера строки с тем номером, что хранится в переменной ТекСтрока, и если совпадение имеет место, то срабатывает заливка выбранным цветом.
Вот и всё - просто и красиво, правда?
Примечания
- На больших таблицах вся эта красота может тормозить - условное форматирование штука ресурсоёмкая, да и пересчёт на каждое выделение тоже может быть тяжеловат.
- Чтобы на графике не пропадали данные при случайном выделении ячейки выше или ниже таблицы, можно добавить в имя ТекСтрока дополнительную проверку вложенными функциями ЕСЛИ вида:
Построение диаграммы в Microsoft Excel по таблице – основной вариант создания графиков и диаграмм другого типа, поскольку изначально у пользователя имеется диапазон данных, который и нужно заключить в такой тип визуального представления.
В Excel составить диаграмму по таблице можно двумя разными методами, о чем я и хочу рассказать в этой статье.
Способ 1: Выбор таблицы для диаграммы
Откройте необходимую таблицу и выделите ее, зажав левую кнопку мыши и проведя до завершения.
Справа отображаются виды выбранного типа графика, а при наведении курсора появляется увеличенный размер диаграммы. Дважды кликните по ней, чтобы добавить в таблицу.
Предыдущие действия позволили вставить диаграмму в Excel, после чего ее можно переместить по листку или изменить размер.
Дважды нажмите по названию графика, чтобы изменить его, поскольку установленное по умолчанию значение подходит далеко не всегда.
Не забывайте о том, что дополнительные опции отображаются после клика правой кнопкой мыши по графику. Так вы можете изменить шрифт, добавить данные или вырезать объект из листа.
Как видно, нет ничего сложного в том, чтобы сделать диаграмму по таблице, заранее выбрав ее на листе. В этом случае важно, чтобы все значения были указаны правильно и выбранный тип графика отображался корректно. В остальном же никаких трудностей при построении возникнуть не должно.
Способ 2: Ручной ввод данных
Преимущество этого типа построения диаграммы в Экселе заключается в том, что благодаря выполненным действиям вы поймете, как можно в любой момент расширить график или перенести в него совершенно другую таблицу. Суть метода заключается в том, что сначала составляется произвольная диаграмма, а после в нее вводятся необходимые значения. Пригодится такой подход тогда, когда уже сейчас нужно составить график на листе, а таблица со временем расширится или вовсе изменит свой формат.
В нем отыщите подходящую так, как это было продемонстрировано в предыдущем методе, после чего вставьте на лист и нажмите правой кнопкой мыши в любом месте текущего значения.
Задайте диапазон данных для диаграммы, указав необходимую таблицу. Вы можете вручную заполнить формулу с ячейками или кликнуть по значку со стрелкой, чтобы выбрать значения на листе.
По завершении редактирования вы увидите готовую диаграмму, которую можно изменить точно таким же образом, как это было сделано ранее.
Вам остается только понять, как сделать диаграмму в Excel по таблице проще или удобнее конкретно в вашем случае. Два представленных метода подойдут в совершенно разных ситуациях и в любом случае окажутся полезными, если вы часто взаимодействуете с графиками во время составления электронных таблиц. Следуйте приведенным инструкциям, и все обязательно получится!
Читайте также: