Порада №1 з книги XL - швидкі способи копіювання формули вниз у стовпець.
У вас є тисячі рядків даних. Ви додали нову формулу у верхньому рядку набору даних, щось подібне до цього =PROPER(A2&" "&B2), як показано нижче. Тепер потрібно скопіювати формулу в усі рядки набору даних.
Багато людей схоплять ручку заповнення і почнуть тягнути вниз. Але коли ви тягнете вниз, Excel починає працювати все швидше і швидше. Починаючи з Excel 2010, в останньому рядку даних є пауза в 200 мікросекунд. 200 мікросекунд достатньо, щоб ви помітили паузу, але недостатньо, щоб ви зреагували і відпустили кнопку миші. Перш ніж ви це зрозумієте, ви перетягнули ручку заповнення занадто далеко.
Рішення полягає в тому, щоб двічі клацнути маркер заповнення! Перейдіть до того самого місця, де ви починаєте перетягувати ручку заповнення. Покажчик миші змінюється на чорний знак плюс. Подвійне клацання.
Excel переглядає навколишні дані, знаходить останній рядок із даними сьогодні та копіює формулу до останнього рядка набору даних.
Раніше порожні клітинки в стовпці ліворуч спричиняли припинення роботи трюку “подвійне клацання ручки заповнення” безпосередньо перед порожньою клітинкою. Але, як ви можете бачити нижче, імена на зразок Мадонна, Шер або Пеле не створюватимуть проблем. За умови, що існує принаймні діагональний шлях (наприклад, через B76-A77-B78), Excel знайде справжнє дно набору даних.
На моїх живих семінарах Power Excel цей фокус завжди викликає задишку у половини людей у залі. Це мій фокус номер один, який економить час.
Альтернативи подвійному клацанню ручки заповнення
Цей фокус є приголомшливим трюком, якщо все, що ви зробили до цього моменту, - це перетягнути маркер заповнення до нижньої частини набору даних. Але є ще швидші способи вирішення цієї проблеми:
- Використовуйте таблиці. Якщо вибрати одну клітинку в A1: B112 і натиснути Ctrl + T, Excel відформатує діапазон як таблицю. Отримавши таблицю, просто введіть формулу в С2. Коли ви натиснете Enter, він буде скопійований внизу.
- Використовуйте складну, але ефективну комбінацію клавіш. Цей ярлик вимагає, щоб у сусідньому стовпці не було порожніх комірок. Поки це здається складним для пояснення, люди, які розповідають мені про цей ярлик, можуть зробити все це за мить.
Ось кроки:
- З нещодавно введеної формули в C2 натисніть клавішу зі стрілкою вліво, щоб перейти до комірки B2.
- Натисніть Ctrl + стрілка вниз, щоб перейти до останнього рядка з даними. У цьому випадку B112.
-
Натисніть клавішу зі стрілкою вправо, щоб повернутися до нижньої частини переважно порожнього стовпця C.
Використання комбінацій клавіш - З комірки C112 натисніть Ctrl + Shift + Стрілка вгору. Це вибирає всі порожні клітинки поруч із вашими даними, а також формулу в С2.
-
Натисніть Ctrl + D, щоб заповнити формулу в C2 до всіх порожніх місць у виділенні. (Зверніть увагу, що Ctrl + R заповнює праворуч, що може бути корисно в інших ситуаціях.)
Ctrl + D, щоб заповнити
Як альтернативу, ви можете отримати ті самі результати, натиснувши Ctrl + C перед кроком 1 і замінивши крок 5 на Ctrl + V.
Завдяки наступним людям, які запропонували цю пораду: Д. Кармайкл, Шеллі Фішель, Доун Гілберт, @Knutsford_admi, Френсіс Логан, Майкл Ортенберг, Джон Патерсон, Майк Салліван та Грег Ламберт Лейн запропонували Ctrl + D. Білл Хазлетт, автор Excel для Клас математики, зазначив Ctrl + R. Саме мультфільм Ема Демарко, розміщений у Twitter, був натхненням для найму ілюстраторів для цієї книги.
Переглянути відео
- Порада №1 з книги XL - швидкі способи копіювання формули вниз у стовпець. Також у цьому відео:
- Ctrl + стрілка вниз, щоб перейти до кінця блоку даних
- Ctrl + Shift + Стрілка вгору, щоб вибрати верхню частину блоку даних
- Ctrl + D, щоб заповнити формулу з першого рядка виділення вниз
- Двічі клацніть маркер заповнення
- Функція PROPER
- & символ для об'єднання
- Функція ROMAN
- АРАБСЬКА функція
Стенограма відео
Спонсор подкасту - "MrExcel XL" - книга, що містить понад 40 підказок Excel, а також мультфільми Excel, твіти та жарти.
Дізнайтеся Excel з подкасту, серія 1974 - Швидке копіювання формули!
Гаразд, ну, привіт, у цій книзі багато ілюстрацій, і мене зачепила ця ілюстрація Емілі ДеМарко. Емілі була на якомусь семінарі Excel, де вони показували, як поєднувати дані за допомогою символу конкатенації, і вона прорисовувала це, і хтось із присутніх сказав: "Але це не те саме, що = CONCATENATE?" Для тих, хто викладає класи, так. Але хто може писати conCATenate? Я це бачив, думав, це було весело.
Отже, питання сьогодні полягає в тому, як ми беремо цю формулу, я не буду говорити про цю формулу, але як ми беремо цю формулу або будь-яку формулу і копіюємо її до сотень або тисяч рядків даних? І перед тим, як дістатися до мого шляху, спочатку слід визнати метод Олімпійських ігор Excel! Є люди, які можуть виконати ці п’ять кроків, наприклад, одним натисканням клавіші, це займає менше секунди, і якщо це ви, вітаю, вам честь, якщо коли-небудь відбудеться Олімпіада в Excel, я призначаю вас до складу команди. Отже, якщо ви є комбінацією клавіш, стрілка вліво рухається до B2, CTRL + стрілка вниз, стрілка вправо, CTRL + Shift, стрілка вгору виділяє вгору, а в моєму улюбленому - ту, яку я ніколи не бачив використання раніше, було CTRL + D. CTRL + D бере формулу у верхньому рядку виділення та копіює її вниз.Отже, якщо ви любите комбінації клавіш, неодмінно, це шлях.
Але спосіб вирішення цієї проблеми більшістю людей полягає в тому, що вони створюють ту першу формулу там, у рядку 2, і хапають ручку заповнення, і вони починають перетягуватись, вправо, і вона починає рухатися все швидше і швидше, швидше і швидше. Перш ніж ви знаєте, це сотні рядків нижче нижньої частини даних. І якщо ви помітите, вони насправді гальмують саме там. Вони недостатньо довгі для того, щоб ви насправді реагували, а достатньо довгі, щоб побачити уповільнення. Отже, якщо ви виявите, що занадто далеко спускаєтесь і повертаєтеся назад, я називаю це танцювальним ручком, і вам сподобається цей наступний фокус.
Отже, ось швидший спосіб, все починається точно так само, там є білий хрестик, є курсор миші, коли ви наближаєтесь, він змінюється на чорний +, прямо там, коли у вас є чорний +, подвійний -клацніть! Excel дивиться ліворуч, розраховує, скільки рядків даних ми маємо сьогодні, і копіює формулу внизу! Будь то 112 рядків у цьому випадку або 1,1 мільйона рядків, в будь-якому випадку, це потрапляє до кінця.
Гаразд, для тих з вас, хто вже знав подвійне клацання маркер заповнення, ви це знаєте давно: коли подвійне клацання маркер заповнення не працює? Правильно, це коли хтось не має прізвища! У стовпці ліворуч є порожній простір. Замість Шер, покладемо Принца. Отож, як правило, двічі клацніть маркер заповнення, він виглядає ліворуч, і він копіюється лише до рядка 8. Праворуч, через цю заготовку в рядку 9. І звичайно, це ніколи не на першому екрані, це завжди сотні рядків вниз у дані. Ну, раніше це була проблема, але, мовчки, без будь-якого повідомлення в Excel 2010, вони тепер переглядають усі стовпці ліворуч, щоб з’ясувати, наскільки потрібно скопіювати формулу. Тож його не ошукає порожня клітинка в стовпці ліворуч!Це, безумовно, моя улюблена порада у всьому Excel. Коли я роблю потужні семінари Excel, ці живі семінари, і я добираюся до цього приблизно 45 хвилин в день, я майже завжди задихаюся від кількох людей до кімнаті, коли я двічі клацну ручку заповнення.
Зараз тут просто цікавий факт, книга "MrExcel XL", ви знаєте, я виріс у ті часи, ще в 70-ті роки, коли виходили всілякі фільми, які виходили з римськими цифрами, так що частина "Хрещений батько" була перший у. У сучасній історії "Назад у майбутнє III", "Зоряні війни IV", "Роккі V", "Зоряний шлях VI", навіть Final Fantasy VII на 7. Ви знаєте, і це смішно, що Excel насправді має функцію ROMAN! Гаразд, ви можете попросити РИМАН РЯДУ, і це дасть вам римські цифри. Що насправді дивно, тому що в Excel 2013, якщо ви введете римську цифру XL і попросите АРАБИК, він перетворить цю РИМСКУ цифру назад у звичайну цифру. Отже, коли я закінчив свою 39-у книгу, і я знав, що виходить 40-та книга, просто як кінчик капелюха назад до всіх цих фільмів, де використовуються римські цифри, я знав це,це просто збирався бути "MrExcel XL", тому що це моя 40-та книга, хоча я туди поставив слоган, = ROMAN (40), щоб люди зрозуміли жарт!
Добре, лише короткий підсумок усіх речей, про які ми говорили в цьому епізоді: І ЦЕ - лише підказка №1 з книги. Ну, привіт, дякую всім, хто писав, пропонуючи цю пораду. Це всі люди, які написали мені твіт. Той, кого я тут навіть не висвітлював, Білл Хазлетт, зазначає, що CTRL + R схожий на CTRL + D, якщо вибрати одну клітинку та перетягнути праворуч або вибрати праворуч, CTRL + R скопіює праворуч. Вперед і натисніть цю інформаційну кнопку у верхньому правому куті прямо зараз, щоб прочитати про те, як ви можете придбати цю книгу.
Дякуємо, що завітали, ми побачимось наступного разу для чергової трансляції від!
