Каталог статей

• Основы работы с большими данными Python в Excel
• Ссылка на данные
• Очистка данных
  • Дедупликация данных
  • Обработка отсутствующих значений
• обработка данных
  • Расчет данных
  • преобразование данных
• Сортировка данных
• Фильтрация данных
• Типы диаграмм Excel
  • Узнайте, какие типы диаграмм доступны
• Использование диаграммы Excel
  • Как создаются диаграммы
  • Создавайте диаграммы, используя фиксированные диапазоны данных.
  • Редактировать ряд данных
  • Добавить метки данных
  • Формат диаграммы
• сводная таблица Excel
  • Требования к сводной таблице для необработанных данных
  • Создать сводную таблицу
  • Макет полей сводной таблицы
  • Превратите сводную диаграмму в обычную диаграмму

Основы работы с большими данными Python в Excel

Ссылка на данные

Ссылка на текущие данные рабочего листа

• Введите «=" в ячейку B2.

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

Ссылка на текущий номер рабочего листа

• Используйте клавишу Enter, чтобы завершить ввод, и данные в ячейке B2 будут ссылаться на ячейку E2.

• В это время ячейка B2 является ссылочной ячейкой, а ячейка E2 — ссылочной ячейкой. Если ссылочная ячейка будет изменена, ссылочная ячейка также изменится.

Справочные данные из других листов

• В ячейке E1 введите «=".

Примечание. Можно делать ссылки только на отдельные ячейки.

Справочные данные из других листов

• Нажмите на другую таблицу данных, найдите в таблице данные, на которые вы хотите сослаться, и выберите соответствующую ячейку.

Это то же самое, что и текущая ссылка на рабочий лист. Если ссылочная ячейка будет изменена, ссылающаяся ячейка также изменится.

Очистка данных

Дедупликация данных

Используйте функцию удаления дубликатов

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

•Выберите область для расчета.

• Нажмите кнопку «Удалить дубликаты» в меню «Данные».

•Выберите столбцы для сравнения и удалите дубликаты, если все столбцы имеют одинаковое значение.

• Нажмите «ОК», совместимое содержимое будет удалено, и останется только уникальное значение.

Условное форматирование удаляет дубликаты

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

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

данные->удалить Дубликаты->выбиратьудалитьсостояние

Обработка отсутствующих значений

Три распространенных способа борьбы с пропущенными значениями

1. Чтобы заполнить недостающие значения, вы обычно можете использовать статистические значения, такие как среднее/медиана/режим, или вы можете использовать прогнозирование алгоритма.

Медиана: относится к данным, которые упорядочены по размеру для формирования последовательности и расположены в середине последовательности.

Режим: Режим относится к данным, которые чаще всего появляются в наборе данных. Набор данных может иметь несколько режимов или не иметь режима.

2. Удалить пропущенные значения,Если доля отсутствующих данных слишком высока,Рассмотрите возможность удаления,Например, определенный столбецданные>50%Все отсутствуют,Рассмотрите возможность удаленияэта колонка。

3. Игнорируйте значение по умолчанию и не обрабатывайте его.

Заполните недостающие значения средним значением

•Выберите данные в столбце B и рассчитайте среднее значение.

• Скопируйте среднее значение в отдельную строку (выберите значение для вставки), обязательно скопируйте, иначе возникнет циклическая ссылка.

Круговая ссылка: формула в ячейке A применяется к ячейке B, а формула в ячейке B относится к ячейке A.

• Ctrl+G вызывает меню позиционирования, выбирает нулевые значения позиционирования и находит все нулевые значения в столбце B.

• Примените средние данные, удерживайте Ctrl+Enter, заполняя все позиции пропущенных значений.

обработка данных

Расчет данных

1. Простой расчет

В Excel вам нужно научиться «именовывать» ячейки при использовании функций. Имена ячеек и символы операций можно использовать для выполнения простых вычислений значений ячеек.

Например, третья ячейка в столбце B называется «B3».

Обычно используемые операторы точно такие же, как и в математике: сложение (+), вычитание (-), умножение (*), деление (/).

Тогда формула расчета суммы данных в ячейке A1 и ячейке A2 будет «=A1+A2».

1. Часто используемые вычисления функций

Чтобы использовать функцию для вычисления данных, необходим метод именования ячеек диапазона имен.

Например, для диапазона ячеек от A1 до B6 метод именования заключается в добавлении знака «:» между двумя именами ячеек, а метод записи — «A1:B6».

Используйте функции для вычисления данных. Формула записывается следующим образом: английский знак «=" плюс функция плюс область данных.

Например, если вы вычисляете сумму диапазона от ячейки A1 до ячейки B6, а функция суммирования — СУММ, то формула запишется как: =СУММ(A1:B6).

Пример:

Вкладка [Формула] Excel обеспечивает быструю вставку часто используемых функций. Если вы не можете запомнить часто используемые функции, вы можете вставить их, вставив их.

преобразование данных

1. Классификация данных

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

