Таблицы Excel — очень мощный инструмент. В них больше 470 скрытых функций. Поначалу это пугает: кажется, на то, чтобы разобраться со всем, уйдут годы. На самом деле это не так. Всего десятка функций и горячих клавиш уже хватит для того, чтобы сильно упростить себе жизнь. Расскажем о некоторых из них (скоро стартует второй поток курса «Магия Excel»).
Интерфейс
Настраиваем панель быстрого доступа
Начнем с самого простого — добавления самых часто используемых опций на панель быстрого доступа. Чтобы сделать это, заходите в параметры Excel — «Настроить ленту» — и ищите в параметрах «Панель быстрого доступа».
Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.
Другой вариант — просто щелкнуть по инструменту на ленте правой кнопкой мыши и нажать «Добавить…»:
Перемещаемся по ленте без мышки
Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:
Нажмите на клавиатуре любую из букв — попадете на соответствующую вкладку на ленте, а там каждый инструмент в свою очередь тоже будет подписан. Так можно быстро вызвать нужные опции, не трогая мышку.
Ввод данных
Теперь давайте рассмотрим несколько инструментов для быстрого ввода данных.
Автозамена
Если вам часто нужно вводить какое-то словосочетание, адрес, емейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах:
Прогрессия
Если нужно заполнить столбец или строку последовательностью чисел или дат, введите в ячейку первое значение и затем воспользуйтесь этим инструментом:
Протягивание
Представьте, что вам нужно извлечь какие-то данные из целого столбца или переписать их в другом виде (например, фамилию с инициалами вместо полных ФИО). Задайте Excel одну ячейку с образцом — что хотите получить:
Выделите все ячейки, которые хотите заполнить по образцу, — и нажмите Ctrl+E. И магия случится (ну, в большинстве случаев).
Проверка ошибок
Проверка данных позволяет избежать ошибок при вводе информации в ячейки.
Какие бывают типовые ошибки в Excel?
- Текст вместо чисел
- Отрицательные числа там, где их быть не может
- Числа с дробной частью там, где должны быть целые
- Текст вместо даты
- Разные варианты написания одного и того же значения. Например, сокращения («ЭБ» вместо «Электронная библиотека»), лишние пробелы в конце текстового значения или между словами — всего этого достаточно, чтобы превратить текстовые значения в разные и, соответственно, чтобы они обрабатывались Excel некорректно.
Инструмент проверки данных
Чтобы использовать инструмент проверки данных, нужно выделить ячейки, к которым хотите его применить, выбрать на ленте «Данные» → «Проверка данных» и настроить параметры проверки в диалоговом окне:
Если в графе «Сообщение об ошибке» вы выбрали вариант «Остановка», то после проверки в ячейки нельзя будет ввести значения, не соответствующие заданному правилу.
Если же вы выбрали «Предупреждение» или «Сообщение», то при попытке ввести неверные данные будет появляться предупреждение, но его можно будет проигнорировать и все равно ввести что угодно.
Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:
Удаление пробелов
Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:
Дата и время
За любой датой в Excel скрывается целое число. Датой его делает формат.
Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.
Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:
ДД.ММ.ГГГГ
ДД/ММ/ГГГГ
ГГГГ-ММ-ДД
С датами можно производить операции вычитания и сложения.
Вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней.
Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.
Поиск и подстановка значений
Функция ВПР / VLOOKUP
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — «подтянуть» данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции).
=ВПР (что ищем; таблица с данными, где «что ищем» должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])
У нее есть два режима работы: интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
В большинстве случаев мы связываем таблицы по текстовым ключам — в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» равным нулю (или ЛОЖЬ). Только тогда функция будет корректно работать с текстовыми значениями.
Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX
У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР «подтянуть» невозможно.
В реальных условиях структура таблиц бывает разной и не всегда возможно изменить порядок столбцов. Поэтому важно уметь работать с любой структурой.
Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:
=ПОИСКПОЗ (что ищем; где ищем ; 0)
На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).
ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.
=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)
Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.
Получается следующая конструкция:
=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))
Оформление
Нужно оформить ячейки в книге Excel в едином стиле? Для этого есть одноименный инструмент — «Стили».
На ленте инструментов нажмите на «Стили ячеек» и выберите подходящий. Он будет применен к выделенным ячейкам:
А самое главное — если вы применили стиль ко многим ячейкам (например, ко всем заголовкам на 20 листах книги Excel) и захотели что-то переделать, щелкните правой кнопкой мыши и нажмите «Изменить». Изменения будут применены ко всем нужным ячейкам в документе.
На курсе «Магия Excel» будет два модуля — для новичков и продвинутых. Записывайтесь→
Фото на обложке отсюда
Excel открывает большие возможности в обработке массива цифр и строк. Сегодня мы разберем, как в excel обработать большой объем данных. В этой части мы не будем разбирать макросы. Цель этой статьи — научиться работать с самыми доступными и простыми формулами excel, которые помогут выполнить нашу работу в большинстве случаев.
Статья будет разделена на 2 части. Содержание первой части, представлена ниже. Начнем без теории. Вряд ли она вам интересна.
Содержание:
- Как в excel найти повторяющееся значение
- Как в excel быстро удалить дублирующиеся строки
- Работа со сводной таблицей в excel
- Как в excel «подтянуть» данные из другого листа или файла
- Что такое функции правсимв и левсимв и как их применять
Как в excel найти повторяющееся значение
В своде данных мы можем столкнуться с проблемой, когда нам нужно из большого количества строк быстро найти повторяющиеся строки. Ведь в одной строке может быть одно значение, а во второй, по такому же наименованию товара, дубль или другое значение.
Возьмем таблицу. В столбец Е ставим равно и затем, в поиске «Другие функции» ищем нужную нам формулу (см. рис 1)
Для поиска повторяющегося значения, в данном случае, в коде товара по столбцу А, мы будем пользоваться простой формулой = СЧЕТЕСЛИ
Выделяем весь столбец «А», и в диапазоне аргументов функций ( маленькое голубое окошко посреди экрана), у нас появляется А:А, то есть весь выделенный диапазон по этому столбцу. см. рис 3.
Переходим в окно «критерий», и выделяем только первую строку по коду товара. У нас она отразится, как А2. см. рис. 3.
Далее, нажимаем «ок», и в столбце «Е» появляется цифра 1. Это значит, что по товару 100101200 Молоко Вологодское 1% жирности, только один такой товар, нет дублей. См. рис 5.
«Протягиваем» значения по столбцу «Е» вниз, и мы получаем результат, а именно, какие товары у нас имеют дубль в нашем списке, см рис 6. У нас проявилось 2 одинаковых товара, (их excel обозначил цифрой 2), которые, для наглядности вручную выделил желтым.
Если бы у нас было три одинаковых товара в списке, то excel, соответственно, проставил цифру 3. И так далее. Уже через простой фильтр, можно выделить, все, что больше 1 и увидеть полную картину.
Как в excel удалить дублирующиеся строки
По сути, метод, указанный выше, уже выполняет наш запрос. Однако, если Вам не нужны данные с повторяющихся строк, а требуется просто их удалить, тогда есть наиболее простой способ быстрого удаления дублей.
Мы воспользуемся функцией, которая уже встроена в панель excel. См. на панели закладку » ДАННЫЕ». Наша функция так и называется «Удалить дубликаты».
Мы выделяем область поиска, у нас это вновь столбец А. См рис 7.
(В более поздней версии excel, можно все находить через поисковое окно.)
Далее нам просто нужно подтвердить удаление. Однако, для наглядности, выделил зеленым те задвоенные строки, которые у нас есть. Это строка 7 и 21. См рис 8.
Теперь на панели жмем кнопку «удалить дубликаты». У нас появляется окошко. Здесь нам автоматически предлагает удалить всю горизонтальную строку, то есть «автоматически расширить выделенный диапазон». Жмем на кнопку «удалить дубликаты». См рис 9
После этого мы видим, что указаны столбцы отмеченные галочками, которые будут удалены по дублирующейся горизонтальной строке. См. рис 10. Мы жмем «ок».
Все. Теперь мы видим окно с оповещением, что дубль в количестве 1 строки был удален. Теперь, на месте 21-ой строки по товару-дублю, появился следующий товар из нижнего списка. См. рис 11.
Исходя из описания, может показаться, что по времени занимает не меньше, чем в первом варианте, но на самом деле это не так. Я просто эту функцию расписал очень подробно.
Как в excel обработать большой объем данных, сводная таблица
Сводная таблица служит для объединения разрозненной информации воедино. Сегодня мы также научимся это делать. Здесь нет ничего сложного. К примеру нам требуется, сколько же у нас есть одного и того же товара, не по брендам, а по виду товара.
Смотрим нашу таблицу. В панели инструментов ищем закладку «ВСТАВКА». Под панелью инструментов, в верхнем левом углу, появляется иконка, которая так и называется «Сводная таблица». см. рис 12. (Или ищем ее в поиске новой версии excel)
Мы выделяем все столбцы или столбцы интересующих нас значений.
Затем нажимаем на иконку «сводная таблица». У нас выходит окошко, в котором выделен диапазон столбцов. По умолчанию, excel предлагает сводную таблицу вынести на новый лист. см. рис 13. Мы так и делаем.
Подтверждаем команду нажав кнопку «ок». Получаем на новом листе нашей страницы excel возможность построения сводной таблицы, см рис 14.
Теперь мы выбираем нужные нам значения из правого верхнего участка. Раз мы договорились, что нам нужно знать сколько у нас товара по одному наименованию, то выбираем галкой наименование товара. См. рис 15.
По аналогии, мы ставим галку напротив количества (остатки в шт, склад 1).
При этом, перемещаем данные с количеством не в окно «название строк», а в окно «Значения». см. рис 16
Здесь мы видим, что у нас появился дополнительный столбец, но пока не по количеству штук каждого товара, а по количеству строк. Далее мы делаем следующую операцию.
Правой клавишей мыши нажимаем на столбец с количеством. См. рис 17. У нас открывается окно, где в строке ИТОГИ ПО, мы ставим галку не по количеству (строк), как на картинке, а по сумме.
Теперь мы получаем именно сведенное количество по каждому товару. См рис 18.
Для сравнения и наглядности, возвращаемся в исходный лист, (см. рис 19) и мы видим:
одинаковые товары по наименованию, помеченные синим цветом 3+3 = 6 штук.
одинаковые товары, помеченные зеленым 5+56 = 61 штука.
Тоже самое у нас в сводной таблице ( рис 18), 6 и 61 штука.
В сводную таблицу можно добавить поставщика и так далее. Можно ее сделать более сложной в плане количества учитываемый столбцов. Это уже дело необходимости и практики. Один-два раза сделаете, поймете суть. Потом, навык, как в excel обработать большой объем данных на уровне сводной таблицы, уже никогда не забудете.
Как в excel подтянуть данные из одного диапазона в другой, с помощью функции ВПР
Будет логичным, если сразу же покажу, как в excel «подтянуть» данные из другого листа или файла, в другой. Для этого есть замечательная функция ВПР. Мы разберем, как пользоваться этим на уже знакомых нам данных.
К примеру, Вам нужно свести цифры воедино с другого магазина, склада заявки на один лист Excel. Это делается по ключевому значению, которое должно быть во всех источниках данных. Это может быть уникальный код товара или его наименование.
Сразу оговорюсь по наименованию или текстовому значению, функция ВПР бескомпромиссна.
Если в наименовании товара есть пробел или точка, (любое отклонение) то для нее это будет уже другое значение.
Также необходимо, что бы все источники были в одном формате. Если мы говорим о числах, то в числовом формате.
Итак, у нас есть исходный файл, на листе 1, (см. рис 20)
Из листа 2, (рис 21) мы будем подтягивать цифры в лист 1. Обратите внимание, что количества на листах разное. Строки также могут быть смещены в списке или перемешаны, поэтому, простым сложением одной цифры с другой нам не обойтись.
Для нас данные на листе 1 те, к которым нужно подтянуть другие значения. Также действуем через знак равно «=». В левом верхнем углу, через поиск других функций, находим ВПР, см рис 22.
Затем, у нас открывается окно и мы выделяем весь столбец А, то есть искомое значение. Оно в новом окне выделяется, как А:А, см рис 23.
Далее, мышкой переходим в самом окошке на вторую строку «таблица», только после этого переходим на лист 2 нашего файла.
И от столбца «А» выделяем и протягиваем к столбцу с количеством. В данном случае, к столбцу «D», см рис 25.
Столбец D, это четвертый столбец начиная с искомого значения, то есть с кода товара в столбце А.
Поэтому, мы ставим в третьем поле окошка «номер столбца» цифру 4. и в поле «интервальный просмотр» всего ноль. В итоге у нас получается заполненное окошко, см рис 26.
Нажимаем «ок», и получаем подтянутую цифру со второго листа, по коду товара 100101200. см. рис 27.
Протягиваем значение вниз, столбец D заполняется цифрами с листа 2. см. рис 28. Здесь нам остается просто сложить одни цифры с другими простой формулой сложения и протянуть вниз.
Таким образом, мы можем подтянуть значение из большого массива данных, которое вручную искать долго и не целесообразно, если есть функция ВПР.
Важный момент. Если Вы подтягиваете из другого файла, то файлы должны быть сохранены. И еще. Формулы подтянутых значений остаются. Вам нужна цифры привести в значения или не удалять и не менять значения, которые Вы подтягивали.
Как в excel обработать большой объем данных, функция правсимв и левсимв
Бывает, что необходимо для работы с функцией ВПР, привести искомые значения, и значение которые мы подтягиваем в единую форму. Как мы говорили выше, для ВПР любое отклонение, даже пробел, это уже другое значение.
Для этого, нам в помощь функция excel: правсимв и левсимв. То есть с помощью этой функции можно слева или справа нашего значения, например наименования товара, убрать лишние знаки.
Итак,, нам нужно взять только часть от полного наименования. Смотрим наш рис 29, к примеру, нам нужно только слово «молоко». Мы также в окне поиска формул ищем = левсимв.
У нас появляется окошко, см рис 30.
Мы выделяем интересующий нас столбец «В», в строке «текст» он появляется как В:В, см рис 31.
Далее, в строку «количество знаков» мы ставим ту цифру, сколько букв или символов содержит слово или слова с пробелом начиная с левой стороны. Если нам нужно только слово «молоко», то в нем, с учетом пробела 7 букв, поэтому, ставим цифру 7. См. рис 32.
Вот и обрезалось наше наименование только в нужное нам слово, см. рис 33.
Теперь остается только «протянуть» вниз, и все значения с первыми 7-ю символами с левой стороны, будут в нашей таблице., см рис 34.
По аналогии, можно пользоваться функцией ПРАВСИМВ. Здесь все тоже самое, только символы оставляет с правой стороны. Эту функцию часто применяют на числовых значениях, когда код имеет дополнительные обозначения или отделяется, например точкой.
Заключение
Я отдельно сделал статью, как в excel вести учет и планирование товарных запасов. Ели интересно, статью можно почитать здесь.
Чтобы не утяжелять прочтение, разделю материал на две части. В следующей части пойдет речь о том, как в excel обработать большой объем данных с помощью функции СЦЕПИТЬ, построения графиков и диаграмм. Как автоматически подсветить значения верхнего или нижнего порога, и как седлать пароль на страницу или всю книгу в excel, и так далее.
Надеюсь материал был полезным, всего Вам хорошего. Успехов!
#Руководства
- 13 май 2022
-
0
Как систематизировать тысячи строк и преобразовать их в наглядный отчёт за несколько минут? Разбираемся на примере с квартальными продажами автосалона
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
Сводная таблица — инструмент для анализа данных в Excel. Она собирает информацию из обычных таблиц, обрабатывает её, группирует в блоки, проводит необходимые вычисления и показывает итог в виде наглядного отчёта. При этом все параметры этого отчёта пользователь может настроить под себя и свои потребности.
Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».
Сводные таблицы удобно применять, когда нужно сформировать отчёт на основе большого объёма информации. Они суммируют значения, расположенные не по порядку, группируют данные из разных участков исходной таблицы в одном месте и сами проводят дополнительные расчёты.
Вид сводной таблицы можно настраивать под себя самостоятельно парой кликов мыши — менять расположение строк и столбцов, фильтровать итоги и переносить блоки отчёта с одного места в другое для лучшей наглядности.
Разберём на примере. Представьте небольшой автосалон, в котором работают три менеджера по продажам. В течение квартала данные об их продажах собирались в обычную таблицу: модель автомобиля, его характеристики, цена, дата продажи и ФИО продавца.
Скриншот: Skillbox Media
В конце квартала планируется выдача премий. Нужно проанализировать, кто принёс больше прибыли салону. Для этого нужно сгруппировать все проданные автомобили под каждым менеджером, рассчитать суммы продаж и определить итоговый процент продаж за квартал.
Разберёмся пошагово, как это сделать с помощью сводной таблицы.
Создаём сводную таблицу
Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:
- у каждого столбца исходной таблицы есть заголовок;
- в каждом столбце применяется только один формат — текст, число, дата;
- нет пустых ячеек и строк.
Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».
Скриншот: Skillbox Media
Появляется диалоговое окно. В нём нужно заполнить два значения:
- диапазон исходной таблицы, чтобы сводная могла забрать оттуда все данные;
- лист, куда она перенесёт эти данные для дальнейшей обработки.
В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».
Скриншот: Skillbox Media
Excel создал новый лист. Для удобства можно сразу переименовать его.
Слева на листе расположена область, где появится сводная таблица после настроек. Справа — панель «Поля сводной таблицы», в которые мы будем эти настройки вносить. В следующем шаге разберёмся, как пользоваться этой панелью.
Скриншот: Skillbox Media
Настраиваем сводную таблицу и получаем результат
В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».
Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:
- «Значения» — проводит вычисления на основе выбранных данных из исходной таблицы и относит результаты в сводную таблицу. По умолчанию Excel суммирует выбранные данные, но можно выбрать другие действия. Например, рассчитать среднее, показать минимум или максимум, перемножить.
Если данные выбранного поля в числовом формате, программа просуммирует их значения (например, рассчитает общую стоимость проданных автомобилей). Если формат данных текстовый — программа покажет количество ячеек (например, определит количество проданных авто).
- «Строки» и «Столбцы» — отвечают за визуальное расположение полей в сводной таблице. Если выбрать строки, то поля разместятся построчно. Если выбрать столбцы — поля разместятся по столбцам.
- «Фильтры» — отвечают за фильтрацию итоговых данных в сводной таблице. После построения сводной таблицы панель фильтров появляется отдельно от неё. В ней можно выбрать, какие данные нужно показать в сводной таблице, а какие — скрыть. Например, можно показывать продажи только одного из менеджеров или только за выбранный период.
Настроить сводную таблицу можно двумя способами:
- Поставить галочку напротив нужного поля — тогда Excel сам решит, где нужно разместить это значение в сводной таблице, и сразу заберёт его туда.
- Выбрать необходимые для сводной таблицы поля из перечня и перетянуть их в нужную область вручную.
Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.
В случае с нашим примером нужно, чтобы сводная таблица отразила ФИО менеджеров по продаже, проданные автомобили и их цены. Остальные поля — технические характеристики авто и дату продажи — можно будет использовать для фильтрации.
Таблица получится наглядной, если фамилии менеджеров мы расположим построчно. Находим в верхней части панели поле «Продавец», зажимаем его мышкой и перетягиваем в область «Строки».
После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.
Скриншот: Skillbox
Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».
В левую часть листа добавился второй блок. При этом сводная таблица сама сгруппировала все автомобили по менеджерам, которые их продали.
Скриншот: Skillbox Media
Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.
Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».
Скриншот: Skillbox Media
Теперь мы видим, какие автомобили продал каждый менеджер, сколько и по какой цене, — сводная таблица самостоятельно сгруппировала всю эту информацию. Более того, напротив фамилий менеджеров можно посмотреть, сколько всего автомобилей они продали за квартал и сколько денег принесли автосалону.
По такому же принципу можно добавлять другие поля в необходимые области и удалять их оттуда — любой срез информации настроится автоматически. В нашем примере внесённых данных в сводной таблице будет достаточно. Ниже рассмотрим, как настроить фильтры для неё.
Настраиваем фильтры сводной таблицы
Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».
В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.
Скриншот: Skillbox Media
Для примера отфильтруем данные по году выпуска: настроим фильтр так, чтобы сводная таблица показала только проданные авто 2017 года.
В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:
Скриншот: Skillbox Media
В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.
Скриншот: Skillbox Media
Теперь сводная таблица показывает только автомобили 2017 года выпуска, которые менеджеры продали за квартал. Чтобы снова показать таблицу в полном объёме, нужно в том же блоке очистить установленный фильтр.
Скриншот: Skillbox Media
Фильтры можно выбирать и удалять как удобно — в зависимости от того, какую информацию вы хотите увидеть в сводной таблице.
Проводим дополнительные вычисления
Сейчас в нашей сводной таблице все продажи менеджеров отображаются в рублях. Предположим, нам нужно понять, каков процент продаж каждого продавца в общем объёме. Можно рассчитать это вручную, а можно воспользоваться дополнениями сводных таблиц.
Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».
Скриншот: Skillbox
Теперь вместо цен автомобилей в рублях отображаются проценты: какой процент каждый проданный автомобиль составил от общей суммы продаж всего автосалона за квартал. Проценты напротив фамилий менеджеров — их общий процент продаж в этом квартале.
Скриншот: Skillbox Media
Можно свернуть подробности с перечнями автомобилей, кликнув на знак – слева от фамилии менеджера. Тогда таблица станет короче, а данные, за которыми мы шли, — кто из менеджеров поработал лучше в этом квартале, — будут сразу перед глазами.
Скриншот: Skillbox Media
Чтобы снова раскрыть данные об автомобилях — нажимаем +.
Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».
Обновляем данные сводной таблицы
Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.
Скриншот: Skillbox
В сводную таблицу эти данные самостоятельно не добавятся — изменился диапазон исходной таблицы. Поэтому нужно поменять первоначальные параметры.
Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».
Скриншот: Skillbox Media
Кнопка переносит нас на лист исходной таблицы, где нужно выбрать новый диапазон. Добавляем в него две новые строки и жмём «ОК».
Скриншот: Skillbox Media
После этого данные в сводной таблице меняются автоматически: у менеджера Трегубова М. вместо восьми продаж становится десять.
Скриншот: Skillbox Media
Когда в исходной таблице нужно изменить информацию в рамках текущего диапазона, данные в сводной таблице автоматически не изменятся. Нужно будет обновить их вручную.
Например, поменяем цены двух автомобилей в таблице с продажами.
Скриншот: Skillbox Media
Чтобы данные сводной таблицы тоже обновились, переходим на её лист и во вкладке «Анализ сводной таблицы» нажимаем кнопку «Обновить».
Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».
Скриншот: Skillbox Media
Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:
Скриншот: Skillbox Media
Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше
4.5.1. Группировка данных
При обработке и
анализе таблиц большой размерности
часто требуется выделять данные,
относящиеся к одной группе и это можно
реализовать средствами Excel
путем создания так называемой структуры.
Данная структура позволяет показывать
и скрывать строки дополнительных
сведений о месячном объеме продаж, что
особенно важно при большом значении
строк, не умещающихся на одном экране.
На рис. 4.23 в верхней таблице строки
разбиты на две группы : 1 и 2 кварталы,
кнопки “+” и “–“ раскрывают и скрывают
группы, а кнопки 1 и 2 – уровни групп. На
рис.4.24 ( нижней таблице) данные по 1-му
кварталу скрыты, а по 2-му кварталу
открыты. Каждая из групп формируется
отдельно. В меню Данные
выберите команду Группа
и структура,
а затем — Создание
структуры.
Выделите строки или столбцы, содержащие
сведения.
Рис.4.23. Группировка
данных
Строки или столбцы
сведений обычно прилегают к строке или
столбцу, содержащему итоговые формулы
или заголовки. Например, для рассматриваемого
примера выделите строки 3- 5 и 7- 9 . В общем
случае необходимо продолжать выделение
и группировку строк или столбцов сведений
и выполнение команды «Группировать»
до тех пор, пока не будут созданы все
необходимые уровни структуры. Данная
структура позволяет показывать и
скрывать строки дополнительных сведений
о месячном объеме продаж. Если итоговые
строки расположены над строками данных
или итоговые столбцы расположены слева
от столбцов данных, измените параметры
расположения.
4.5.2. Сортировка и фильтрация данных
Для многих применений
Excel
таблицы могут содержать большое
количество неупорядоченных по выбранным
критериям данных, представленных в виде
списка. В таком случае эффективным
инструментом для анализа и некоторой
обработки табличных данных в Excel
используются сортировка списков и их
фильтрация. Под списком понимают набор
строк листа, содержащий однородные
данные, например база данных счетов или
набор адресов и телефонов клиентов.
Первая строка в этом списке содержит
заголовки столбцов, список не должен
содержать пустых строк или столбцов.
Сортировка
данных. Лучше
всего, если сортируемый список будет
иметь заголовки столбцов. Сортировка
или упорядочивание списков значительно
облегчает поиск и анализ данных. После
сортировки записи отображаются в
порядке, определенном значениями
столбцов по алфавиту, возрастанию,
убыванию и другим критериям . Наиболее
просто сортировка выделенных ячеек
выполняется при использовании кнопок
сортировки по возрастанию и убыванию
(рис.4.24).
Рис.4.24. Выполнение
сортировки
Для выполнения
сортировки курсор необходимо поместить
в столбце, по которому выполняется
сортировка. Выбрав на панели инструментов
«Сортировка от А до Я», данный список
будет отсортирован по первому столбцу,
то есть по полю Турфирма. Для выполнения
сортировки по нескольким столбцам можно
также использовать данные кнопки
сортировки, но более эффективным
инструментом сортировки является
команда Данные
> Сортировка.
На
рабочем
листе появляется диалоговое окно
Сортировка,
в
котором
можно задать три ключа сортировки. Это
позволит расставить строки по выбранному
критерию сортировки (возрастание или
убывание) в столбце В, а внутри групп
других столбцов с одинаковыми значениями
столбца В расставит по выбранному
критерию в столбце С (рис.4.24). Таким
образом, выполнение сортировки в общем
случае включает следующие этапы:
-
необходимо выделить
ячейку в списке, который требуется
отсортировать; -
в меню Данные
выберите команду Сортировка; -
выбор столбцов
сортировки в полях Сортировать
по и Затем
по. -
выберите остальные
параметры сортировки и нажмите кнопку
OK.
Фильтрация
данных. Фильтр
— это быстрый и легкий способ поиска
подмножества данных и работы с ними в
списке. В отфильтрованном списке
отображаются только строки,
отвечающие условиям.
Под условием понимается ограничение,
заданное для отбора записей, включаемых
в результирующий набор записей запроса
или фильтра.
для столбца. В Excel доступны две команды
для фильтрации списков:
-
автофильтр,
включая фильтр по выделенному, для
простых условий отбора; -
расширенный
фильтр для
более сложных условий отбора.
В отличие от
сортировки, фильтр не меняет порядок
записей в списке. При фильтрации временно
скрываются строки, которые не требуется
отображать. Строки, отобранные при
фильтрации в Excel, можно редактировать,
форматировать, создавать на их основе
диаграммы, выводить их на печать, не
изменяя порядок строк и не перемещая
их. На рис.4.25 представлены результаты
работы группы агентов туристической
фирмы, осуществляющей распространение
туров в различных городах страны. Для
анализа их работы по продаже туров
необходимо периодически проводить
экспресс-анализ их деятельности за
первое полугодие.
Очевидно, что даже
такую простую таблицу не просто
анализировать и делать прогнозы, не
говоря о таблицах, содержащих сотни и
тысячи записей.
Для использования
Автофильтра
необходимо выделить какую-нибудь
ячейку исходной
таблицы и выполнить команду
Данные-Фильтр-Автофильтр.
Рис.4.25. Результаты
работы турагентов
В каждой ячейке
верхней строки появляются кнопки со
стрелками, отражающими наличие
какого-нибудь списка (рис. 4.26).
Рис.4.26. Таблица
исходных данных
Если щелкнуть по
кнопке-стрелке в столбце «город», то
появляется список со всеми введенными
в таблицу городами и можно выбрать
интересующий город. Выберем город СПб
для анализа деятельности в нем агентов
(рис.4.27).
Рис.4.27. Выполнение
фильтрации
Excel
оставил в отфильтрованной таблице
только результаты работы агентов данного
города. Аналогично можно проанализировать
данные по другим городам таблицы или
сделать фильтрацию по агентам и т.п.
Полученные таблицы можно отформатировать,
распечатать или передать по электронной
почте.. Для отмены режима фильтрации
нужно выполнить команду Данные—Фильтр—
Отразить Все.
Для отфильтрованных
таблиц можно выполнить обработку данных:
вычислить суммы, произведения, так, как
будто никаких скрытых строк в таблице
нет. Если поместить курсор в свободную
ячейку какого-нибудь столбца, выделить
нужные ячейки и нажать на кнопку
автосуммирования «сигму», то выполнится
специальная функция Промежуточные
Итоги. Данная
функция, например будет суммировать
только строки, которые видны, а остальные
невидимые пропускает. Если фильтр
изменится, то изменятся и промежуточные
результаты. Поля, по которым установлен
фильтр, отображаются со значком воронки.
Если подвести указатель мыши к такой
воронке, то будет показано условие
фильтрации.
Соседние файлы в папке Информатика
- #
- #
- #
- #
- #
- #
Чтобы попробовать и работать данные из таблицы excel вам нужно будет знать, с какими типами документов вы можете работать шаблон расчета Microsoft.
Именно поэтому мы подготовили эту статью, чтобы вы могли найти вся информация, связанная с пошаговым выполнением работы с информацией который вы вводите в шаблон расчета.
Если вы хотите узнать больше, вам придется продолжить чтение. Мы объясним, что такое формула и как ее лучше всего использовать. Наконец, мы также расскажем вам, что такое движения по ячейкам и как вы должны использовать представления.
Типы документов, с которыми можно работать в Microsoft Excel, следующие:
- Связать файлы символический.
- База данных типа ДБФ 3 и ДБФ 4.
- Документы обмен данными.
- Текстовые файлы в ДВА.
- Документы в формате XPS.
- Форматы файлов Майкрософт Эксель.
- Электронная таблица ОткрытьДокумент.
- Картинки.
- Битовая карта.
- Объект рисования Офис в формате ЭДС.
- Ссылка владельца, ОбъектСсылка, гифки и файлы JPG.
- PDF.
- Текст отображать.
- Текст Юникод.
- Значения с разделителями запятыми, табуляцией или пробелами.
Узнайте шаг за шагом, как обрабатывать данные в электронной таблице Excel
Далее мы объясним шаг за шагом, что вы должны сделать для обработки данных в электронной таблице Excel:
Формулы
Добавление данных
Когда вам нужно добавить данные, вы можете сделать это напрямую через функцию АВТОСУМАДля этого вам нужно будет выбрать все ячейки, которые вы хотите добавить, а затем нажать Enter.
Другой способ использования клавиатуры — ввести знак знак равно а также затем введите первый слова-формулы, Microsoft Excel поможет вам с автозаполнением и он покажет вам результаты в окне, которое вы найдете рядом с ячейкой, которую вы пишете. Чтобы получить доступ к нужной функции, вы можете сделать это с помощью стрелок направления, а затем нажать Enter.
Движение по клеткам
Перемещение по ячейкам позволит вам узнать, какие формулы есть у каждой из них и каков их синтаксис. чтобы понять происхождение данных, которые вы хотите проанализировать. Для этого вам придется использовать дату адреса клавиатуры или использовать мышь, чтобы выбрать нужную ячейку. Если вы хотите увидеть, какая формула у ячейки можно нажать клавишу F2 либо делай Двойной щелчок чтобы узнать происхождение каждого поля в той же электронной таблице.
Вставка фигур
Если вы хотите, чтобы графики, связанные с блочными чертежами, звездами, блок-схемами или линиями, были включены в вашу электронную таблицу, вы можете использовать функцию Формы Что он предлагает Майкрософт Эксель. Для этого вам нужно будет перейти на вкладку Вставлятьзатем нажмите на Иллюстрации и, наконец, выберите Формы. Далее вам нужно будет перейти в то место, где вы хотите, чтобы график появился, и нажать Войти.
Если хочешь, Можно изменить контур и линии заливки, для этого вам нужно будет щелкнуть правой кнопкой мыши по фигуре, а затем выбрать опцию Формат формы. Чтобы на экране появилась панель конфигурации, в которой можно выбрать заливку и тип линий. Вы также сможете добавлять и редактировать слова, если выберете вкладку Параметры текста. Это позволит вам создавать текстовые эффекты или включать блоки в графическую фигуру.
Если у вас есть несколько рисунков в шаблоне, вы можете упорядочить их, перемещая их вперед или назад, щелкнув правой кнопкой мыши и выбрав опцию Вывести на фронт или же Отправить вниз. Если вы хотите сгруппировать их, чтобы переместить их все вместе, чтобы вы могли копировать и изменять их размер, вам придется работать с опцией Группа как только вы выбрали их все, удерживая нажатой клавишу Ctrl нажал.
Автозаполнение ячеек
Инструмент автозаполнения ячеек позволяет быстро и точно найти нужную формулу. Для этого вам нужно будет вставитьв ячейке, в которой вы хотите Excel Я бросил тебе результат, знак знак равно а затем начать напишите название формулы.
Вы увидите всплывающее окно который будет расположен рядом с ячейкой, которую вы вводите. В этом поле вы найдете все предложения, которые программа дает вам, чтобы вы могли выбрать правильную формулу. Когда вы найдете тот, который вам нужен вам нужно будет выбрать его мышью или стрелкой направления на клавиатуре.
Добавить или удалить строки или столбцы
Если вы хотите добавить строки или столбцы, вы можете сделать это полностью или включить или удалить определенные ячейки. То есть вам не нужно будет включать всю горизонтальную или вертикальную линию.
В первом случае вам нужно будет выбрать столбец или строку, а затем щелкнуть правой кнопкой мыши. Далее вам нужно будет выбрать вариант Вставить (или Удалить, в зависимости от обстоятельств) чтобы закончить свою цель. Если вам нужно добавить или удалить определенные ячейки, вам нужно будет выбрать их и щелкнуть правой кнопкой мыши, чтобы выбрать Вставить или удалить.
Далее вам предстоит выбрать:
- Переместить ячейки к Правильно.
- Переместить ячейки к вниз.
- Вставлять все одно строка.
- Вставлять все одно столбец.
Вы должны знать, что это движение может повлиять на формат из остальной части таблицы данных, особенно если вы объединили ячейки.
Скрыть строки или столбцы
Если у вас есть электронная таблица со вспомогательными вычислениями, позволяющими лучше анализировать информацию, вы сможете скрыть эти операции подкрепления, чтобы они не отображались на распечатке листа или не создавали визуальное усложнение на вашем экране. . Вы можете полностью скрыть как ячейки, так и строки.
В этом случае вы должны выбрать строку, которую хотите перестать видеть нажав на заголовок строки или столбца. Тогда вам придется сделать щелкните правой кнопкой мыши и выберите Скрыть или показать (в зависимости от вашего случая). Быстрый способ выбрать то, что вы хотите, — это сделать это с помощью клавиатуры. Для столбца вам придется нажимать клавиши вместе Ctrl и пробел. Если вы хотите выбрать строку, вы будете использовать ярлык пробел + шифт.
Обтекание текстом в ячейках
Если вам нужно разместить текст в ячейке или их комбинацию, вам придется использовать функцию «Подогнать текст».. Это позволит вам найти все слова, которые вы написали внутри ячейки, но вы должны иметь в виду, что высота ячейки изменится.
Для выполнения этой задачи вам нужно будет сделать щелкните правой кнопкой мыши в ячейке, которую вы хотите отсортировать, а затем выберите вариант Формат ячейки. Откроется новое окно, в котором вам нужно будет выбрать вкладку Выравнивание и включить в поле Текстовое управлениеинструмент Изменить текст. Вы также можете выполнить эти шаги если вы перейдете на вкладку «Главная» и выполните поиск в группе Выравниваниефункция Изменить текст. Это очень удобно, когда вы работаете с таблицами и вам нужно упорядочить заголовки столбцов или строк.
Текстовый формат
Он текстовый формат в Microsoft Excel относится к типу данных, которые будут отображаться в клетки и с которым вы будете работать как если бы это были слова, а не как даты, числа или валюты. Это имеет то преимущество, что Excel он не будет добавлять или усреднять эти данные среди других функций.
Также, можно использовать логические формулы которые помогут вам найти разные переменные. Чтобы выполнить преобразование клетка к формату текст вам придется выбрать его и сделать щелкните правой кнопкой мыши. Затем вам нужно будет выбрать вариант Формат ячейки и выберите вкладку Число. Когда вы находитесь в этом месте, выполните поиск в списке Категория функция Текст.
Абсолютные ссылки
А абсолютная ссылка всегда относится к одной и той же ячейке (или их диапазон) и позволяет формуле оставаться неизменной, когда вы включаете эту ячейку. Таким образом, вы можете переместите ячейку абсолютной ссылки в любую сторону шаблона расчета и вы будете уверены, что результат не изменится.
Чтобы сделать абсолютную ссылку, вам нужно будет создайте формулу с соответствующим синтаксисом а затем введите перед столбцом и именем строки символ $. Например, если у вас есть ячейка D4вы можете сделать это как абсолютную ссылку $ Д $ 4. Это очень полезно, когда вы хотите, чтобы созданная вами формула охватывала другие ячейки, но если вы перетащите ее с помощью мыши Майкрософт Эксель воспримет его как мобильную ссылку. По этой причине рекомендуется использовать абсолютную ссылку.
Просмотры
Формат ячейки
Если вы выберете клетка и щелкните правой кнопкой мыши, вы можете выбрать вариант Формат ячейки. Таким образом, вы получите доступ к различным вкладкам, которые помогут вам настроить данные, которые вы будете включать в ячейку.
Вкладки:
- Число. Вы сможете модифицировать тип формата, который будет иметь ячейкапоэтому вы можете выбрать, хотите ли вы общий формат, число, валюту, учет и дату, среди других параметров.
- Выравнивание. Вы можете использовать эту вкладку для выравнивания текста по горизонтали и вертикали. Вы также можете использовать для настройки текст к одному клеткаобъединить их или уменьшить фразу до размера область. Наконец, у вас будет возможность выбрать адрес текстто есть если вы хотите сделать это слева направо или наоборот.
- Эта вкладка позволит вам настроить шрифт, который будет отображаться в шаблоне расчета.. Вы сможете выбрать тип шрифта, стиль, размер и цвет.
- Если хочешь, выбранная вами ячейка или диапазон могут иметь границу или контуртак что вы можете выбрать его цвет и толщину.
- Как и в случае с крайвы также сможете включить определенный цвет и эффект заливки в ячейки.
- Этот раздел используется для блокировать ячейки и скрывать формулы, чтобы другие пользователи не могли изменять к тому же.