Правая нижняя ячейка excel самая
Приветствую!
Можно ли сделать так, чтобы ячейка принимала значение, которое находиться в самой нижней ячейке другого столбца?
К примеру, на первом скриншоте ячейка B2 принимает значение 109, так как ячейка A12 самая нижняя.
На втором скрине в ячейка B2 появляется число 507, так как в столбце А самой нижней ячейкой является ячейка А1 со значением 507
только теперь второй вопрос, что лучше:
=ИНДЕКС (A1:A16;ПОИСКПОЗ (1000;A1:A16;1))
или
=ПРОСМОТР (2;1/A1:A16;A1:A16)
По идее вторая функция жрет меньше ресурсов компа? только теперь второй вопрос, что лучше:
=ИНДЕКС (A1:A16;ПОИСКПОЗ (1000;A1:A16;1))
или
=ПРОСМОТР (2;1/A1:A16;A1:A16)
По идее вторая функция жрет меньше ресурсов компа?
только теперь второй вопрос, что лучше:
=ИНДЕКС (A1:A16;ПОИСКПОЗ (1000;A1:A16;1))
или
=ПРОСМОТР (2;1/A1:A16;A1:A16)
По идее вторая функция жрет меньше ресурсов компа?
Полосатый жираф Алик Искусственный Интеллект (300771) Одинаково будет. Вот только, если в данных будет значение, большее 1000, то первая формула не сработает. Потому у меня и стоит в формуле 1E+307 Это ОЧЕНЬ большое число и в данных не встретится число, большее него.
Вам для коллекции формулы
=-1/ПРОСМОТР (;-1/A1:A16)
и формула массива, ввод Ctrl+Shift+Enter
=-1/ВПР (;-1/A1:A16;1)
Спасибо за ответ! Как написал выше, на ресурсе планета ексель нашел подходящее решение и взял за основу: =ПРОСМОТР (2;1/A1:A16;A1:A16) и получилось!
Но не понятно, что означает 2 (2 менял на 1 результат правильный) и что означает 1 делить на область (вогнало в ступор и никак не могу врубиться)
Алексей Матевосов (AlexM) Просветленный (27241) 1/A1:A16 получаем числа меньше 2 (или 1), чтобы функция ПРОСМОТР () работала правильно (см. описание) еще формула =ПРОСМОТР (9^9;A:A) Попробуйте 5 формул из коллекции на диапазонах с разными последними значениями после 109: 0,2; -0,2; 500; -500. Функции отработают по-разному. Выберете подходящую. Классическая формула на Планете написана в 2011г. А сейчас 2016г Экселисты придумывают новые решения.
Это статья про выделение (Selection) ячеек с целью изменения Формата ячеек , а не про выделение (Highlight) ячеек цветом с помощью Условного форматирования .
Выделяем диапазон смежных ячеек
Для того чтобы выделить диапазон смежных ячеек, необязательно отрывать руки с клавиатуры и браться за мышку. Для выделения диапазона достаточно выделить ячейку - левый верхний угол диапазона, затем нажать клавишу SHIFT и одну из стрелок (вниз, вверх, влево, вправо).
Количество выделенных столбцов и строк отображается в поле Имя (слева от Строки формул ) в формате 3Rx1C, что означает, что выделено 3 ячейки в одном столбце.
После того, как Вы отпустили клавишу SHIFT , в поле Имя отобразится адрес активной ячейки.
Выделяем большой диапазон смежных ячеек
Если диапазон ячеек, который необходимо выделить не помещается на видимой части листа, то можно уменьшить масштаб отображения листа (см. статью Быстрое изменение масштаба отображения листа ) или щелкнуть первую ячейку диапазона (левую верхнюю), а затем, добравшись до последней ячейки диапазона с помощью полосы прокрутки листа, нажать клавишу SHIFT и щелкнуть последнюю ячейку диапазона (правую нижнюю). В случае выделения ячеек, содержащих значения, можно щелкнуть первую ячейку диапазона, а затем, удерживая клавишу SHIFT , дважды щелкнуть по ее нижней границе. Будет выделен диапазон до первой незаполненной ячейки. Чтобы распространить выделение на заполненные столбцы справа, нужно, удерживая клавишу SHIFT , дважды щелкнуть по правой границе ранее выделенного диапазона.
Выделяем все ячейки листа
Выделяем несмежные ячейки или диапазоны ячеек
Выделите первую ячейку или диапазон ячеек и, удерживая клавишу CTRL , выделите курсором мыши остальные ячейки или диапазоны. Также можно, выбрав первую ячейку или диапазон ячеек, нажать сочетание клавиш SHIFT+F8 , чтобы добавить другую несмежную ячейку или диапазон ячеек к области выделения. Чтобы остановить выделение, повторно нажмите сочетание клавиш SHIFT+F8 . Невозможно исключить отдельные ячейки или диапазоны ячеек из области выделения несмежных ячеек; можно лишь отменить все выделение целиком.
Выделяем всю строку или весь столбец
Щелкните заголовок строки или столбца. Строку или столбец можно выделить, выбрав первую ячейку строки или столбца и нажав затем сочетание клавиш CTRL+SHIFT+КЛАВИША СО СТРЕЛКОЙ (СТРЕЛКА ВПРАВО или СТРЕЛКА ВЛЕВО позволяет выделить всю строку, СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ позволяет выделить весь столбец). Если строка или столбец содержат данные, CTRL+SHIFT+КЛАВИША СО СТРЕЛКОЙ выделяет текущую область данных столбца или строки. Повторное нажатие сочетания клавиш CTRL+SHIFT+КЛАВИША СО СТРЕЛКОЙ позволяет выделить все ячейки столбца или строки.
Выделяем последнюю ячейку листа, которая содержит данные или форматирование
Чтобы выделить последнюю ячейку листа, которая содержит данные или форматирование, нажмите сочетание клавиш CTRL+END . Сама ячейка может быть пуста и не иметь форматирование. Но, она находится в самом правом столбце и в самой нижней строке, ячейки которых имеют форматирование и не пусты. Иногда, выделенная таким образом ячейка находится в незаполненных строке и столбце. Чтобы все встало на свои места - сохраните книгу.
Рассмотрим диапазон значений, в который регулярно заносятся новые данные.
Диапазон без пропусков и начиная с первой строки
В случае, если в столбце значения вводятся, начиная с первой строки и без пропусков, то определить номер строки последней заполненной ячейки можно формулой: =СЧЁТЗ(A:A))
Формула работает для числовых и текстовых диапазонов (см. Файл примера )
Значение из последней заполненной ячейки в столбце выведем с помощью функции ИНДЕКС() : =ИНДЕКС(A:A;СЧЁТЗ(A:A))
Ссылки на целые столбцы и строки достаточно ресурсоемки и могут замедлить пересчет листа. Если есть уверенность, что при вводе значений пользователь не выйдет за границы определенного диапазона, то лучше указать ссылку на диапазон, а не на столбец. В этом случае формула будет выглядеть так: =ИНДЕКС(A1:A20;СЧЁТЗ(A1:A20))
Диапазон без пропусков в любом месте листа
Если список, в который вводятся значения расположен в диапазоне E8:E30 (т.е. не начинается с первой строки), то формулу для определения номера строки последней заполненной ячейки можно записать следующим образом: =СЧЁТЗ(E9:E30)+СТРОКА(E8)
Формула СТРОКА(E8) возвращает номер строки заголовка списка. Значение из последней заполненной ячейки списка выведем с помощью функции ИНДЕКС() : =ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))
Диапазон с пропусками (числа)
В случае наличия пропусков (пустых строк) в столбце, функция СЧЕТЗ() будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает пустые ячейки.
Если диапазон заполняется числовыми значениями, то для определения номера строки последней заполненной ячейки можно использовать формулу =ПОИСКПОЗ(1E+306;A:A;1) . Пустые ячейки и текстовые значения игнорируются.
Так как в качестве просматриваемого массива указан целый столбец ( A:A ), то функция ПОИСКПОЗ() вернет номер последней заполненной строки. Функция ПОИСКПОЗ() (с третьим параметром =1) находит позицию наибольшего значения, которое меньше или равно значению первого аргумента (1E+306). Правда, для этого требуется, чтобы массив был отсортирован по возрастанию. Если он не отсортирован, то эта функция возвращает позицию последней заполненной строки столбца, т.е. то, что нам нужно.
Чтобы вернуть значение в последней заполненной ячейке списка, расположенного в диапазоне A2:A20 , можно использовать формулу: =ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))
Диапазон с пропусками (текст)
В случае необходимости определения номера строки последнего текстового значения (также при наличии пропусков), формулу нужно переделать: =ПОИСКПОЗ("*";$A:$A;-1)
Пустые ячейки, числа и текстовое значение Пустой текст ("") игнорируются.
Диапазон с пропусками (текст и числа)
Если столбец содержит и текстовые и числовые значения , то для определения номера строки последней заполненной ячейки можно предложить универсальное решение: =МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ("*";$A:$A;-1);0); ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))
Функция ЕСЛИОШИБКА() нужна для подавления ошибки возникающей, если столбец A содержит только текстовые или только числовые значения.
Другим универсальным решением является формула массива : =МАКС(СТРОКА(A1:A20)*(A1:A20<>""))
После ввода формулы массива нужно нажать CTRL + SHIFT + ENTER . Предполагается, что значения вводятся в диапазон A1:A20 . Лучше задать фиксированный диапазон для поиска, т.к. использование в формулах массива ссылок на целые строки или столбцы является достаточно ресурсоемкой задачей.
Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции ДВССЫЛ() : =ДВССЫЛ("A"&МАКС(СТРОКА(A1:A20)*(A1:A20<>"")))
Как обычно, после ввода формулы массива нужно нажать CTRL + SHIFT + ENTER вместо ENTER .
СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц .
Часто приходится искать на листе ячейки содержащие формулы, Условное форматирование , Проверку данных или просто пустые ячейки. Это и многое другое можно быстро найти при помощи инструмента Выделение группы ячеек.
Рассмотрим очень полезный инструмент Выделение группы ячеек .
Совет . О выделении ячеек на листе с помощью курсора мыши и клавиатуры читайте статью Выделение диапазонов ячеек в MS EXCEL . О выделении ячеек в зависимости от их содержимого - читайте в статье Условное форматирование в MS EXCEL .
Окно этого инструмента доступно через меню Главная/ Найти и выделить/ Выделение группы ячеек . Название инструмента не совсем корректно, он может выделять не только ячейки, но и графические объекты.
В этой статье рассмотрим все опции инструмента MS EXCEL "Выделение группы ячеек".
ПРИМЕЧАНИЯ . Выделяет только те ячейки, которые содержат примечания к ячейкам. Сами примечания при этом не показываются. Показать их можно, нажав кнопку Рецензирование/ Примечания/ Показать все примечания . Эта опция также доступна через меню Главная/ Найти и выделить/ Примечания . Сочетания клавиш быстрого вызова: CTRL + SHIFT +щ .
КОНСТАНТЫ . Выделяет все непустые ячейки, не содержащие формул. Эта опция полезна в случае, если на листе создана некоторая расчетная модель и нужно очистить все ячейки с исходными данными, чтобы ввести в них новые значения. Если ячейка содержит значение =11 , то это значение считается формулой, а не константой, хотя значение ячейки не может меняться. Такая ячейка выделена не будет. Эта опция также доступна через меню Главная/ Найти и выделить/ Константы .
ФОРМУЛЫ . Выделяет ячейки, содержащие формулы. Можно также сузить задачу, выбрав тип результата, который дает формула: число, текст, логическое значение (ИСТИНА или ЛОЖЬ) или ошибку. Удобно для использования поиска ошибок. Эта опция также доступна через меню Главная/ Найти и выделить/ Формулы .
ПУСТЫЕ ЯЧЕЙКИ . Выделяет пустые ячейки. Конечно, не все пустые ячейки на листе будут выделены, т.к. их очень много (пользователь не заполняет все 1048576 строк). EXCEL выделяет пустые ячейки не на всем листе, а только на определенной области: в диапазоне А1:Адрес последней заполненной ячейки (Последняя заполненная ячейка - ячейка, которая расположена в самой нижней используемой строке и в самом крайнем правом заполненном столбце). Для ее определения можно нажать CTRL + END . Что такое пустая ячейка? Для EXCEL это ячейка, в которой не содержится ни значения, ни формулы. А если к ячейке применено Условное форматирование или задействован инструмент Проверка данных , и она ничего не содержит? Эта ячейка считается пустой. Удаление такой ячейки для пользователя может означать потерю формулы введенной, например, в Условном форматировании .
ТЕКУЩАЯ ОБЛАСТЬ . Выделяет прямоугольный диапазон ячеек вокруг активной ячейки. Граница этого диапазона определяется окружающими его пустыми строками и столбцами. Сочетания клавиш быстрого вызова: CTRL+SHIFT+* или CTRL + A .
ТTREOBQ МАССИВ . Эта опция используется крайне редко. Выделяет диапазон ячеек, содержащий массив в который входит активная ячейка. Те, кто не знаком с формулами массива , могут пропустить описание этой опции. Остальные могут поэкспериментировать.
- выделите ячейки от A1 до E1 включительно (всего 5 ячеек);
- в Строке формул введите формулу = ;
- нажмите сочетание клавиш CTRL+SHIFT+ENTER .
Теперь мы получили массив. Попробуйте удалить значение из ячейки B 1 . Вы получите вот такое предупреждение:
Выделите любую ячейку от A1 до E1 . Вызовите инструмент Выделение группы ячеек . Установите опцию Текущий массив . После нажатия ОК, будет выделен диапазон A1:E1 , содержащий массив (вместо нажатия на ОК можно дважды кликнуть на слова Текущий массив ). Сочетания клавиш быстрого вызова: CTRL +/ .
ОБЪЕКТЫ . Выбирает графические объекты рабочего листа. Очень удобная опция - позволяет обнаружить скрытые надписи, фигуры , диаграммы (графики). Полезно при вставке данных из интернета.
ОТЛИЧИЯ ПО СТРОКАМ. Анализирует выбранное множество ячеек и выделяет те, которые отличаются от ячеек самого левого столбца (сравнение производится построчно). Сочетания клавиш быстрого вызова: Ctrl + \ .
Приведем пример. Имеется 2 столбца с почти одинаковыми данными: значения отличаются только в строках 5 и 8 . Выделяем диапазон А2:В10 .
Вызываем инструмент Выделение группы ячеек с опцией Отличия по строкам и получаем 2 выделенные ячейки, одна на строке 5 , другая на 8 . Логика такова: столбец 2 сравнивается с эталонным столбцом 1 . Если добавить еще один столбец, то будут выделены все ячейки, которые отличаются в соответствующих строках от ячеек столбца 1 .
ОТЛИЧИЯ по СТОЛБЦАМ. Аналогично предыдущей опции. Эталоном служит верхняя строка.
ВЛИЯЮЩИЕ ЯЧЕЙКИ . Выделяет ячейки, на которые есть ссылки в формулах. Можно использовать так: выделяете весь лист (или область, где находятся формулы и ячейки с исходными данными), вызываете инструмент Выделение группы ячеек с опцией Влияющие ячейки и получаете выделенными ВСЕ ячейки, влияющие на значения других ячеек (для их запоминания можно изменить фон ячеек). Сравните с командой меню Формулы/ Зависимости формул/ Влияющие ячейки (позволяет определить на какие ячейки влияет активная ячейка).
ЗАВИСИМЫЕ ЯЧЕЙКИ . Выделяет ячейки с формулами, в которых есть ссылки на выделенные ячейки. Можно использовать так: выделяете весь лист, вызываете инструмент Выделение группы ячеек с опцией Зависимые ячейки и получаете выделенными ВСЕ ячейки, зависимые от других ячеек (для их запоминания можно изменить фон ячеек). Сравните с командой меню Формулы/ Зависимости формул/ Зависимые ячейки .
ПОСЛЕДНЯЯ ЯЧЕЙКА. Выделяет нижнюю правую ячейку листа, которая содержит данные или атрибуты форматирования. Быстрее воспользоваться клавиатурным эквивалентом этой команды - CTRL + END .
ТОЛЬКО ВИДИМЫЕ ЯЧЕЙКИ . Выделяет только видимые ячейки из выделенного диапазона. Ячейки в скрытых столбцах и строках не выделяются. Эта опция полезна для работы со структурами или со списком, к которому применен фильтр .
УСЛОВНЫЕ ФОРМАТЫ . Выделяет ячейки, к которым применено Условное форматирование ( Главная/ Стили/ Условное форматирование )
ПРОВЕРКА ДАННЫХ . Выделяет ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки допустимости данных, что и для активной ячейки.
На практике часто возникает необходимость быстро найти значение последней (крайней) непустой ячейки в строке или столбце таблицы. Предположим, для примера, что у нас есть вот такая таблица с данными продаж по нескольким филиалам:
Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера - 41 и т.д.
Если бы в нашей таблице не было пустых ячеек, то путь к решению был бы очевиден - можно было бы посчитать количество заполненных ячеек в каждой строке и брать потом ячейку с этим номером. Но филиалы работают неравномерно: Москва простаивала в марте и августе, филиал в Тюмени открылся только с апреля и т.д., поэтому такой способ не подойдет.
Универсальным решением будет использование функции ПРОСМОТР (LOOKUP) :
У этой функции хитрая логика:
- Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
- Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
- Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.
Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*10 11 или сто миллиардов).
Если в таблице не числа, а текст, то идея остается той же, но "очень большое число" нужно заменить на "очень большой текст":
Применительно к тексту, понятие "большой" означает код символа. В любом шрифте символы идут в следующем порядке возрастания кодов:
- латиница прописные (A-Z)
- латиница строчные (a-z)
- кириллица прописные (А-Я)
- кириллица строчные (а-я)
Поэтому строчная "я" оказывается буквой с наибольшим кодом и слово из нескольких подряд "яяяяя" будет, условно, "очень большим словом" - заведомо "большим", чем любое текстовое значение из нашей таблицы.
Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже "на ура".
Читайте также: