Совет №1 из книги XL - быстрые способы скопировать формулу вниз по столбцу.
У вас есть тысячи строк данных. Вы добавили новую формулу в верхнюю строку набора данных, примерно так =PROPER(A2&" "&B2), как показано ниже. Теперь вам нужно скопировать формулу во все строки вашего набора данных.
Многие люди схватятся за маркер заполнения и начнут тянуть вниз. Но по мере того, как вы перетаскиваете вниз, Excel начинает работать все быстрее и быстрее. Начиная с Excel 2010, в последней строке данных есть 200-микросекундная пауза. 200 микросекунд достаточно, чтобы вы заметили паузу, но не достаточно, чтобы вы отреагировали и отпустили кнопку мыши. Прежде чем вы это узнаете, вы слишком далеко зашли за маркер заполнения.
Решение состоит в том, чтобы дважды щелкнуть маркер заполнения! Перейдите в то же место, где вы начали перетаскивать маркер заливки. Указатель мыши изменится на черный знак плюса. Двойной клик.
Excel просматривает окружающие данные, находит последнюю строку с данными за сегодня и копирует формулу в последнюю строку набора данных.
В прошлом пустые ячейки в столбце слева приводили к прекращению работы трюка «двойной щелчок по метке заполнения» непосредственно перед пустой ячейкой. Но, как вы можете видеть ниже, такие имена, как Мадонна, Шер или Пеле, не вызовут проблем. При наличии хотя бы диагонального пути (например, через B76-A77-B78) Excel найдет истинное основание набора данных.
На моих семинарах по Power Excel этот трюк всегда вызывает вздох у половины присутствующих. Это моя уловка номер один для экономии времени.
Альтернативы двойному щелчку ручки заливки
Этот трюк - отличный трюк, если все, что вы сделали до этого момента, - это перетащить маркер заполнения в нижнюю часть набора данных. Но есть еще более быстрые способы решить эту проблему:
- Используйте таблицы. Если вы выберете одну ячейку в A1: B112 и нажмете Ctrl + T, Excel отформатирует диапазон как таблицу. Если у вас есть таблица, просто введите формулу в C2. Когда вы нажмете Enter, он будет скопирован в самый низ.
- Используйте сложное, но эффективное сочетание клавиш. Этот ярлык требует, чтобы в соседнем столбце не было пустых ячеек. Хотя это кажется сложным для объяснения, люди, которые рассказывают мне об этом ярлыке, могут сделать все в мгновение ока.
Вот шаги:
- Из вновь введенной формулы в C2 нажмите клавишу со стрелкой влево, чтобы перейти к ячейке B2.
- Нажмите Ctrl + стрелка вниз, чтобы перейти к последней строке с данными. В данном случае B112.
-
Нажмите клавишу со стрелкой вправо, чтобы вернуться к нижней части почти пустого столбца C.
Использование сочетаний клавиш - В ячейке C112 нажмите Ctrl + Shift + стрелка вверх. Это выберет все пустые ячейки рядом с вашими данными, а также формулу в C2.
-
Нажмите Ctrl + D, чтобы заполнить формулу в C2 всеми пробелами в выделенном фрагменте. (Обратите внимание, что Ctrl + R заполняет вправо, что может быть полезно в других ситуациях.)
Ctrl + D для заполнения
В качестве альтернативы вы можете получить те же результаты, нажав Ctrl + C перед шагом 1 и заменив шаг 5 на Ctrl + V.
Благодаря следующим людям, которые предложили этот совет: Д. Кармайкл, Шелли Фишел, Дон Гилберт, @Knutsford_admi, Фрэнсис Логан, Майкл Ортенберг, Джон Патерсон, Майк Салливан и Грег Ламберт Лейн предложили Ctrl + D. Билл Хазлетт, автор Excel для Класс математики, указал Ctrl + R. Именно мультфильм Эма ДеМарко, размещенный в Твиттере, был вдохновением для найма иллюстраторов для этой книги.
Смотреть видео
- Совет №1 из книги XL - быстрые способы скопировать формулу вниз по столбцу. Также в этом видео:
- Ctrl + стрелка вниз для перехода к концу блока данных
- Ctrl + Shift + стрелка вверх для выделения в начало блока данных
- Ctrl + D, чтобы заполнить формулу от первой строки выделения вниз
- Дважды щелкните маркер заполнения
- Функция PROPER
- & символ для конкатенации
- РИМСКАЯ функция
- АРАБСКИЙ (функция)
Стенограмма видео
Подкаст спонсируется "MrExcel XL", книгой с более чем 40 советами по Excel, а также мультфильмами Excel, твитами о коктейлях и шутками.
Изучите Excel из подкаста, выпуск 1974 - Быстрое копирование формул!
Хорошо, ну, во всей этой книге много иллюстраций, и меня зацепила иллюстрация Эмили ДеМарко. Эмили была на каком-то семинаре по Excel, где они показывали, как объединить данные с помощью символа конкатенации, и она рисовала это, и кто-то из аудитории сказал: «Но разве это не то же самое, что = 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-х годах, когда были всевозможные фильмы, которые выходили с римскими цифрами, поэтому вторая часть "Крестного отца" была первый. В современной истории, Back to the Future III, Star Wars IV, Rocky V, Star Trek VI, даже Final Fantasy VII для 7. Знаете, и это забавно, в Excel есть функция ROMAN! Хорошо, вы можете попросить ROMAN of ROW, и он даст вам римские цифры. Что действительно странно, потому что в Excel 2013, если вы введете римскую цифру XL и попросите арабскую цифру, она преобразует эту РИМСКАЯ цифра обратно в обычную. Итак, когда я закончил свою 39-ю книгу и знал, что скоро появится 40-я книга, я знал, что это просто как верхушка шляпы ко всем этим фильмам, в которых используются римские цифры,Это должно было быть "MrExcel XL", потому что это моя 40-я книга, хотя я поставил там слоган = ROMAN (40), чтобы люди поняли шутку!
Хорошо, просто краткий обзор всего, о чем мы говорили в этом выпуске: И ЭТО всего лишь совет №1 из книги. Привет, спасибо всем, кто предложил этот совет. Это все люди, которые писали обо мне в Твиттере. Билл Хазлетт, которого я здесь даже не упомянул, отметил, что CTRL + R похож на CTRL + D, если вы выделите одну ячейку и перетащите вправо или выберите вправо CTRL + R скопирует вправо. Нажмите кнопку с информацией в правом верхнем углу прямо сейчас, чтобы узнать, как вы можете купить эту книгу.
Спасибо, что заглянули, увидимся в следующий раз на другой сетевой трансляции от!