2. Реорганизация данных

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

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

Ввод: =vlookup, функция vlookup появится автоматически, используйте только мышь, не нажимайте Enter. Затем введите три параметра (данные, правила, столбец возвращаемого результата).

3. Объединение данных

Разделение данных означает разделение одного столбца данных на несколько столбцов, а объединение данных означает объединение нескольких столбцов данных в один столбец.

Например, объедините данные столбца «Провинция» и столбца «Город» в данные столбца «Провинция + город» или объедините данные года и месяца в данные «Возраст + месяц».

При объединении данных необходимо гибко использовать логические коннекторы и функции преобразования текста:

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

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

В это время вы можете поместить слова в двойные кавычки на английском языке для объединения.

Ввод: conca автоматически предложит выбрать первую строку для объединения.

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

Сохраните исходный знак процента, вам нужно использовать форматирование текста.

Сортировка данных

Сортировать по числовому значению

Простой способ сортировки данных — выбрать ячейку поля данных и нажать кнопку [По возрастанию] или [По убыванию].

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

Сортировать по цвету

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

Затем в процессе анализа ключевые данные, разбросанные в разных местах таблицы данных, можно просмотреть вместе.

В настоящее время вы можете сортировать ячейки по цвету фона и цвету текста.

Сортировка по алфавиту ручкой/рисунком

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

Фильтрация данных

Обычный фильтр

Чтобы отфильтровать данные таблицы, сначала необходимо войти в режим фильтра.

Метод показан на рисунке ниже слева. Выберите ячейку в первой строке и нажмите кнопку [Фильтр] в меню [Сортировка и фильтр] на вкладке [Главная]. В это время в ячейке имени поля первой строки появится кнопка-треугольник, с помощью которой можно будет выполнить операцию фильтрации.

Пользовательский фильтр

Обычный Фильтр может фильтровать только по одному критерию. Если вам нужно отфильтровать данные, соответствующие двум условиям, вам нужно использовать Пользовательский. фильтр。

Типы диаграмм Excel

Чтобы выявить закономерности данных

Быть убедительным и облегчать общение.

Профессиональные диаграммы могут продемонстрировать профессионализм

Узнайте, какие типы диаграмм доступны

гистограмма

Линейный график

круговая диаграмма

диаграмма областей

радиолокационная карта

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

Как создаются диаграммы

Источником данных диаграммы обычно является статистическая сводная таблица или подробная таблица с относительно небольшим объемом данных.

В зависимости от источника данных существует два метода создания базовых диаграмм:

1. Создайте диаграмму на основе фиксированной области данных на листе.

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

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

1. Используйте все данные в непрерывной области.

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

Выберите некоторые данные

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

Создание диаграмм с использованием фиксированных констант

•Выберите любую ячейку, перейдите на вкладку «Вставка» и выберите соответствующий тип диаграммы.

• В это время будет вставлена ​​пустая диаграмма. Мы выбираем диаграмму, переключаем вкладку «Дизайн диаграммы» и нажимаем, чтобы выбрать значок данных.

• Нажмите кнопку «Добавить», чтобы добавить источник данных.

Введите имена координат и данные.

• Выберите информацию о данных, которая будет отображаться на диаграмме, и нажмите «Изменить», чтобы изменить метки недель.

Нажмите «ОК», чтобы создать диаграмму.

Введение в диаграмму

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

Изменить тип диаграммы

1. После создания диаграммы обнаруживается, что тип диаграммы не соответствует потребностям визуального анализа. Тип диаграммы можно изменить.

Щелкните правой кнопкой мыши на графике,Вызов меню,выбирать Изменить тип диаграммы。

• Выберите тип диаграммы, которую вы хотите изменить, нажмите «ОК», и изменение типа диаграммы будет завершено.

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

• Аналогично щелкните правой кнопкой мыши,Вызовите раскрывающееся меню,Нажмите Изменить тип диаграммы。

1. Добавьте ряд данных

Повторно выберите данные в диалоговом окне «Выбор данных» в дизайне диаграммы.

•Выберите диаграмму ряда данных, который вы хотите добавить.

Редактировать ряд данных

• Щелкните правой кнопкой мыши или переключитесь на вкладку «Дизайн диаграммы» и выберите значок данных.

Нажмите стрелку вверх, чтобы повторно выбрать область данных.

• На этом этапе вновь выбранная серия данных уже находится в списке. Нажмите «ОК», чтобы завершить добавление серии.

1. Конечно, есть более простой способ добавить столбцы данных с помощью сочетаний клавиш ctrl+c ctrl+v.

•Выберите серию данных, которую нужно добавить, с помощью мыши и нажмите Ctrl+C.

Выберите диаграмму и нажмите Ctrl+V.

Не для всех диаграмм требуется легенда. Обычно диаграммы имеют легенду по умолчанию, и вы можете удалить/добавить/изменить ее положение и т. д.

•Можно управлять через меню или выбирать перетаскиванием мыши.

