ОСНОВЫ ОБРАБОТКИ ДАННЫХ В Excel
3 сезон
 
INTRODUCTION
Excel – это незаменимый универсальный аналитический инструмент для динамического решения вычислительных задач.

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

1. Знакомство с Excel: интерфейс, горячие клавиши

2. Работа с данными

3. Основные формулы и функции
Tip: Рекомендуем потренироваться в использовании каждой из функций на практике
ЗНАКОМСТВО С EXCEL
Создание книги

  1. Откройте Excel.
  2. Выберите элемент Пустая книга.

Или нажмите клавиши CTRL+N.

Основные элементы интерфейса
ГОРЯЧИЕ КЛАВИШИ
Многие пользователи находят использование внешней клавиатуры с помощью сочетаний клавиш для Excel помогает им работать эффективнее. Для пользователей с ограниченными возможностями или плохим зрением клавиши быстрого доступа могут быть более удобными, чем работа с сенсорным экраном или мышью.
Закрепление областей окна для блокировки строк и столбцов
Чтобы определенная область листа оставалась видимой при прокрутке к другой его области, перейдите на вкладку Вид и выберите команду Закрепить области, чтобы закрепить на месте определенные строки и столбцы, или команду Разделить области, чтобы создать отдельные окна в одном листе (см. Видео).


Закрепление первого столбца

На вкладке Вид нажмите кнопку Закрепить области и выберите пункт Закрепить первый столбец.

Неяркая линия между столбцами A и B указывает на то, что первый столбец закреплен.


Закрепление первых двух столбцов

  1. Выберите третий столбец.

  2. На вкладке Вид нажмите кнопку Закрепить области и выберите пункт Закрепить области.


Закрепление столбцов и строк

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

  2. На вкладке Вид нажмите кнопку Закрепить области и выберите пункт Закрепить области.

РАБОТА С ДАННЫМИ
Условное форматирование

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

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

При применении условного форматирования внешний вид ячеек изменяется на основании указанных вами условий. Если условия выполнены, то форматирование применяется к диапазону ячеек. Если условия не выполнены, форматирование не применяется. Существует множество встроенных условий, а также вы можете создавать собственные условия (в том числе и с помощью формулы, результатом которой становится значение "Истина" или "Ложь").

Условное форматирование месячной записи данных высокой температуры для разных мест, цвета интуитивно соответствуют значениям (значения более высокой температуры — оранжевые и красные, значения низкой температуры — желтые и зеленые)

PIVOT TABLES

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

Сводные таблицы работают немного по-разному в зависимости от того, какая платформа используется для запуска Excel.

Создание сводной таблицы (см. Видео)

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

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

2. На вкладке Вставка нажмите кнопку Сводная таблица.

3. В разделе Выберите данные для анализа установите переключатель Выбрать таблицу или диапазон.

4. В поле Таблица или диапазон проверьте диапазон ячеек.

5. В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист, чтобы поместить сводную таблицу на новый лист. Можно также выбрать вариант На существующий лист, а затем указать место для отображения сводной таблицы.

6. Нажмите кнопку ОК.

