У нас в офисе сотрудникам частенько приходится работать с MS Excel. Программа стоит не самой свежей версии, но всё сказанное применимо к любым версиям, так как это совсем основы. Все примеры, которые будут в этой заметке я встретил у сотрудников на этой неделе (я им показывал как это делать, некоторым не первый раз). Отмечу, что это были люди, которые при устройстве на работу заполняли в резюме, что владеют MS Word и MS Excel. Я считаю, что именно уровень владения Excel очень часто является ответом на вопрос "почему два человека, которые делают одну и ту же работу одинаковый рабочий день, выполняют настолько разное количество работы".
Расскажу поучительный пример из работы нашего офиса и приступим к изучению приёмов. У нас были три человека, которые 90% рабочего времени обрабатывали прайсы поставщиков и готовили данные, выгруженные из нашей программы (1С), всё это в Экселе. Все они с небольшой разницей были взяты на работу сразу после института. Через полгода одну девушку я заменил тремя макросами, написанными за 2 дня. Потом через месяц подошёл ко мне один из двух оставшихся и говорит, что он проанализировал, что и его работу можно заменить макросами, а он хочет заниматься сайтом организации, потому что там как раз появилась вакансия, а он не против добавки к зарплате. В итоге он остался один из троих и занимался пол дня макросами в Excel (из-за постоянных изменений и пожеланий один раз на всегда сделать всё равно не получилось), а вторые полдня программированием сайта. Третьего парня тоже сократили, а его ЗП прибавили оставшемуся. Сейчас он уехал из нашего города в Москву, где работает специалистом по RPA, а всё началось со знания, что всегда можно делать ещё меньше повторений операций в Excel. Но в этой заметке пока самое простое, но не самое очевидное
1. Исчезающий лидирующий ноль или +
Частенько артикулы товаров являются числами определённой длины (например 5 символов). То есть артикул 00123 вполне нормальное явление. И вот Вам надо сбросить по электронной почте поставщику, что вы заказываете артикул 00123, а на стороне поставщика робот говорит, что такого артикула нет, потому что в экселевском файле с заказом написано 123.
Это происходит потому, что если Вы напишите в ячейке Excel любое число, начинающиеся с нуля, то лидирующий нолик исчезнет. Не помогает выставить текстовый формат. Например если выставить в формате ячейки "Все форматы" со значением "00000", то всё равно содержимое этой ячейки не будет 5 символами - ведь на принимающей стороне на него будет смотреть не человек, а робот.
Помогает вставить символ ' . Это символ, который на кнопке клавиатуры вместе с буквой Э. То есть набираете '00123 и жмёте Enter. Появится зелёный треугольничек с подсказкой, что число записано как текст. Возможна ситуация, когда при копировании столбца целиком в другой документ исчезает форматирование как текст, тогда после вставки сразу (пока вставленное обведена рамкой) пролистайте в самый верх и там будет ромбик где можно выбрать что число сохранено как текст. Используйте то же самое, чтобы написать +перед числом.
Этот штришок после сохранения некоторыми программами не распознаётся как надо (но очень редко). Если у вас случился такой случай, то тут поможет комбинация методов. Как их применять если не понятно, то после прочтения других приёмов уже будете знать и вернётесь сюда. Сначала получим нужный внешний вид формулой вроде =ПРАВСИМВ(СЦЕПИТЬ("00000";D14);5). Затем скопируем и специальной вставкой вставим значения. Появится сообщение с вариантом вставки как текст, но штришка не будет!
2. Никогда не пользуйтесь калькулятором при работе с данными из Excel
Например у Вас есть отчёт в Excel, сохранённый с прошлого месяца и цифра в 1С и Вам быстренько надо вычислить 1,5% премии от общей суммы, обещанной агенту. Я испытываю испанский стыд (это когда стыдно за другого) при виде бухгалтера, который считает это 5 минут на калькуляторе, записывает промежуточные результаты на листочек, а потом ещё раз перепроверяет на калькуляторе.
В таком случае используя сочетания кнопок Ctrl-C и Ctrl-V (копировать и вставить) копируем цифру из 1С в любую свободную ячейку на листе Excel. Затем в Excel в любую свободную клетку пишем = и мышкой выделяем нужную цифру, затем пишем + и мышкой выделяем следующую цифру. Затем можно в новой ячейке нажать = и выделить мышкой полученный промежуточный результат, нажать * и напечатать 0,015. При нажатии Enter на экране будет искомое число. И никаких ошибок, ведь ручной ввод чисел минимален.
Чтобы не портить старый отчёт, просто закрываем его без сохранения или очищаем клетки с нашими вычислениями. Кстати в формуле можно использовать скобки и всевозможные функции. Чтобы ознакомится с самыми популярными функциями достаточно кликнуть на кнопочке fx, которая видна скриншоте в самом верху картинки перед строкой формулы.
3. Автозаполнение столбца
Допустим у Вас есть список, строки в котором следует пронумеровать и добавить столбец с вычисленным значением (розничной ценой по цене закупки, вычислить разницу двух столбцов итд).
Для начала Вставим столбец для номеров строк перед таблицей. Для того чтобы выделить весь столбец целиком надо нажать на его заголовке. Для первого столбца это буква А (как на картинке выше) или 1 (при формате R1C1, этот формат ставится для текущего и всех новых документов в параметрах Excel - иногда надо его поменять, поэтому написал). Выделить несколько столбцов подряд можно с нажатой кнопкой Shift, а не подряд с нажатой кнопкой Ctrl. Аналогично и со строками (вся таблица выделится при нажатии треугольничка в левом верхнем углу). Вернёмся к вставке чистого столбца. Если нажать правой кнопкой мыши на заголовке столбца, то там есть два разных пункта "Вставить":
Первый (с обычным значком вставки) вставляет ранее скопированный столбец, а второй (который выделен на картинке) - чистый столбец.
Напишем с первой строки чистого столбца "1", нажмём Enter, а потом мышкой выделим эту ячейку с единичкой, она обвелась жирной рамочкой. Видите, в правом нижнем углу этой рамочки есть точка (она хорошо видна на первой картинке в этой статье). Сделайте двойной клик мышкой на этой точке. Нумерация продлилась до конца списка. На самом деле такое продление работает до первой пустой ячейки в соседнем столбце. Чтобы продлить через пустую строку надо зажать мышью и "протянуть" до куда надо. В половине случаев нумерация не заполняется автоматически, а копирует 1 во все строки, тогда в самом низу листа (до конца списка прокручивать не надо) есть значок как на втором слайде. Надо нажать на нём и поменять "Копировать значение ячейки" на "Заполнить".
Теперь заполним в новый столбец сколько машине лет. Для этого справа от последнего столбца в первой строке напишем формулу "=2020-D2" (D - это столбец с годом выпуска, а D1 - ячейка заголовка). Нажмём Enter и "протянем" за точку до конца списка (потому что встречаются пробелы). Кстати от пробелов можно для удобства избавится - это сэкономит время (как это сделать я чуть дальше напишу в этой статье). После того как автозаполнение произошло, данные в столбце обведены рамочкой. Прежде чем кликнуть куда-то прочитайте следующий приём, скорее всего это надо сделать именно сейчас, чтобы заново не выделять столбец.
4. Вставить вычисленное в формуле значение
Если скопировать формулу и вставить её куда-то, то вставится именно её формулировка (знак = и далее). Но чаще всего надо вставить вычисленное значение или сохранить файл без формул, а со значениями (особенно если их смотрит робот). В свежих версиях Экселя появится предложение вставить значения. В более старых надо нажать в месте куда хотите вставить правой кнопкой мыши и выбрать пункт "Специальная вставка". Там выбрать "Значения".
5. Найти и заменить пустоту или минусы, дроби, пробелы, разделители разрядов.
Часто пустая ячейка мешает выполнять операции, вроде той, что описана в третьем приёме. Ещё если приглядитесь к тому примеру, то увидите, что нет года в некоторых строках и для них будет возраст машины 2020 лет. Решение простое: надо заменить пустоту на пробел. Но пустота встречается иногда не только в пустых ячейках. Поэтому вот алгоритм действий:
1. Выделяете мышкой в ячейку любом месте таблицы
2. Нажимаете Ctrl+A - это выделит всю таблицу со значениями
3. Нажимаете Ctrl+H - это откроет окно "Найти и заменить"
4. В найти ничего не пишете, в заменить печатаете " " (пробел)
5. Раскрываете "Параметры" (кликнуть на этой кнопке)
6. Ставите галочку "Ячейка целиком" - то есть если ячейка целиком равна пустоте, только тогда менять. Вспомните это, когда придётся поменять ячейки где только 0 среди столбцов заполненных цифрами.
7. Жмите кнопку заменить всё.
Если в артикулах надо убрать точки, пробелы, знаки дроби и другие спецсимволы, то можно выделить столбец артикула и последовательно заменять на пустоту эти знаки без галочки "Ячейка целиком".
Отдельная боль это убрать разделители разрядов, так как на клавиатуре нет такого символа. Для этого найдите в файле любое число с разделителем разрядов, скопируйте этот разделитель (он только выглядит как пробел), затем вставьте его в поле "Найти" и замените на пустоту!
________________________
Если статья была полезной рекомендую продолжить чтение: 4 приёма в MS Excel, которые значительно сокращают время работы. Повторяющихся приёмов нет.
Ещё раньше писал заметку как можно считать в Excel графики платежей по кредитам.
