4 как перейти в крайнюю левую ячейку строки excel
У вас большой монитор, но таблицы, с которыми вы работаете - еще больше. И, пробегая взглядом по экрану в поисках нужной информации, всегда есть шанс "соскользнуть" взглядом на соседнюю строчку и посмотреть не туда. Я даже знаю людей, который для таких случаев постоянно держат недалеко от себя деревянную линейку, чтобы приложить ее к строке на мониторе. Технологии будущего!
А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:
Поудобнее, чем линейка, правда?
Есть несколько способов разной сложности, чтобы реализовать такое. Каждый способ - со своими плюсами и минусами. Давайте разберем их детально.
Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец
Самый очевидный путь для решения нашей проблемы "в лоб" - нам нужен макрос, который будет отслеживать изменение выделения на листе и выделять целую строку и столбец для текущей ячейки. Также желательно иметь возможность при необходимости включать и отключать эту функцию, чтобы такое крестообразное выделение не мешало нам вводить, например, формулы, а работало только тогда, когда мы просматриваем список в поисках нужной информации. Это приводит нас к трем макросам (выделения, включения и выключения), которые нужно будет добавить в модуль листа.
Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:
Измените адрес рабочего диапазона на свой - именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.
Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off - выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.
Плюсы этого способа:
- относительная простота реализации
- выделение - операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть
Минусы этого способа:
- такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки - выделяются сразу все строки и столбцы, входящие в объединение
- если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца
Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование
Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA - высший пилотаж ;)
Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке - высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:
- кодовое слово для параметра, например "столбец" или "строка"
- адрес ячейки, для которой мы хотим определить значение этого параметра
Хитрость в том, что второй аргумент не является обязательным. Если он не указан, то берется текущая активная ячейка.
Вторая составляющая этого способа - условное форматирование. Эта крайне полезная функция Excel позволяет автоматически форматировать ячейки, если они удовлетворяют заданным условиям. Если соединить эти две идеи в одно целое, то получим следующий алгоритм реализации нашего координатного выделения через условное форматирование:
- Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
- В Excel 2003 и более старших версиях открываем меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) . В Excel 2007 и новее - жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
- Вводим формулу для нашего координатного выделения:
Все почти готово, но остался один нюанс. Дело в том, что Excel не считает изменение выделения изменением данных на листе. И, как следствие, не запускает пересчет формул и перекраску условного форматирования только при изменении положения активной ячейки. Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:
Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.
Плюсы этого способа:
- Условное форматирование не нарушает пользовательское форматирование таблицы
- Этот вариант выделения корректно работает с объединенными ячейками.
- Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
- Макросы используются минимально
Минусы этого способа:
- Формулу для условного форматирования надо вводить вручную.
- Нет быстрого способа включить-выключить такое форматирование - оно включено всегда, пока не будет удалено правило.
Способ 3. Оптимальный. Условное форматирование + макросы
Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.
Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:
Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.
Способ 4. Красивый. Надстройка FollowCellPointer
Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:
Красивое решение. Не без глюков местами, но попробовать точно стоит. Качаем архив, распаковываем на диск и устанавливаем надстройку:
Как быстро перейти в верхнюю левую ячейку или ячейку A1 на большом листе?
Если у вас есть большой рабочий лист с тысячами строк и столбцов, когда вы работаете с этим листом в глубоких строках и столбцах, чтобы вернуться в левый верхний угол - ячейку A1, вам нужно перетащить прокрутки. Но здесь я порекомендую несколько быстрых трюков, чтобы перейти к ячейке A1 всего за одну секунду.
Быстро переходите в верхнюю левую ячейку или ячейку A1 на большом листе с помощью сочетаний клавиш
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Самый простой способ вернуться к ячейке A1 - это сочетание клавиш, сделайте следующее:
Удерживая нажатой Ctrl + Главная вместе, как показано на следующем снимке экрана, курсор переместится в ячейку A1 из любой точки рабочего листа.
Быстро переместитесь в верхнюю левую ячейку или ячейку A1 на большом листе с помощью имени диапазона
Вы также можете определить имя диапазона для ячейки A1, и вам просто нужно щелкнуть имя диапазона в поле имени, курсор будет возвращен в ячейку A1 так быстро, как вы можете.
1. Щелкните ячейку A1 и введите имя диапазона в поле Имя Box, см. снимок экрана:
2, Затем нажмите Enter ключ, и теперь, когда вы щелкаете имя этого диапазона в поле Имя, оно вернется в ячейку A1 из любой точки рабочего листа.
Внимание: Вышеупомянутые два метода могут быть успешно применены, когда на рабочем листе нет области замораживания, если на вашем листе активирована область замораживания, она перенесет вас в первую строку, первый столбец под блокированными панелями.
На практике часто возникает необходимость быстро найти значение последней (крайней) непустой ячейки в строке или столбце таблицы. Предположим, для примера, что у нас есть вот такая таблица с данными продаж по нескольким филиалам:
Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера - 41 и т.д.
Если бы в нашей таблице не было пустых ячеек, то путь к решению был бы очевиден - можно было бы посчитать количество заполненных ячеек в каждой строке и брать потом ячейку с этим номером. Но филиалы работают неравномерно: Москва простаивала в марте и августе, филиал в Тюмени открылся только с апреля и т.д., поэтому такой способ не подойдет.
Универсальным решением будет использование функции ПРОСМОТР (LOOKUP) :
У этой функции хитрая логика:
- Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
- Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
- Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.
Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*10 11 или сто миллиардов).
Если в таблице не числа, а текст, то идея остается той же, но "очень большое число" нужно заменить на "очень большой текст":
Применительно к тексту, понятие "большой" означает код символа. В любом шрифте символы идут в следующем порядке возрастания кодов:
- латиница прописные (A-Z)
- латиница строчные (a-z)
- кириллица прописные (А-Я)
- кириллица строчные (а-я)
Поэтому строчная "я" оказывается буквой с наибольшим кодом и слово из нескольких подряд "яяяяя" будет, условно, "очень большим словом" - заведомо "большим", чем любое текстовое значение из нашей таблицы.
Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже "на ура".
Для того чтобы освоить навыки работы без использования мышки первым делом необходимо научиться свободно перемещаться по ячейкам, таблицам и листам Excel.
Зачем это нужно?
Если в вашей компании есть человек, который не пользуется мышью при работе в Excel, то вы наверно замечали, как быстро он выполняет свою работу.
Использование клавиатуры в качестве основного инструмента дает существенную прибавку к скорости работы. Это связано с тем, что наиболее часто используемые команды можно выполнить путем нажатия всего пары клавиш. Также у наших рук есть механическая память, и через некоторое время вы заметите, что вам нужно только подумать, как пальцы уже нажимают нужные клавиши (Вы ведь не задумываетесь, когда печатаете текст - руки сами это делают).
Конечно, полностью отказаться от мышки не получится, да это и не нужно. Например, выбрать рисунок на листе быстрее мышкой, чем перебирать клавиатурой графические объекты. Вы сами для себя определите, какие действия вам быстрее выполнять мышью, а какие с помощью клавиатуры.
Базовые клавиши
Перемещение по одной ячейке. Стрелки вниз, вверх, влево и вправо
Тут все просто. Нажимая стрелки влево, вправо, вверх и вниз вы будете перемещать курсор в соответствующем направлении на одну ячейку.
Перемещение в начало строки. Клавиша Home
Для того чтобы переместиться в начало строки достаточно нажать одну клавишу - Home. Это быстрее чем прокручивать лист мышкой.
Постраничное перемещение по листу Excel. Клавиши Page Up и Page Down
Клавиша Page Up перемещает курсор ровно на одну страницу вверх, а клавиша Page Down – на страницу вниз. Данные клавиши полностью заменяют прокрутку документа скролом мыши и экономят время. Единственное на первый взгляд неудобство - это то, что при медленной прокрутке скролом вы можете более подробно следить за изменениями в тексте не перемещая глаз с одной точки экрана. При использовании клавиш необходимо будет просматривать страницу от верхнего до нижнего края.
Сочетания клавиш для быстрого перемещения по документу
Перемещение курсора в начало и конец документа. Сочетание клавиш Ctrl + Home и Ctrl + End
Для того, чтобы быстро переместиться в начало листа необходимо просто нажать комбинацию клавиш Ctrl + Home.
Аналогично, для быстрого перехода в конец документа - Ctrl + End.
Перемещение по листам книги. Сочетание клавиш Ctrl + Page Up и Ctrl + Page Down
Для перемещения между листами книги также можно использовать клавиатуру. Зажмите клавишу Ctrl и клавишами Page Up или Page Down перемещайтесь на предыдущий или следующий лист.
Перемещение по документам Excel и другим приложениям. Сочетание клавиш Alt + Tab
Данная комбинация клавиш позволяет переключаться между открытыми приложениями. Как правило, у нас открыто множество приложений, но параллельно мы работаем с двумя-тремя из них (это могут быть Excel и Word, Excel и интернет браузер). Сколько времени вы тратите на то, чтобы переключиться с одного на другой. Сочетание клавиш Alt + Tab позволяет мгновенно перейти к предыдущему документу с котором вы работали. Если необходимо перейти к другому документу, то удерживая клавишу Alt, нажимайте Tab до тех пор, пока не перейдете к нужному документу.
Навигация внутри таблицы и прочие методы перемещения
Быстрое перемещение по таблице. Сочетание клавиши Ctrl и стрелок вверх, вниз, влево и вправо
С помощью клавиатуры гораздо быстрее и удобнее перемещаться внутри таблицы. Для этого используется комбинация клавиши Ctrl и клавиш со стрелками.
Более подробно рассмотрим как работает сочетание клавиш Ctrl + Вниз, так как сочетание клавиши Ctrl с другими стрелками меняет только направление перемещения курсора.
Если курсор установлен на ячейке, содержащей данные, то комбинация клавиш Ctrl + Вниз переместит его вниз по столбцу до последней непустой ячейки. Если же курсор изначально был установлен в пустой ячейке, то сочетание клавиш Ctrl + Вниз поместит его вниз до первой непустой ячейки столбца.
Как это можно использовать: Во-первых, можно быстро переместиться в конец или начало таблицы по полностью заполненному столбцу. Во-вторых, очень удобно найти ячейку, на которой вы остановились, заполняя данные.
Вызов окна перехода. Сочетание клавиш Ctrl + G
С помощью диалогового окна "переход" легко перейти к именованной ячейке/диапазону или умной таблице. Для вызова этого окна можно воспользоваться комбинацией клавиш Ctrl + G или клавишей F5. Для выбора объекта, к которому вы хотите перейти также можно использовать клавиатуру: нажмите Tab и стрелками вверх и вниз выберите переменную к которой хотите перейти.
Использование окна поиска для перехода к нужной ячейки. Сочетание клавиш Ctrl + F
Очень часто, вместо того чтобы прокручивать скролом мышки документ или перебирать страницы клавишами, проще перейти к нужному место путем поиска. Для того чтобы быстро вызвать окно поиска пользуйтесь сочетанием клавиш Ctrl + F (для запоминания F - от слова Find - поиск).
Если первая строка (строка 1) или столбец (столбец A) не отображаются на этом экране, отобразить их может быть непросто, так как простого способа выбора этой строки или столбца нет. Вы можете выбрать весь таблицу, а затем отобразить скрытые строки или столбцы(вкладкаГлавная, группа Ячейки, кнопка Формат, команда Скрыть & Отобразить), но при этом отображаются все скрытые строки и столбцы на этом компьютере, чего вы, возможно, не захотите делать. Вместо этого можно выбрать первую строку и первый столбец с помощью полей Имя или Перейти.
Чтобы выделить скрытую первую строку или скрытый первый столбец листа, выполните одно из описанных ниже действий.
В поле Имя рядом с полем строка формул введите A1и нажмите ввод.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти. В поле Ссылка введите значение A1 и нажмите кнопку ОК.
На вкладке Главная в группе Ячейки нажмите кнопку Формат.
Выполните одно из следующих действий:
В области Видимостьщелкните Скрыть & Открыть, а затем щелкните Открыть строки или Открыть столбцы.
В поле Размер ячейкищелкните Высота строки или Ширина столбца ,а затем в поле Высота строки или Ширина столбца введите значение, которое вы хотите использовать для высоты строки или ширины столбца.
Совет: По умолчанию высота строк составляет 15,а ширина столбцов — 8,43.
Если вы не видите первый столбец (столбец A) или строку (строку 1) на листе, вероятно, они скрыты. Ниже описано, как отобразить их. На этом рисунке столбец A и строка 1 скрыты.
Чтобы отобразить столбец A, щелкните правой кнопкой мыши заголовок или подпись столбца B и выберите Показать.
Чтобы отобразить строку 1, щелкните правой кнопкой мыши заголовок или подпись строки 2 и выберите Показать.
Совет: Если вы не видите команды Отобразить столбцы или Отобразить строки, убедитесь, что вы щелкаете правой кнопкой мыши внутри подписи столбца или строки.
Читайте также: