Недавно я провел несколько семинаров по Excel Power. Когда вы собираете 150 бухгалтеров в комнате, чтобы весело провести утро за советами и приемами Excel, я всегда узнаю что-то новое. Кто-то из зрителей может поделиться классным трюком с остальной частью комнаты.
В сегодняшнем выпуске у меня есть набор новых трюков. На самом деле это приемы, которые лучше или отличаются от аналогичных методов, обсуждаемых в книге. Они обязательно будут в следующей редакции книги.
Кстати, я бы с удовольствием приехал в ваш город на семинар по Power Excel. Если вы принадлежите к профессиональной группе, такой как местное отделение Института управленческих бухгалтеров, Института внутренних аудиторов, AICPA, SME и т. Д., Почему бы не предложить им забронировать меня на один из предстоящих дней CPE? Пошлите на эту страницу руководителя программы вашего отделения для получения подробной информации.
Найди разницу между двумя свиданиями
Я обычно говорить о методах использования =YEAR(), =MONTH(), =DAY()функции, но есть крутая старая функция скрывается в Excel.
Функция РАЗНДАТ оставлена от Lotus. Хотя справка Excel не говорит об этой функции, это отличный способ найти разницу между двумя датами.
Синтаксис: =DATEDIF(EarlierDate,LaterDate,Code)
Вот допустимые значения, которые вы можете использовать для кода.
- Y - сообщит вам количество полных лет между двумя датами.
- YM - сообщит вам количество полных месяцев, исключая годы, между двумя датами.
- MD - сообщит вам количество полных дней, исключая полные месяцы, между двумя датами.
- M - сообщит вам количество полных месяцев. Например, я живу 495 месяцев
- D - подскажет количество дней. Например, я живу 15 115 дней. Это тривиальное использование, поскольку вы можете просто вычесть одну дату из другой и отформатировать ее как число, чтобы дублировать этот код.
Полезные коды - это первые три кода. На выставке я продемонстрировал этот рабочий лист. Идентичные формулы в столбцах D, E и F вычисляют РАЗНДАТ в годах, месяцах и днях.
Формула в столбце G объединяет их вместе, чтобы создать текст с периодом времени в годах, месяцах и днях.
Вы можете объединить это в единую формулу. Если ячейка A2 содержит дату присоединения, используйте следующую формулу в B2:
=DATEDIF($A2,TODAY(),"Y")&" years, "&DATEDIF($A2,TODAY(),"YM")&" months & "&DATEDIF($A2,TODAY(),"MD")&" days"
Сумма видимых ячеек
Добавьте функцию СУММ под базу данных, а затем используйте автофильтр для фильтрации базы данных. Excel будет досадно включать скрытые строки в сумму!
Вместо этого выполните следующие действия:
- Используйте Data - Filter - AutoFilter, чтобы добавить раскрывающиеся списки AutoFilter.
- Выберите фильтр для одного поля
- Перейдите в пустую ячейку под одним из числовых столбцов в базе данных.
- Щелкните греческую букву E (сигма) на стандартной панели инструментов. Вместо ввода
=SUM()Excel будет вводить=SUBTOTAL()и использовать коды, чтобы предотвратить включение скрытых строк.
Сочетание клавиш для повтора последней команды
Клавиша F4 повторит последнюю выполненную команду.
Например, выберите ячейку и щелкните значок B, чтобы выделить ячейку жирным шрифтом.
Теперь выберите другую ячейку и нажмите F4. Excel выделит эту ячейку жирным шрифтом.
F4 запомнит последнюю команду. Итак, вы можете выделить ячейку курсивом, а затем использовать F4, чтобы сделать курсивом многие ячейки.
Предварительно выберите диапазон ячеек для ввода
В книге я покажу вам, как использовать Инструменты - Параметры - Редактировать - Переместить выделение после ввода направления - Вправо, чтобы заставить Excel перемещаться вправо при нажатии клавиши ввода. Это хорошо, когда вам нужно вводить данные в строке.
Это особенно полезно, если вы вводите числа на цифровой клавиатуре. Уловка позволяет вам набрать 123 Enter и оказаться в следующей ячейке. Держа руки на цифровой клавиатуре, вы можете быстрее вводить числа.
Кто-то предложил усовершенствовать эту технику. Предварительно выберите диапазон, в который вы будете вводить данные. Преимущество состоит в том, что когда вы дойдете до последнего столбца и нажмете Enter, Excel перейдет к началу следующей строки.
На изображении ниже нажатие Enter переместит вас в ячейку B6.
Ctrl + перетащите маркер заливки
Я много раз показывал на шоу трюк с ручкой заполнения. Введите понедельник в A1. Если вы выберете ячейку A1, в правом нижнем углу ячейки появится квадратная точка. Эта точка - ручка заливки. Щелкните маркер заполнения и перетащите его вниз или вправо. Excel заполнит вторник, среду, четверг, пятницу, субботу, воскресенье. Если вы перетащите более 7 ячеек, Excel снова запустится в понедельник.
Excel действительно хорош. Он может автоматически расширять все эти серии:
- Понедельник - вторник, среда, четверг, пятница и т. Д.
- Янв - февраль, март, апрель и т. Д.
- Январь - февраль, март и др.
- Q1 - Q2, Q3, Q4 и т. Д.
- Qtr 1 - Qtr 2, Qtr 3, Qtr 4, Qtr 1 и т. Д.
- 1 период - 2 период, 3 период, 4 период и т. Д.
- 23 октября 2006 г. - 24 октября 2006 г., 25 октября 2006 г. и т. Д.
Поскольку Excel может выполнять ВСЕ эти удивительные серии, чего вы ожидаете, если введете 1 и перетащите маркер заполнения?
Вы могли ожидать, что получите 1, 2, 3,…
Но вы действительно получаете 1, 1, 1, 1, 1,…
В книге говорится о запутанном методе. Введите 1 в A1. Введите 2 в A2. Выберите A1: A2. Перетащите маркер заполнения. Существует лучший способ.
Просто введите 1 в A1. Ctrl + перетащите маркер заливки. Excel заполнит значения 1, 2, 3. Удерживание Ctrl, кажется, отменяет нормальное поведение маркера заполнения.
Кто-то на семинаре сказал, что он хотел бы ввести дату, перетащить дату и заставить Excel сохранять дату без изменений. Если при перетаскивании маркера заполнения удерживать нажатой клавишу Ctrl, Excel переопределит нормальное поведение (увеличение даты) и выдаст вам одинаковую дату во всех ячейках.
