Здравствуйте, уважаемые читатели. Сегодня расскажу Вам несколько важных мелочей, которые сделают Вашу работу в Экселе более продуктивной. Это не базовые навыки, а скорее, методы использования программы, делающие Вашу жизнь проще. Поехали!
Используйте встроенные функции Excel везде, где это возможно
Не старайтесь изобрести велосипед. Многие популярные операции уже реализованы в функции, нужно только найти нужную, применить и взять на вооружение. Это экономит много времени, ведь написание одной функции занимает меньше времени, чем сборка и проверка сложной формулы.
Например, функция АГРЕГАТ, выполнит за Вас множество операций: суммирование, поиск минимального, максимального значений, стандартное отклонение, дисперсию и др.
Упускаем закрывающую скобку
Если вы пишете простую формулу с одной функцией, например =СУММ(А1:А10 – не тратьте время на закрывающую скобку в конце, сразу жмите Enter. Программа поставит скобку за вас!
Однако, это не работает в более сложных расчетах, когда используется больше, чем одна пара скобок. Здесь Эксель сообщит об ошибке и предложит вариант исправления. Вряд ли, это будет удобно.
Используйте автозаполнение
Одно из лучших решений разработчиков – применение автозаполнения. Когда Вы пишете формулу, Excel отслеживает набранный текст и сопоставляет его с именами функций, именованных констант. Когда появляется возможное совпадение, всплывает подсказка с вариантами функций.
Например, я введу в ячейке =СУ. Посмотрите на изображении, какая подсказка появилась на экране.
Если в списке есть нужное название – выбираем его двойным кликом мышью. Или выберите стрелками клавиатуры, нажмите Tab. Это бывает удобно, особенно, когда в списке только один вариант.
Автозаполнение формул в «официальных» таблицах Эксель
Одним из преимуществ инструмента «Таблица» можно назвать автоматическое заполнение формул. Взгляните на изображение с примером. Здесь у нас простая таблица. В последнем столбце которой мы производим некоторые расчеты.
Я введу формулу лишь в первую строку таблицы, для остальных строк программа проставит формулы автоматически. Это экономит Ваше время и снижает вероятность ошибки.
Быстрый ввод множества аргументов
Этот метод будет полезен, когда ваши данные хранятся не одним массивом, а разбросаны по листу. Посмотрите на следующий пример.
Чтобы просуммировать эти данные с помощью функции СУММ, нужно выделять каждый диапазон отдельно, разделяя их точкой с запятой. А можно поступить проще. Зажмите Ctrl и выделяйте мышью все нужные данные. Вот как это получится:
Нажимаем Enter и получаем правильный результат.
Используем подсказку аргументов
Большинство функций требуют ввода аргументов. Если Вы недостаточно хорошо знаете эту функцию, Эксель подскажет, что именно нужно сейчас ввести. При наборе, на экране появляется маленькая подсказка, в которой описано, какие требуются аргументы.
Кликая по элементам подсказки, можно переместиться к соответствующему аргументу. А клик по имени функции – откроет подсказку.
Если подсказка перекрывает нужные Вам ячейки – наведите на нее мышью, найдите положение, когда курсор становится четырехнаправленной стрелкой и перетащите подсказку в другое удобное место.
Отображаем аргументы в формуле
Можно вставить названия аргументов прямо внутрь функций, а потом лишь заменять их реальными данными. Это может быть полезно, когда Вы плохо знакомы с ней. Некоторым моим ученикам это удобно.
Чтобы вставить, напишите название функции и нажмите Ctrl+Shift+A (в английской раскладке) или Ctrl+Shift+Ф (в русской). Вот, как это будет выглядеть:
Как сохранить формулу до окончания набора
Когда Вы пишете длинные, сложные формулы, может понадобиться временно остановить написание, до того, как формула окончена. Если нажать Enter, вероятно Excel не сможет вычислить значение и потребует исправить формулу перед сохранением. Если же нажать Esc, ничего из проделанного ранее не сохранится. Как же поступить, чтобы ненадолго прерваться?
Нужно заставить программу думать, что Ваша формула – это текст. Предложу два способа:
- Удалить знак равенства вначале строки
- Поставить перед знаком равенства какой-нибудь видимый символ. Например, одинарную кавычку, или знак решетки (#)
Теперь формула стала текстом и ее можно временно сохранить. Для продолжения работы над ней – верните знак равенства или удалите добавленный символ.
Пишем сложные формулы пошагово
Новички считают, что писать длинные формулы очень сложно. Но если у Вас есть алгоритм, ничего сложного в этом нет. Просто пишите формулу постепенно, реализовывая всё, что требуется. Приведу небольшой пример. У нас есть таблица с ФИО сотрудников. Все они записаны в одинаковом виде в одой ячейке: фамилия, имя и отчество. Стоит задача получить лишь фамилии, без имени и отчества.
Действуем по алгоритму, решаем задачу сначала для первой записи:
- Чтобы отделить фамилию Смирнов, нам потребуется функция ПСТР с такими аргументами:
- Текст – ссылка на ФИО
- Начальная позиция – равна 1, т.к. фамилия записана сначала строки
- Количество символов – 7, т.к. в фамилии «Смирнов» именно столько букв
- Пока все хорошо, фамилия получена. Но для следующей строки эта формула не подойдет, там в фамилии уже 10 символов, а не 7. И нет смысла для каждой записи вручную проставлять длину. После фамилии всегда идет пробел, давайте найдем его положение, чтобы автоматически определять длину фамилии. Вместо «7» пишем функцию НАЙТИ с такими аргументами:
- Искомый текст – « » (пробел в кавычках)
- Просматриваемый текст – ссылка на ФИО
- Начальная позиция – 1
- Функция НАЙТИ вернет позицию первого пробела – 8. Вычитаем единицу и получаем длину фамилии. Вот как сейчас выглядит формула. И она уже находит фамилии любой длины без ручного ввода!
Мы получили формулу с применением вложенной функции, но сделали это просто и логично.
Объединение строк
Когда нужно вывести результат не просто значением, а добавить какой-то сопровождающий текст, нам не обойтись без операции конкатенации (объединения) строк. Например, в предыдущем примере нужно решить обратную задачу: из фамилии, имени и отчества получить ФИО одной строкой.
Можно пойти двумя путями:
- Использовать функцию СЦЕПИТЬ, где через точку с запятой перечислить все элементы будущего текста. В нашем случае это будет выглядеть так:
- Использовать оператор конкатенации «&», без использования функций. Вот пример, как его использовать:
Применяя эти способы, можно получить хороший автоматически изменяющийся контент.
Переносы строк в формулах
Когда Ваша формула достаточно длинная, разобраться в ней очень сложно. Сейчас, когда она только написана, Вам все ясно. А через некоторое время, чтобы внести в нее правки, придется хорошо поломать голову. Вот пример формулы, которую сложно понять сразу:
Но Вы можете поставить переносы строк прямо в формуле. Это сделает ее более читаемой. Вот как выглядит формула из предыдущего примера, но с переносами:
Гораздо лучше, не прада ли? Чтобы сделать так же – установите курсор в то место формулы, где будет перенос и нажмите Alt+Enter. Это работает только в английской раскладке. В русской – не получится. Но это не большая проблема.
Автосумма
Многие используют в расчетах суммирование функцией СУММ. Но немногие знают, что Эксель может написать эту формулу за Вас. Просто установите курсор под колонкой для суммирования (или справа от строки), нажмите Alt+Enter. Программа сама вставит функцию и задаст аргументы.
Учтите, это работает только для непрерывных массивов. Если в одной из ячеек не будет значения – автосумма сработает неправильно.
Чтобы суммировать сразу по нескольким столбцам (или строкам) – выделите ячейки в ряду под этими столбцами и нажмите Alt+Enter. Ну или строки справа. Автосумма будет выполнена для каждой из строк/столбцов отдельно.
Чтобы просуммировать и строки и столбцы сразу, выделите весь массив, а так же, пустую строку снизу и столбец справа. Нажмите Alt+Enter.
Пожалуй, для одной статьи достаточно. В следующем посте будет продолжение – новая порция важных, интересных повседневных мелочей, используя которые Вы ускорите работу. Заходите и читайте!