Эта статья не содержит повторов со статьёй 5 приёмов в MS Excel, которые часто требуются в реальной работе, так что можете взять на заметку прочитать и её, если ещё не сделали этого. Также хочу отметить, что эти приёмы работают во всех версиях MS Excel.
1. Самый быстрый способ выделить ненужные столбцы (быстрые клавиши)
В одном из приёмов в предыдущей статье я просто говорил выделить столбик и предлагал для этого кликнуть мышкой на номере нужного столбца. В реальной работе часто надо выделить несколько столбцов, которые не всегда идут подряд, бывают очень узкими и выделять их мышкой не очень удобно.
Наверняка многие знают о том что если кликнуть на две ячейки (или столбца) с зажатой кнопкой Shift, то выделится всё, что между ними. А если с зажатой кнопкой Ctrl, то добавится конкретно то на чём нажали.
Это знание уже упрощает задачу, но для работы со столбцами можно не целиться точно в шапочку. Достаточно выделить любую ячейку столбца и нажать Crtl+Space (он же Пробел). Тогда выделится весь столбец (Shift+Space аналогично выделит всю строку).
Теперь приведу пример, в котором объединим знания. Допустим у нас есть таблица, в которой надо удалить 2,3,5,6,7 столбцы. Для этого:
1. Выделим любую ячейку в 2 столбце.
2. Нажмём Shift+Стрелочка_вправо. Выделиться соседняя ячейка в третьем столбце.
3. Нажмём Ctrl+Space. Выделятся целиком столбцы 2 и 3.
4. Нажмём Ctrl (и с нажатым) выделим любую ячейку в 5 столбце. В дополнение к выделенным столбцам выделится выбранная ячейка.
5. Нажмём Shift+Стрелочка_вправо+Стрелочка_вправо. Станут выделены два столбца (2,3) целиком и три ячейки из 5,6,7 столбцов.
6. Нажмём Ctrl+Space. Выделятся целиком все не нужные нам столбцы.
7. Нажмём Ячейки->Удалить столбцы или ПКМ (Правая кнопка мыши) на любом из номеров выделенных столбцов и там выбрать пункт "Удалить".
2. Повторить порядок частых действий (Макросы)
К сожалению, быстрых клавиш для удаления столбца в Excel нет (все сочетания клавиш можно посмотреть здесь). И в отличии от Word назначить их самому нельзя. Эту функцию приберегли для быстрого запуска макросов. Но это означает, что мы можем сами записать макрос для этого действия и нажать быструю комбинацию кнопок для его запуска.
Макрос - это любая последовательность действий, которую можно запустить в любой момент. Это очень полезно, когда приходится делать одинаковые действия над несколькими файлами или повторяющиеся действия в одном и том же файле.
Например в 10 разных файлах или листах надо удалить все столбцы кроме первых 5, переименовать подписи столбцов и добавить столбец, в котором количество будет умножено на цену. Для этого достаточно включить запись макроса, сделать эти действия над первым файлом, остановить запуск макроса. Затем открыть остальные файлы и просто нажать выполнение этого макроса в каждом из них.
Чтобы запустить запуск макроса в зависимости от версии Excel надо нажать значок записи макроса в левом нижнем углу или зайти Вид->Макросы->Запись макроса или Разработчик->Запись макроса (если нигде нет, а в ленте нет вкладки "разработчик", то надо включить её отображение).
Сразу можно задать комбинацию клавиш, для быстрого запуска макроса но это не обязательно. Тут же надо выбрать где будет храниться макрос (в этой книге, в личной или на сервере). В простом варианте я рекомендую использовать личную книгу (чтобы одни и те же макросы были доступны всем файлам на компьютере), но для редактирования программного кода макроса (VBA) придётся включить её отображение на время редактирования. Как изменять макрос на VBA можно разобраться и не программисту, в сети много примеров и готовых скриптов, но это не очень просто, если вы уже забыли информатику. Если хочется изучить, но не знаете с чего начать, то с этого.
После начала записи макроса надо выполнить все действия, которые планируется повторять и нажать "остановить запись макроса".
Перед первым выполнением записанного макроса (в нашем примере после открытия второго файла), я обычно жму кнопку "Сохранить", так как действия макроса нельзя отменить. Затем жму "Макросы" (ALT+F8) и там выбираю на макросе не "Выполнить", а "Войти", затем пошагово выполняю макрос, просматривая действия на каждом шаге в новом файле. Затем вношу изменения, если что-то не так (например, часто запись макроса вместо "выбрать всё" указывает точный диапазон таблицы, которая была при записи). Скрипт макроса, удаляющего столбцы для нашего примера состоит из одной строки (действия):
Selection.Delete Shift:=xlToLeft
Если мы знаем (например нашли в интернете) макрос, то можно не записывая макрос, а сразу жать ALT+F8 написать название (содержащее буквы) и вставить скрипт. Иногда этот подход полезен для типичных задач, таких как "оставить только цифры в столбце", которые не знаешь как сделать в записи макроса, но точно кто-то делал до тебя.
3. Исправить автоматические изменения (удалить гиперссылки, длинные числа,
В предыдущей статье я рассказывал как сохранить лидирующий 0 или + после написания или вставки, поэтому повторяться не буду и рассмотрим другие встречающиеся довольно часто случаи.
Бывает копируешь-вставляешь что-то похожее на сайт или ссылку и умный Excel выделяет это синим и подчёркивает, создав гиперссылку. Достаточно нажать CTRL+Z (отменить последнее действие), отменится не сама вставка, а создание гиперссылки (если создание гиперссылки всегда только мешает, то можно отключить эту функцию в параметрах).
А что делать если Вам прислали файл, в котором изменения уже сохранены?
Чтобы удалить гиперссылки (в Excel 2010 и свежее) можно воспользоваться пунктом контекстного меню "Удалить гиперссылки", а вот в более ранних версиях надо самому скопировать весь диапазон, а потом вставить на то же место с помощью специальной вставки (с параметром "Значения").
Бывает часто, что присылают списки Штрих-кодов или артикулов, где установлен формат столбца текстовый. Но внезапно оказывается, что длинные числа там записаны в экспоненциальном формате. Изменение формата ячеек не помогает, так как содержимое уже записано в текстовом формате. Вам повезло, если задача стоит найти совпадения или сцепить по артикулу, тогда хотя бы можно восстановить первые 6 цифр и общую длину, например формулой =ЛЕВСИМВ(RC[-1];(2+ДЛСТР(RC[-1])-ПОИСК("+";RC[-1])))*СТЕПЕНЬ(10;ПРАВСИМВ(RC[-1];ДЛСТР(RC[-1])-ПОИСК("+";RC[-1]))), остальное к сожалению потеряно безвозвратно и это Ꙍ.
4. Если копируется то, что спрятано под фильтром
Бывает когда есть большой прайс деталей (упорядочен по полю Артикул) и надо скопировать на новый лист только те, у которых производитель Bosch. Для этого ставим фильтр по производителю и отбираем только Bosch.
В таком случае часто копируется всё подряд, а не только отобранные артикулы нужного бренда. Это легко понять по тому что пунктирная линия вокруг выделенных частей не появляется. Происходит это потому что в Экселе есть ограничение на количество разрывов в выделении. Соответственно, чтобы выделить только нужное - необходимо уменьшить число разрывов в фильтре.
Проще всего до того как включать фильтр упорядочить по столбцу производитель. Затем поставить фильтр, а потом скопировать отобранное (теперь выделится только то что нужно). После вставки можно вернуть упорядочивание по столбцу Артикул, если оно было необходимым.
Иногда бывает что копируется то, чего не видно, но фильтр при этом не включен. Такое бывает из-за того, что высота этих не видных столбцов равна 0. Это можно заметить по нумерации строк с пропусками. В таком случае надо выделить все строки (нажав треугольник в левом верхнем углу таблицы) и нажать правой кнопкой мыши на любом номере строки. Затем с помощью соответствующего пункта контекстного меню выставить высоту строки не меньше 10.
