Нещодавно я відвідував кілька семінарів Excel Power. Коли ви приїжджаєте в кімнату 150 бухгалтерів на смішний ранок підказок та підказок Excel, я завжди дізнаюся щось нове. Хтось із глядачів може поділитися крутим трюком з рештою кімнати.
У сьогоднішньому епізоді у мене є збірка нових прийомів. Це насправді трюки, які кращі або відрізняються від еквівалентного методу, про який йдеться в книзі. Вони неодмінно будуть при наступному перегляді книги.
До речі, я хотів би приїхати до вашого міста, щоб провести семінар Power Excel. Якщо ви належите до такої професійної групи, як місцевий відділ Інституту управлінських бухгалтерів, Інституту внутрішніх аудиторів, AICPA, МСП тощо, чому б не запропонувати, щоб вони забронювали мене на один із наступних днів CPE? Надішліть голову програми для розділів на цю сторінку, щоб отримати докладнішу інформацію.
Знайдіть різницю між двома датами
Я зазвичай говорити про методи використання =YEAR(), =MONTH(), =DAY()функції, але є крута стара функція ховається в Excel.
Функція DATEDIF залишилася від Lotus. Хоча довідка Excel не говорить про цю функцію, це чудовий спосіб знайти різницю між двома датами.
Синтаксис є =DATEDIF(EarlierDate,LaterDate,Code)
Ось дійсні значення, які ви можете використовувати для коду.
- Y - покаже вам кількість повних років між двома датами.
- YM - покаже вам кількість повних місяців, за винятком років, між двома датами.
- Доктор медицини - покаже вам кількість повних днів, за винятком повних місяців, між двома датами.
- М - підкаже вам кількість повних місяців. Наприклад, я живий 495 місяців
- D - підкаже кількість днів. Наприклад, я живий 15 115 днів. Це тривіальне використання, оскільки ви можете просто відняти одну дату від іншої та відформатувати як число для копіювання цього коду.
Корисними кодами є перші три коди. На шоу я продемонстрував цей аркуш. Ідентичні формули у стовпцях D, E та F обчислюють DATEDIF у роках, місяцях та днях.
Формула у стовпці G об'єднує це, щоб створити текст із тривалістю часу в роках, місяцях та днях.
Ви можете об'єднати це в єдину формулу. Якщо комірка A2 містить дату приєднання, використовуйте таку формулу в B2:
=DATEDIF($A2,TODAY(),"Y")&" years, "&DATEDIF($A2,TODAY(),"YM")&" months & "&DATEDIF($A2,TODAY(),"MD")&" days"
Сума видимих клітин
Додайте функцію SUM під базу даних, а потім використовуйте AutoFilter для фільтрування бази даних. Excel буде надокучливо включати в суму приховані рядки!
Натомість виконайте такі дії:
- Використовуйте Дані - Фільтр - Автофільтр, щоб додати спадні меню Автофільтр.
- Виберіть Фільтр для одного поля
- Перейдіть до порожньої комірки під одним із числових стовпців бази даних.
- Клацніть грецьку літеру E (Sigma) на стандартній панелі інструментів. Замість введення
=SUM()Excel буде вводити=SUBTOTAL()та використовувати коди, щоб запобігти включенню прихованих рядків.
Комбінація клавіш для повторення останньої команди
Клавіша F4 повторить останню виконану вами команду.
Наприклад, виберіть клітинку та натисніть піктограму B, щоб зробити комірку жирною.
Тепер виберіть іншу комірку та натисніть F4. Excel зробить цю клітинку жирною.
F4 запам'ятає останню команду. Отже, ви можете зробити комірку курсивом, а потім використовувати F4, щоб зробити багато комірок курсивом.
Попередньо виберіть діапазон комірок, які потрібно ввести
У книзі я покажу вам, як користуватися Інструментами - Параметри - Редагувати - Перемістити виділення після введення напряму - Право, щоб змусити Excel рухатися вправо при натисканні клавіші Enter. Це добре, коли потрібно вводити дані, що йдуть через рядок.
Це особливо корисно, якщо ви вводите цифри на цифровій клавіатурі. Хитрість дозволяє ввести 123 Enter і потрапити в наступну комірку. Тримаючи руки на цифровій клавіатурі, ви можете швидше вводити цифри.
Хтось запропонував вдосконалення цієї техніки. Попередньо виберіть діапазон, куди ви будете вводити дані. Перевага полягає в тому, що коли ви дійдете до останнього стовпця та натисніть Enter, Excel перейде на початок наступного рядка.
На зображенні нижче натискання клавіші Enter перемістить вас до комірки B6.
Ctrl + перетягніть маркер заповнення
Я вже багато разів показував трюк Fill Handle на шоу. Введіть понеділок в A1. Якщо вибрати клітинку А1, у правому нижньому куті комірки буде квадратна крапка. Ця точка - ручка заповнення. Клацніть маркер заповнення та перетягніть вниз або вправо. 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 замінить звичайну поведінку (збільшуючи дату) і дасть вам однакову дату у всіх клітинках.