За исключением круговой диаграммы и кольцевых диаграмм, другие стандартные диаграммы обычно имеют как минимум две оси.

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

• Через вкладку «Дизайн диаграммы». —> Добавьте элементы диаграммы —> Варианты названия оси Названия осей могут быть установлены

Добавить метки данных

1. Метки данных относятся к данным, которые отображают информацию о значках на диаграмме.

•Выберите вкладку «Дизайн диаграммы» и выберите «Добавить». элементы диаграммы Этикетка—>данные Этикетка—>лучший матч,Метки данных могут быть автоматически адаптированы.

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

Щелкните диаграмму правой кнопкой мыши, чтобы открыть меню, и выберите «Форматировать метки данных».

• Измените метку в пустом цветовом поле и измените «значение» на «процент», тогда изменение будет успешным.

Формат диаграммы

Обеспечьте целостность диаграммы

Полная диаграмма должна содержать следующие основные элементы: заголовок диаграммы, ряд данных, легенду, ось и единицы данных.

Формат области диаграммы/области рисования

Настройки формата области диаграммы в основном включают шрифт, заливку фона, границу, размер, атрибуты и т. д.

Формат диаграммызаголовок、название оси、легенда

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

Формат осей

Оси координат включают оси классификации и числовые оси. Элементы настройки включают шрифты, линии, заливки, выравнивания, параметры осей и т. д.

Форматировать последовательность данных

Содержимое, которое необходимо отформатировать для последовательности данных, может включать: ① Задание цвета границы/заливки ② Расстояние между классами и коэффициент перекрытия ③ Положение оси координат

Метки данных в основном включают в себя содержимое метки, положение метки, шрифт, выравнивание и т. д.

сводная таблица Excel

Требования к сводной таблице для необработанных данных

Все данные в одной таблице

Необработанные данные сводной таблицы необходимо разместить на одном листе, а не на нескольких листах.

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

Данные должны представлять собой одномерную, а не двумерную таблицу.

Исходные данные сводной таблицы должны представлять собой одномерную таблицу, то есть первая строка таблицы — это имя поля, а следующая — данные, соответствующие полю.

Сводные таблицы невозможно успешно создать с помощью двумерных таблиц.

Не иметь нулевых значений в таблице

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

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

Не иметь объединенных ячеек в таблице

Не допускайте объединения ячеек в исходной таблице сводной таблицы, иначе это легко приведет к ошибкам сводного анализа.

Как заполнить объединенные ячейки: Отменить объединенные ячейки ->Выберите пустые ячейки для заполнения ->Введите формулу->в соответствии сCtrl+Enterклавиша повтора операции。

Формат данных должен быть правильным

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

Создать сводную таблицу

• Используйте рекомендуемые сводные таблицы.

В исходной таблице данных нажмите кнопку [Рекомендуемая сводная таблица] в группе [Таблица] на вкладке [Вставка], и появится ряд рекомендуемых сводных таблиц.

. Как показано на рисунке ниже, выберите разные сводные таблицы, и вы увидите подробную информацию о сводной таблице справа.

•Создание собственных сводных таблиц.

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

Макет полей сводной таблицы

Обзор

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

При необходимости диаграммы также можно использовать для визуального отображения и анализа данных.

Понимание полей

После создания сводной таблицы панель Excel делится на три области: слева — область отображения сводной таблицы, справа вверху — область списка полей и справа внизу — область настройки полей.

Этапы оформления поля ： Проверьте обязательные поля => Установить поля =>（фильтр,Метод расчета） => Проверьте, соответствует ли сводная таблица требованиям

Понимание полей

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

Если вам нужно проанализировать продажи разных товаров разными продавцами, вам необходимо проверить три поля «Продавец», «Название продукта» и «Объем продаж».

Если вам нужно проанализировать продажи разных продавцов и разных категорий товаров, вам необходимо проверить три поля «Продавец», «Категория» и «Сумма (юани)».

Понимание полей

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

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

Настройки поля

• Перемещение полей

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

Настройки поля

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

Как показано на рисунке ниже слева, «Дата» находится в области [Строка]. Выберите «Дата» и перетащите ее в область [Столбец].

Настройки поля

•Значение параметра Установить поля.

Сводная таблица — это таблица, которая позволяет быстро суммировать большие объемы данных. В области Настройки полей сводной таблицы будут подсчитаны поля в области [Значение].

По умолчанию статистическим методом является суммирование. Если в этой области разместить поле «Сумма (юани)», данные о продажах будут суммироваться.

конечный эффект

Анализируйте продажи разных товаров разными продавцами.

Анализируйте продажи разных продавцов и разных категорий товаров.

Добавить сводную диаграмму

Как добавить перспективную диаграмму: выделите ячейки в области сводной таблицы и выберите [Сводная диаграмма] в группе [Инструменты] на вкладке [Анализ сводной таблицы].

Фильтрация содержимого сводной диаграммы

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

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

Превратите сводную диаграмму в обычную диаграмму

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