Поиск и сброс последней ячейки на листе
При сохранении книги Excel сохраняет только часть каждого листа, которая содержит данные или форматирование. Пустые ячейки могут содержать форматирование, которое приводит к тому, что последняя ячейка в строке или столбце выходит за пределы диапазона ячеек, содержащих данные. Это приводит к тому, что размер файла книги будет больше, чем необходимо, и может привести к большему объему печатных страниц при печати листа или книги.
Чтобы избежать этих проблем, можно найти последнюю ячейку, содержащую данные или форматирование, на листе, а затем сбросить эту последнюю ячейку, очистив все форматирование, которое может быть применено в пустых строках или столбцах между данными и последней ячейкой.
Найдите последнюю ячейку, содержащую данные или форматирование на листе
- Чтобы найти последнюю ячейку, содержащую данные или форматирование, щелкните в любом месте листа и нажмите клавиши CTRL+END.
Примечание: Чтобы выделить самую последнюю ячейку в строке или столбце, нажмите клавишу END, а затем нажмите клавишу СТРЕЛКА ВПРАВО или СТРЕЛКА ВНИЗ.
Очистка форматирования между последней ячейкой и данными
- Выполните одно из указанных ниже действий.
- Чтобы выделить все столбцы справа от последнего столбца, содержащего данные, щелкните заголовок первого столбца, удерживая нажатой клавишу CTRL, а затем щелкните заголовки столбцов, которые нужно выбрать.
Совет: Можно также щелкнуть заголовок первого столбца, а затем нажать клавиши CTRL+SHIFT+END.
Совет: Можно также щелкнуть заголовок первой строки, а затем нажать клавиши CTRL+SHIFT+END.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как перейти на последнюю заполненную строку в excel
В MS Excel доступны для использования очень много «быстрых» клавиш. В данной статье я расскажу какими пользуюсь сам и покажу как это увеличивает скорость работы.
Мой лозунг для пользователя MS Excel:
- Выучить функцию ВПР;
- Освоить Сводные Таблицы;
- Научиться использовать «быстрые» клавиши.
Реклама Оцените скорость работы с использованием «быстрых» клавиш (копируем таблицу из одного листа и вставляем в другой лист только значения) — записано в замедленном виде ( курсор мышки не сделал ни одного движения ):
SWF
Как включить видео?
GIF
Чтобы увидеть видео включите в браузере GIF-Анимацию.
Ну давайте уже перейдем к сути — какие клавиши помогают мне ускорить работу в MS Excel.
Работа с книгой (файлом):
Ctrl+F1 — скрыт/показать ленту меню;
Сtrl+N — создать новую книгу (файл);
Ctrl+O — открыть книгу (файл);
Ctrl+S — сохранить книгу (файл);
Ctrl+Z — отмена последнего действия.
Перемещение по листам книги:
Ctrl+PageDown — перейти на 1 лист вправо;
Ctrl+PageUp — перейти на 1 лист влево.
Перемещение по листу (по таблице):
Чтобы быстро переместиться по листу или таблице — Ctrl+стрелка вниз (вверх, вправо, влево). Курсор перейдет на последнюю или первую (в столбце или строке) ячейку с данными, если данных нет — перейдет на последнюю ячейку строки или столбца (в конец листа).
Перемещение в ячейку «A1» из любого места листа — Ctrl+Home.
SWF
Как включить видео?
GIF
Чтобы увидеть видео включите в браузере GIF-Анимацию.
Выделение диапазонов и таблиц:
Чтобы выделить несколько ячеек — Shift+стрелка вниз (вверх, вправо, влево).
Чтобы быстро выделить таблицу — Shift+Ctrl+8.
Чтобы выделить столбец или строку таблицы — Shift+Ctrl+стрелка вниз (вверх, вправо, влево).
SWF
Как включить видео?
GIF
Чтобы увидеть видео включите в браузере GIF-Анимацию.
Копирование и вставка:
Для копирования я использую или Ctrl+C или Ctrl+Insert, зависит это от того на какой клавиатуре я работаю. На ноутбуке легче Ctrl+C, на стационарном компьютере удобнее Ctrl+Insert.
Для вырезания — Ctrl+X.
Для вставки скопированного — Ctrl+V.
Для вызова диалогового окна «Специальная вставка» — Ctrl+Alt+V.
Для быстрого копирования по столбцу или по строке — Ctrl+D и Ctrl+R соответственно. Тут есть один нюанс, а именно чтобы быстро скопировать данные встаньте курсором в ячейку, которую вы хотите скопировать и выделите вместе с ней диапазон в который вы хотите скопировать данные и нажмите Ctrl+D или Ctrl+R:
Как перейти на последнюю заполненную строку в excel
добрый день, коллеги!
Есть у меня одна табличка, в которой ведутся записи по различного рода расходам. И по этой таблице периодически приходится «лазить» и просматривать — а что, когда и почем?
Посему, чтобы сделать новую запись, приходится долго крутить колесиком мыша до последней строки.
И родилась у меня идея — а почему бы не сделать в шапке (области в окне закреплены, потому шапка всегда видна) ссылочку, которая бы отправляла курсор на первую ячейку ниже последней заполненной? Ну и сделал.
Вдруг кому пригодится. А может мне подскажете более изящный метод 😉
Так вот:
— определил номер последней заполненной строки
<=МАКС(ЕСЛИ(A6:A1000<>0;СТРОКА(A6:A1000)))>
— и создал гиперссылку
=ГИПЕРССЫЛКА(«[!Затраты.xls]»&АДРЕС(Затраты!$B$1+1;1);»Перейти в конец списка»)
где:
«[!Затраты.xls]» — имя книги
АДРЕС(Затраты!$B$1+1;1) — ссылка на ячейку на нужном листе
в ячейке Затраты!$B$1 находится первая приведенная формула.
29.04.2008 12:43:35
А тема хорошая, большое спасибо за подсказку, только сегодня думал как это реализовать, ещё раз большое спасибо
29.04.2008 14:01:21
Я поступаю проще — нажимаю Ctrl+End, затем Home
Можно и на кнопочку повесить
Пользователь
Сообщений: 950 Регистрация: 01.01.1970
29.04.2008 14:08:06
нажав ктрл + енд
я попадаю не на последнюю заполненную ДАННЫМИ ячейку, а на последнюю ячейку, в которой есть формулы 😉
а это аж тысячная ячеечка 🙂
а мне сейчас актуальна А361, а не А1000. так что, ваш способ, хоть и самый простой, в моем случае не применим.
Как получить последннюю заполненную ячейку формулой?
Очень часто при работе с большими таблицами возникает вопрос: как узнать последнюю заполненную ячейку в столбце? Обычно это необходимо для того, чтобы суммировать или вычислять среднее только в пределах заданной таблицы, без учета пустых строк, т.к. в случае с вычислением среднего пустые строки могут повлиять на расчеты. Так же определить последнюю ячейку формулой бывает необходимо, если в отдельную ячейку в начале таблицы надо выводить последнее записанное в таблицу значение.
По сути способов узнать последнюю заполненную ячейку формулами не так много. Я в этой статье покажу два варианта: в первом формула проще для понимания, но менее универсальна в использовании — она требует точно знать данные какого типа хранятся в столбце: числа или текст, т.к. ориентируется исключительно на тип данных. Вторая формула более универсальна, но может дольше работать.
Формула ниже по сути будет отбирать только числа и вернет номер самой нижней строки, в которой расположено любое число, даже если это нуль:
=ПОИСКПОЗ(3E+307; A1:A100 )
=MATCH(3E+307,A1:A100)
А эта формула вернет номер строки с последней ячейкой, в которой записан любой текст
=ПОИСКПОЗ(«яяя»; A1:A100 )
=MATCH(«яяя»,A1:A100)
Принцип работы этих формул основан на последнем аргументе функции ПОИСКПОЗ (MATCH) — интервальный просмотр(подробнее про этот аргумент можно узнать из этой статьи — ВПР и интервальный просмотр(range_lookup)). Если его не указывать, то принимается значения по умолчанию для этого аргумента. По умолчанию он равен 1 , что означает искать наибольшее значение, которое меньше или равно искомому . Для «правильной» работы с этим параметром справка Excel рекомендует отсортировать по возрастанию массив значений, в которых осуществляется поиск искомого значения. Но в нашем случае сортировка как раз не нужна. Происходит следующее: в случае с числом мы задает максимально возможное число (3E+307) , которого заведомо в искомых значениях быть не может. ПОИСКПОЗ сверяет каждое значение с этим числом. Определяет, что значение в массиве меньше искомого(но не равно ему!) и запоминает его позицию. Но т.к. ПОИСКПОЗ стремится найти самый подходящий вариант — то просматривает значения дальше, предполагая, что массив отсортирован по возрастанию и дальше пойдут значения ЕЩЕ БОЛЬШЕ предыдущего и там возможно есть значение, равное искомому. Но наш массив не отсортирован и значения там расположены абы как. Да и значения там все меньше указанного. В результате ПОИСКПОЗ доходит до последнего числа в указанном массиве и возвращает именно его позицию, т.к. дальше искать нечего и ПОИСКПОЗ считает, что это максимально подходящее число. Опять же потому, что считает, что значения у нас отсортированы.
Тоже самое и с текстом, только тут мы задаем текст «яяя», который в бинарной сетке будет в самом низу, т.к. буква «я» имеет самый большой числовой код. А три этих буквы подряд дают по сути «самый большой текст».
Но чаще всего как раз заранее неизвестно, какие именно данные будут в ячейках: текстовые или числовые. Т.е. по факту в столбце могут быть абсолютно любые данные. В таких случаях можно применить один из следующих вариантов формулы:
- =МАКС(ПОИСКПОЗ(; A1:A100 ))
=MAX(MATCH(,A1:A100)) - =ПРОСМОТР(2;1/( A1:A100 <>«»);СТРОКА( A1:A100 ))
=LOOKUP(2,1/(A1:A100<>«»),ROW(A1:A100))
Первая формула вводится как формула массива(ввод формулы в ячейку завершается нажатием не просто Enter, а сочетанием клавиш Ctrl+Shift+Enter). Но есть и еще один недостаток: если в столбце нет какого-либо типа данных — формула вернет #Н/Д. Обхитрить можно, если захватить в расчет заголовок, в котором будет текст или число, в зависимости от того, какие данные предположительно могут отсутствовать. Или сделать двойной заголовок — в одном число, в другом текст. Основной принцип работы ПОИСКПОЗ в данном случае описан выше. Могу лишь добавить, что ввод её как формулу массива заставляет формулу искать как позицию самого дальнего числа, так и позицию самого дальнего текста. А функция МАКС (MAX) отбирает из найденных двух позиций максимальное значение.
Вторая формула вводится в ячейку обычным методом и вроде как не имеет никаких подводных камней. Кроме одного: не стоит указывать в качестве диапазона ВЕСЬ СТОЛБЕЦ с данными — формула может очень долго пересчитываться. Особенно это сказывается в файлах версии 2007 Excel, где строк больше миллиона. Предыдущие формулы лишены этого недостатка. Хотя я в любом случае советовал бы указывать явно диапазон «с запасом».
Принцип её работы похож на ПОИСКПОЗ с небольшими дополнениями:
- A1:A100 <>«» — здесь идет сравнение каждого значения в указанном диапазоне и если ячейка не пустая — возвращается ИСТИНА (TRUE) .
- 1/( A1:A100 <>«») — здесь единица делится на полученные значения ИСТИНА (TRUE) . Звучит как бред, но. Для Excel ИСТИНА это 1, а ЛОЖЬ — 0. Таким образом мы получаем массив значений 1 и #ДЕЛ/0 (#DIV/0) . Т.е. максимальное число в массиве у нас — 1. А принцип работы функции ПРОСМОТР (LOOKUP) очень похож на ПОИСКПОЗ, только она всегда стремиться найти наибольшее подходящее значение, меньшее или равное искомому. А в качестве искомого мы задаем 2, т.е. оно заведомо больше любого значения в массиве для поиска: =ПРОСМОТР(2;1/( A1:A100 <>«»)
Таким образом ПРОСМОТР всегда будет нам возвращать позицию последней заполненной ячейки. Последний аргумент функции ПРОСМОТР — массив, равный по размеру просматриваемому( A1:A100 ), из которого будет возвращено значение. Мы задаем в качестве этого массива значений для возврата массив номеров строк: СТРОКА( A1:A100 ) . Т.е. если в массиве A1:A100 последнее значение будет в ячейке A9 , то ПРОСМОТР вернет значение для СТРОКА( A9 ) .
Как видно, недостатки есть в любой из приведенных формул, так что выбор в любом случае за Вами и зависеть он будет напрямую от поставленной задачи.
Вот один из примеров, как можно применить определение последней ячейки в реальных формулах. Например, вычисление среднего значения:
=СРЗНАЧ( A2 :ИНДЕКС( A1:A100 ;ПОИСКПОЗ(9E+307; A1:A100 )))
Числовые данные начинаются с ячейки A2 . В A1 заголовок, а где заканчиваются данные неизвестно — они постоянно изменяются: удаляются, дополняются.
В данном случае мы первой ячейкой указываем A2 — начало числовых данных. А вот далее уже идет вычисление последней ячейки:
ПОИСКПОЗ(9E+307; A1:A100 )
В данном случае можно применить поиск последней ячейки именно с числом, т.к. СРЗНАЧ (AVERAGE) в любом случае игнорирует текст и лишние ячейки нам ни к чему. ПОИСКПОЗ (MATCH) возвращает номер последней ячейки в диапазоне A1:A100 . Но чтобы получить именно ссылку на эту ячейку, а не просто её строку мы используем ИНДЕКС (INDEX) :
ИНДЕКС( A1:A100 ;ПОИСКПОЗ(9E+307; A1:A100 ))
Т.е. по шагам формулу можно представить так:
=СРЗНАЧ( A2 :ИНДЕКС( A1:A100 ;ПОИСКПОЗ(9E+307; A1:A100 ))) =>
=СРЗНАЧ( A2 :ИНДЕКС( A1:A100 ;9)) =>
=СРЗНАЧ( A2 : A9 ) =>
4,5
В приложенном к теме примере записаны все приведенные формулы, а так же пара примеров того, как эти формулы можно использовать в других формулах для определения конца диапазона.
Статья помогла? Поделись ссылкой с друзьями!