Содержание:
- форматы поддерживаемых файлов
- открытие csv файлов
- типы данных
- базовый функционал Excel
------------------------------------------------
Перед тем как приступим рассматривать сам инструмент давай разберемся с какими файлами он работает.
Основные форматы:
- xls
- xlsx
- csv
- xlsm (формат поддерживающий хранение макросов)
Формат xls и xlsx это базовые разрешения для файлов созданных в программе EXCEL, единственно различие это версии: xls (для Excel 97-2003 года), xlsx (для Excel 2010 и выше). В этих форматах зашифрована вся информация о каждой ячейке (фильтры, форматирование цвет/шрифт, сводные таблицы и тд).
Такие форматы могут содержать несколько листов с данными, форматирование, формулы и другие возможности EXCEL.
Формат CSV - это текстовый файл предназначенный для представления табличных данных. Разделителем данных по столбцам служит запятая, но достаточно часто используют и другие разделители (хотя csv==Comma-Separated Values, значения разделенные запятыми. Для разделителя tab (табуляция) есть отдельный формат == tsv и так далее).
Часто используют разделители:
- ";",
- ",",
- "tab",
- " "
- и другие.
Так же формат csv может иметь различную кодировку, это не страшно для файлов которые состоят только из латинских букв (английский алфавит) и цифр, но вот открыть файл с латиницей в другой кодировке будет проблематично на месте латинских букв будет кракозябра.
Посмотри на столбец E (available) - именно про эту кракозябры я говорил.
Открытие файлов
Файлы формата xls и xlsx открываются достаточно просто, достаточно кликнуть по ним :-)
А вот открывать csv файлы лучше через специальную функцию:
Данные - Из текста - выбрать нужный файл
Далее стоит пройти все этапы настройки импорта:
Этап 1: выбираем есть ли разделитель в тексте, если в тексте разделителя (знак по которому можно отделить один столбец от другого) нет - то ставим "Фиксированная ширина". Как видно из предварительного просмотра, у нас разделитель есть и равен он ",". Так же тут можно выбрать с какой по номеру строки начать импорт (строки тут начинаются с 1!). Эта функция может быть полезна, когда вы разбиваете большой csv файл, к примеру более 1млн строк (лимит строк в программе 1048576 строк на один лист), что бы весь набор данных разбить на несколько листов. И конечно же выбор кодировки. Mac OS - дефолтная кодировка для файлов созданных на маке, Windows - кодировка для пк на операционной системе Windows. Разработчики обычно выгружают файлы в кодировке UTF-8. Кодировку иногда можно узнать из свойств файла, но не всегда эта информация присутствует, поэтому самый лучший способ это подбор, пока в окне предварительного просмотра вы не увидите читаемые буквы, попробуйте как минимум начать с этих трех, высока вероятность что вы попадете.
ВАЖНО: Здесь стоит запомнить этот прием, потому что достаточно часто открывая csv файл люди сталкиваются с нечитаемым содержимым и винят во всем битые файлы, но по факту это не так.
Этап 2: Выбор разделителя и ограничителя строк. Функцией "Считать последовательные разделители одним" я ни разу не пользовался.
ВАЖНО: Обрати внимание как изменился предварительный вид после выбора разделителя. Разделителей можно выбирать сразу несколько, если кто-то при создании файла так сделал. Так же можно выбрать свой собственный разделитель в окне "Другое", это может быть что угодно, хоть буква хоть цифра.
Ограничитель строк - функция для определения порогов значения, сам разделитель может содержаться не только для разделения столбцо, но и для части значения:
- "a,b", "a,c" - разобьется на два столбца a,b-a,c
- a,b, a,c - разобьется на 4 столбца a-b-a-c (что не правильно)
Именно по этому используют ограничители строк/значений. Иначе непонятно где разделитель, а где само значение. По стандарту всегда используют ", но бывает и '.
Этап 3: Типы данных, подробнее посмотрим на них ниже. Здесь важно запомнить что настраивается каждый столбец отдельно. В предварительном просмотре кликаете на столбец он выделяется и вы устанавливаете тип.
ВАЖНО: Типы данных можно поменять и после импорта, но важно не потерять данные при импорте. К примеру у вас есть номер товара "00123" при импорте по общему формату это преобразуется в число "123" и в последствии вам не удастся сопоставлять данные, так как 123 не равно 00123. Здесь внимательно относитесь к текстовым данным, которые похоже на числовые, их лучше сразу переопределять на этапе 3.
ВАЖНО: Инструмент импорта часто используют для разделения строки на несколько столбцов Находится он в "Данные-Текст по столбцам".
К примеру если из адреса нужно выделить только город, и структура строки одинаковая.
"страна, город, улица" - разбивается на страна-город-улица
Но будьте внимательны, функция перезаписывает ячейки с данными которые находятся правее. Либо перенесите данные в последний столбец, либо убедитесь что создали достаточное количество пустых столбцов внутри таблицы (плохой пример в конце)
Мы научились импортировать csv файлы и разбивать строки на составляющие. Теперь перейдем к типам данных.
Типы данных
Основные типы данных в ексель:
- дата и время
- число
- текст
При этом отображение этих типов может быть разное. Все форматы можно посмотреть на вкладке "Главная" примерно посередине и здесь же его изменить при необходимости.
---------------------
Начнем с текста, здесь все просто - как пишется так и визуализируется. Но важно разделять текст от числа, напомню: текстовый идентификатор товара "00123" не равен числу "123", пожалуйста учитывайте это. Большинство ошибок с которыми я сталкивался (при построении сводных таблиц или работы функции впр) связаны именно с потерей данных, которая на первый взгляд не критична, но на поиски "почему не находит" может уходить от нескольких дней. Глупая ошибка, согласитесь?
С текстом работают текстовые функции:
- длина строки (по символам) ДЛСТР
- убрать лишние пробелы, кроме одиночных СЖПРОБЕЛЫ
- сцепка двух значений СЦЕПИТЬ
- привести к единому регистру ПРОПИСН, СТРОЧН, ПРОПНАЧ
- приведение текста к числу и обратно ЗНАЧЕН ("123"->123), ТЕКСТ
- поиск подстроки в строке НАЙТИ, ПОИСК
- получить крайний левый/правый символ ЛЕВСИМВ/ПРАВСИМВ
Я выделил те функции которые вам точно пригодятся, глобально важно просто помнить какие функции есть и что они могут делать, учить их не нужно. Когда набьете руку будете на автомате их писать.
---------------------
Теперь рассмотрим тип дата и время. Это тип которых хранит значение даты и/или времени. В исходнике этот тип может быть сохранен как текст и тогда функции даты с ним не будут работать. Что бы правильно поменять формат воспользуйся уже известной функцией "Текст по столбцам".
Функций для работы с датой и временем много, мы их рассмотрим в отдельной статье где будем разбирать функции.
-------------------------------------------------------
Последний тип данных для EXCEL это число. Числа бывают целые 123 и дробные 123.123
Для дробных чисел в екселе может использоваться как "," так и "." По умолчанию используются системные настройки, но их можно изменить.
Для макбуков это делается в системных настройках, и меняется сразу для всего. "Системные настройки - Язык и регион - Дополнительно - Десятичные"
Для Windows это можно сделать непосредственно в программе EXCEL подробнее тут: https://support.microsoft.com/ru-ru/topic/настройка-тысячных-и-десятитысячных-разделителей-в-excel-2010-83c7383c-3a68-3bf8-ba08-4405880c9c47
Поэтому числом будет считаться только те данные в которых указан соответсвующий разделить, числа с другим разделителем будут считаться текстовыми и что бы их преобразовать в число нужно будет с помощью замены изменить разделитель.
Функций по работе с числом множество, их мы рассмотрим в отдельной статье.
Базовый функционал Excel
Файлы в EXCEL представляют собой книги (таблицы с данными), которые содержат один (в случае csv файла) или несколько листов. Таблицы состоят из ячеек. Данные на разных листах можно связывать через формулы, так же можно писать формулу сразу для нескольких открытых книг.
ВАЖНО: Файлы с несколькими книгами, сводными таблицами, формулами лучше сохранять как xlsx файлы, иначе потеряете все данные (пропадет форматирование, фильтры а формулы и сводные таблицы превратятся в значения).
Каждая ячейка имеет свой адрес, который состоит из названия столбца и номера строки и он дублируется в отдельное поле.
Мне удобнее работать через такие абсолютные индексы, но можно работать и через относительные:
Главная
Форматирование текста занимает большой блок на кладке "Главная". Изменять форматирование можно как отдельной ячейки там и сразу выделенный диапазон.
- изменить шрифт, размер, цвет,
- расположение значение относительно ячейки (слева, сверху и тд)
- ориентацию (полезно для объединеных ячеек)
- заливку ячейки
- выделять границы ячейки
- форматирование визуального отображения значения (добавить разделитель разрядов (1000->1 000), перевести в проценты, указать валюту и тд)
Это базовый функционал который достаточно просто освоить.
Из первой вкладки достаточно часто используется "Условное форматирование" когда нужны выделить конкретные ячейки (больше 10, равно 0). В этом форматировании так же можно использовать формулы, сами формулы прописываются в "Создать правило - начертание Классическая". На одну ячейку можно повесить сразу несколько правил.
Я для того что бы не прописывать для каждой ячейки одни и те же правила можно воспользоваться функцией "формат по образцу"
Для построения отчетности можно объединять ячейки в одну "Объединить и поместить в центре" для этого необходимо вначале выделить диапазон который будет объединен.
ВАЖНО: Объединение ячеек оставляет значение только первой ячейки, если в других ячейках у вас есть данные они будут стерты.
Вставлять/удалять столбцы и строки можно через функции вставки и удаления. Вставанию и удаление работает в зависимости от выделенного диапазона. Если выделена одна ячейка, то новая ячейка вставиться на это место, а данные которые расположены ниже спустятся вниз. Что бы вставить целую строку/столбец нужно выделить сразу все (проще всего это сделать кликнув по названию строки/столбца и после этого нажать вставить). После вставки данные по дефолту снижают вниз (для вставки строки) или вправо (для вставки столбца).
Когда вам нужно вставить какие-то значения можно воспользоваться специальной кнопкой "Вставить". Тут есть разные варианты, можно вставить формулы, вставить значения, оставить формат. Я часто использую в работе "формулы" - когда нужно копировать саму формулы, "значения" - когда нужно оставить значения (убрать формулы) и "транспонировать" - эта функция из данных по столбцу делает строки и наоборот, так же это можно использовать когда таблицу нужно поменять и данные по столбца превратить в данные по строкам. Так же можно пользоваться "специальной вставкой", когда все данные нужно разделить или множить на конкрентное значение.
Вставка
Эту вкладку я чаще всего использую для создания сводных таблиц, визуализации графиков и добавления различных фигур и текста на графики.
Данные
На вкладки данные можно импортировать данные из внешних источников (например csv файл), добавить фильтр, разбить значение в одной ячейки на столбцы, удалить дубликаты и сгруппировать строки и столбцы для более красивой визуализации отчета.
Вид
С помощью этой вкладки можно открыть несколько окон одной книги (удобно если часто нужно переключаться между листами одной книги) "Новое окно". Либо для отчета закрепить область, что бы при скроллинге названия столбцов и названия строк оставались видимыми - "Закрепить области"
Владу Формулы мы рассмотрим с вами в отдельной статье, остальные вкладки ты можешь изучить по желанию.
Горячие клавиши
Для быстрого перехода по ячейкам я использую сочетание command+стрелка (позволяет быстро переходить к началу файла cmd+стрелка вверх+стрелка влево, к концу cmd+стрелка вправо+стрелка вниз, комбинации можно использовать разные)
cmd+F - включить окно поиска
Через меня быстрого доступа можно настроить необходимые функции на главную панель. Добавление постоянно используемых функций ускоряет работу в EXCEL
На этом на сегодня все, не забудь сделать рабочие задачи.
Обучение написано для прохождения "Симулятор работы аналитика"