Настройка сводной таблицы

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

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


    Чтобы переместить поле из одной области в другую, перетащите его в целевую область.


    Разделение текста на столбцы с помощью мастера распределения текста по столбцам

    С помощью мастера распределения текста по столбцам текст, содержащийся в одной ячейке, можно разделить на несколько (см. Видео).

    1. Выделите ячейку или столбец с текстом, который вы хотите разделить.

    2. На вкладке Данные нажмите кнопку Текст по столбцам.

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

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

    5. Нажмите кнопку Далее.

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

    7. В поле Поместить в выберите место на листе, где должны отображаться разделенные данные.

    8. Нажмите кнопку Готово.



    Удаление повторяющихся значений

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

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

    2. На вкладке Данные нажмите кнопку Удалить дубликаты и в разделе Столбцы установите или снимите флажки, соответствующие столбцам, в которых нужно удалить повторения.

    3. Нажмите кнопку ОК.

    ФОРМУЛЫ И ФУНКЦИИ EXCEL
    ПРОСТЫЕ ФОРМУЛЫ
    Математические формулы

    Все записи формул начинаются со знака равенства (=). Чтобы создать простую формулу, просто введите знак равенства, а следом вычисляемые числовые значения и соответствующие математические операторы: знак плюс (+) для сложения, знак минус (-) для вычитания, звездочку (*) для умножения и наклонную черту (/) для деления. Затем нажмите клавишу ВВОД, и Excel тут же вычислит и отобразит результат формулы.

    Например, если в ячейке C5 ввести формулу =12,99+16,99 и нажать клавишу ВВОД, Excel вычислит результат и отобразит 29,98 в этой ячейке

    Формула, введенная в ячейке, будет отображаться в строке формул всякий раз, как вы выберете ячейку.


    Использование автосуммирования

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


    Избегание переписывания одной формулы

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

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


    Изменение типа ссылки: относительная, абсолютная, смешанная

    По умолчанию используется ссылка на ячейку относительная ссылка, которая означает, что ссылка относительно расположение ячейки. Если, например, ссылаться на ячейку A2 в ячейке C2, Вы действительно ссылки на ячейки, которая находится два столбца слева от (C за вычетом A) — в той же строке (2). При копировании формулы, содержащей относительная ссылка на ячейку, который будет изменяться в формуле ссылку.

    Например, при копировании формулы = B4 * C4 на ячейку D4 D5, формула в D5 регулирует вправо по одному столбцу и становится = B5 * C5. Если вы хотите сохранить исходный в ссылку на ячейку в этом примере при копировании, внесенные ссылку на ячейку абсолютный перед (B и C) столбцов и строк (2), знак доллара ($). Затем, при копировании формулы = $B$ 4 * $C$ 4 из D4 для D5 формулу, должно оставаться точно так же.
    ОСНОВНЫЕ ФУНКЦИИ
    Ниже приведены наиболее часто используемые функции EXCEL и их синтаксис. Для более подробного ознакомления с функциями можно перейти по ссылке, кликнув на название.
    Текстовые функции и функции поиска
    Сцепить, & = СЦЕПИТЬ(текст1;[текст2];…)
    Записывает несколько ячеек в одну


    Сжпробелы/ TRIM = СЖПРОБЕЛЫ(текст)

    Удаляет из текста лишние пробелы.


    Длстр/LEN = ДЛСТР(текст)
    Определяет количество знаков в текстовой строке.


    Левсимв/LEFT = ЛЕВСИМВ(текст;[число_знаков])
    Выдает нужное количество самых левых знаков в строке.


    Правсимв/RIGHT = ПРАВСИМВ(текст;[число_знаков])
    Выдает самые правые знаки текстовой строки.


    Пстр/MID
    Выдает определенное число знаков из строки текста, начиная с указанной позиции.


    ВПР/VLOOKUP = ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
    Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце.

    Видео с примером



    ПОИСКПОЗ/MATCH = ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

    Выдает порядковый номер позиции.
    Важно!!! Имеет значение: выделять, начиная с шапки или начиная с первой позиции

    Логические функции
    Если/IF = ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
    Выполняет проверку условия.


    Или, И/ OR, and= И(логическое_значение1;[логическое_значение2];…)
    Помогают добавить несколько условий в если


    СУММЕСЛИМН/SUMIFS = СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
    Суммирует ячейки, удовлетворяющие заданным условиям

    Видео с примером


    СЧЕТЕСЛИМН/COUNTIFS = СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)
    Подсчитывает количество непустых ячеек, удовлетворяющих заданным условиям внутри диапазона.


    ЕслиОшибка/IFERROR = ЕСЛИОШИБКА(значение;значение_если_ошибка)

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


    ЕПУСТО/ISBLANK= ЕПУСТО(значение)
    Определяет, является ли указанная ячейка пустой.


    ИНДЕКС(массив; номер_строки; [номер_столбца])

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


    СЧЁТЗ/COUNTA = СЧЁТЗ(значение1;[значение2];...)

    Подсчитывает количество непустых ячеек в выделенном диапазоне
    Формулы даты
    День/DAY= ДЕНЬ(дата_в_числовом_формате)
    Преобразует дату в числовом формате в день месяца.


    Месяц/MONTH = МЕСЯЦ(дата_в_числовом_формате)
    Преобразует дату в числовом формате в месяцы.


    Год/YEAR = ГОД(дата_в_числовом_формате)
    Преобразует дату в числовом формате в год.


    Сегодня/TODAY = СЕГОДНЯ()

    Возвращает текущую дату в числовом формате.


    Дата/DATE = ДАТА(год;месяц;день)
    Возвращает заданную дату в числовом формате.


    Датамес/EDATE= ДАТАМЕС(нач_дата;число_месяцев)
    Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.
    Did you like this article?
    СЛЕДУЮЩИЕ МАТЕРИАЛЫ