скачать книгу бесплатно
МГНОВЕННОЕ ЗАПОЛНЕНИЕ
Файл с примером: Мгновенное заполнение. xlsx
Мгновенное заполнение (Flash Fill) – один из самых простых и полезных инструментов Excel. Появилось мгновенное заполнение в Excel 2013. Этот инструмент позволяет преобразовать данные или извлекать какой-то фрагмент: достаточно задать один-два образца того, что надо получить из исходного столбца (или нескольких столбцов), – и мгновенное заполнение заполнит весь столбец значениями исходя из заданного вами паттерна (шаблона).
Вот примеры задач, которые может решить мгновенное заполнение:
• извлечь из текста фрагмент (например, имя из Ф. И. О.);
• переделать текст в другой вид, в том числе дополняя теми символами, которых нет в исходном тексте (например, вместо Ф. И. О. – инициалы с точками плюс полная фамилия);
• получить текст из нескольких столбцов (например, если у вас есть столбцы с датой и фамилией, а вам надо склеить это в одну фразу «День рождения такого-то человека такого-то числа такого-то месяца»);
• поменять регистр текста.
Вводим одно или несколько значений, которые хотим получить на основе исходных данных, и либо ждем, что Excel сам предложит заполнить, и нажимаем Enter (или щелкаем мышкой на серые значения), либо нажимаем Ctrl + E (^ + E).
В следующем случае ввели одно значение, и этого хватит в данном и большинстве случаев, чтобы Excel считал паттерн и заполнил весь столбец. Достаточно нажать Ctrl + E в ячейке B2:
После нажатия Ctrl + E данные будут заполнены.
В следующем случае ввели два значения, начали вводить третье – Excel предложил заполнить столбец (серые значения). Остается нажать Enter или щелкнуть по любой из этих ячеек, чтобы столбец заполнился.
Также можно вызвать мгновенное заполнение на ленте.
Есть несколько нюансов.
1. Мгновенное заполнение анализирует всю строку, все смежные столбцы (то есть нужно вводить данные в любом соседнем столбце с данными, без перерыва в виде пустого столбца). И это значит, что можно обрабатывать (извлекать и объединять) данные из нескольких столбцов. В этом примере мы формируем фразу, в которой есть переменная часть – название книги и продажи.
После нажатия Ctrl + E все строки будут корректно заполнены по этому образцу:
2. Не во всех случаях Excel сможет распознать ваш шаблон. Иногда приходится задавать несколько примеров (не один, а два или три). Иногда вообще не получается применить мгновенное заполнение.
3. Не всегда удастся извлечь данные в каждой строке корректно. Например, если мы захотим извлечь вес корма в следующей таблице…
…то в некоторых местах будут извлечены лишние данные:
А где-то будет вес в граммах – это не ошибка мгновенного заполнения, но нужно это учесть и конвертировать его в килограммы (это можно сделать с помощью функции ПРЕОБР / CONVERT, о которой мы поговорим ниже, или банального деления, которого хватит в случае с килограммами и граммами; ну а чтобы определять, нужно ли каждое конкретное число преобразовывать из граммов, пригодится функция ЕСЛИ / IF, о которой тоже поговорим ниже).
Это пример не идеальных данных, но именно такими они обычно и бывают в жизни. Мгновенное заполнение не исправит все по взмаху волшебной палочки, но способно выполнить большую часть «грязной» работы, которую можно завершить уже вручную.
Рабочие листы Excel
Рабочая книга (файл) Excel состоит из листов – как минимум один лист в книге точно должен быть.
Бывают рабочие листы (состоящие из ячеек, как правило, в основном в Excel и Google Таблицах пользователи работают с ними) и листы диаграмм (на таких листах есть только одна диаграмма и никаких ячеек; самый простой способ создать такую диаграмму – нажать F11 (Fn + F11), выделив данные для ее построения). «Обычная» и привычная нам диаграмма называется внедренной и размещается поверх ячеек на обычном листе, такую можно создать с помощью сочетания клавиш Alt + F1 (Fn +
+ F11) или вкладки «Вставка» (Insert) на ленте.
Ярлыки листов находятся в нижней части окна.
Можно щелкать на ярлык листа, чтобы перейти к нему, можно также использовать сочетания клавиш Ctrl + PgUp (Fn + ^ + ?) и Ctrl + PgDn (Fn + ^ + ?) для перехода от листа к листу.
У любого ярлыка можно поменять цвет – эта опция доступна в контекстном меню, которое открывается при щелчке правой кнопкой мыши по ярлыку листа. Там же будет пункт для переименования листа, хотя для этого достаточно и дважды щелкнуть по текущему названию и ввести новое.
Здесь же можно удалить лист – это удастся сделать, если он является не единственным видимым во всей книге (видимым, то есть не скрытым; скрыть лист можно здесь же в контекстном меню).
В Google Таблицах все выглядит немного по-другому, но логика та же: ярлыки листов (щелчок по кнопке со стрелкой или правой кнопкой мыши по ярлыку в целом открывает контекстное меню), кнопка для создания нового. Список листов открывается кнопкой слева от ярлыков.
УДАЛЕНИЕ И ВСТАВКА СТРОК И СТОЛБЦОВ
Чтобы вставить столбцы или строки, щелкните правой кнопкой мыши и нажмите «Вставить» (Insert).
Также на панели на вкладке «Главная» (Home) есть выпадающий список для вставки новых строк/столбцов.
В Excel вставка и удаление строк/столбцов не изменит их общее количество на листе (напомним, 1 048 576 строк и 16 384 столбца, начиная с Excel 2007).
Данные будут просто смещаться. Ну а если у вас заполнены все строки (или столбцы), появится сообщение об ошибке при попытке вставить новую строку:
Microsoft Excel не удается вставить новые ячейки, так как это приведет к сдвигу непустых ячеек за пределы листа. Непустые ячейки могут казаться пустыми, но содержат пустые значения, некоторые параметры форматирования или формулы. Удалите столько строк или столбцов, чтобы освободившегося места хватило для вставки, а затем повторите попытку.
В Google Таблицах можно менять количество строк и столбцов на листе. Формальное ограничение – в количестве ячеек, их не может быть более 10 миллионов (за несколько лет это ограничение поменялось – сначала с 2 до 5, а затем до 10 миллионов, – так что вполне вероятно, что увеличится и далее в будущем). А сколько при этом окажется листов, строк и столбцов на них – неважно.
Добавлять новые строки и столбцы можно через контекстное меню, как в Excel, а строки также внизу листа: там есть поле, в котором можно ввести число добавляемых строк.
Удаление строк/столбцов
Чтобы удалить строки или столбцы, можно пользоваться тем же контекстным меню (выделить столбцы или строки и щелкнуть правой кнопкой мыши).
Также можно удалять строки с помощью выпадающего списка «Удалить» (Delete) на вкладке «Главная»:
И есть сочетание клавиш Ctrl + – (
+ –). Если у вас уже выделены строки или столбцы, они сразу будут удалены, если же выделены ячейки, то появится диалоговое окно, потому что в таком случае не очевидно, что именно нужно удалять, – вам нужно будет выбрать.
Удаление строк с пустыми значениями. Инструмент «Найти и выделить»
Бывает необходимость удаления не всех подряд строк, а только тех, в которых пусто в определенном столбце. Представим, что нам нужно удалить целиком строки, в которых нет сумм в следующем столбце:
Сначала выделим эти пустые ячейки. Для этого выделим все ячейки в столбце «Сумма» и обратимся к инструменту «Найти и выделить» ? «Выделить группу ячеек» (Find and Select ? Go To Special).
В появившемся диалоговом окне выбираем, какую группу ячеек нужно выделить (в нашем случае это пустые ячейки – Blanks).
После нажатия ОК мы будем видеть такую картину:
Останется нажать Ctrl + – (
+ –) и выбрать команду «строку» (Entire row).
КАК УДАЛИТЬ ПУСТЫЕ СТРОКИ / СТРОКИ С ПУСТЫМИ ЯЧЕЙКАМИ В EXCEL
Выделяем диапазон, в котором нужно удалить пустые ячейки.
Для этого понадобится инструмент «Найти и выделить» (на ленте на вкладке «Главная»). Выбираем там «Выделить группу ячеек», а в появившемся диалоговом окне – «Пустые ячейки» (Find & Select ? Go To Special ? Blanks).
После этого остается нажать Ctrl + – (это удаление ячеек/строк/столбцов). И выбрать «строку».
P. S. Если у вас пустые ячейки только в одном столбце и нужно удалить строки с такими ячейками, то выделите один столбец, а не всю таблицу. Далее алгоритм такой же.
АВТОПОДБОР ШИРИНЫ СТОЛБЦА
Если вам нужно отрегулировать ширину столбцов, чтобы все данные в них помещались, то достаточно просто выделить их и щелкнуть дважды на границе любого столбца – ширина будет подобрана автоматически так, что ширина каждого столбца станет соответствовать данным в нем (чтобы все отображалось, но не более того).
Выделим все столбцы и щелкнем дважды по границе любого из них.
ОДНОВРЕМЕННОЕ РЕДАКТИРОВАНИЕ НЕСКОЛЬКИХ ЛИСТОВ
В Excel можно вводить данные в ячейки и форматировать их сразу на нескольких листах. Для этого достаточно сгруппировать листы. Это можно сделать так:
– если нужно выделить несколько отдельных листов – просто зажмите клавишу Ctrl и щелкайте на их ярлыки;
– если нужно выделить группу листов, идущих подряд, щелкните на ярлык первого, зажмите Shift и щелкните на ярлык последнего – будут сгруппированы все листы «от и до».
Теперь действия будут применяться ко всем выделенным листам. Например, если вы введете текст в ячейку A1, он будет введен в эту ячейку на каждом из листов.
После того как закончите работу с несколькими листами, не забудьте их разгруппировать: иначе можно случайно переписать нужные данные, если вы забудете, что у вас включена группировка листов, и будете работать, считая, что вводите данные только на один лист.
Разгруппировать листы можно в контекстном меню – достаточно щелкнуть правой кнопкой по любому из ярлыков и выбрать команду «Разгруппировать листы» (Ungroup Sheets).
Что такое формат и значение в Excel и Google Таблицах
У ячейки в Excel (и в Google Таблицах) есть значение и формат.
Значение – это то, что в ячейке содержится или возвращается формулой, введенной в эту ячейку: текст, числа, даты, время, формулы. И то, что может использоваться для последующих вычислений с помощью формул в других ячейках.
Формат – то, как это выглядит в таблице, как ячейка оформлена.
Если сравнить ячейку с контейнером, в котором что-то хранится, то можно сказать, что значение – это то, что в контейнере лежит, а форматирование – то, как он выглядит снаружи.
Контейнер может быть прозрачным, и мы будем видеть в точности то, что там хранится (форматирование не искажает содержимого ячейки), может быть выкрашен в определенный цвет, может быть совсем непрозрачным (с помощью форматирования можно скрыть данные, и тогда внешне ячейка будет выглядеть пустой, мы не будем знать, что в ней, пока не заглянем в строку формул).
Форматирование бывает стилевым и числовым. Стилевое форматирование – это параметры шрифта, выравнивание, цвет заливки, границы ячейки. Числовой формат – то, как отображаются данные: есть ли у чисел знаки после запятой и разделители разрядов, код валюты, есть ли в датах день недели, год, месяц, число и как они выглядят, отображается ли знак «минус» у отрицательных чисел и так далее.
Отметим два важных аспекта, связанных со значениями и форматами.
• Формат (то, как значение представлено) и само значение могут выглядеть по-разному. В ячейке может отображаться одно число, а на самом деле быть другое. Или отображаться целое число, а быть с дробной частью. Значение без всякого форматирования (или формула, если в ячейке она) всегда отображается в строке формул.
При вычислениях будут использоваться точные значения – те, что можно увидеть в строке формул. Форматирование – это внешнее представление данных, которые хранятся (или вычисляются с помощью формул) в ячейках.
• Когда мы очищаем ячейку, мы можем очистить только значения, только форматы или все вместе. Это же касается и копирования-вставки.
В ячейке или диапазоне можно удалить только данные (клавиша Delete), а можно только форматы или все вместе (кнопка «Очистить» / Clear на ленте во вкладке «Главная»).
В Google Таблицах можно очистить форматирование (но только стилевое, то есть заливку, выравнивание и прочие визуальные аспекты, но не форматирование числа) сочетанием клавиш Ctrl + \.
Вы ознакомились с фрагментом книги.
Для бесплатного чтения открыта только часть текста.
Приобретайте полный текст книги у нашего партнера:
