Как в экселе обозначить предыдущую ячейку
Чтобы успешно работать с таблицей надо научиться выделять ячейки в Excel, то есть сказать программе Excel, какие ячейки будут изменяться. Выделить одну ячейку просто: достаточно щёлкнуть по ней курсором. Но часто надо выделить несколько ячеек.
| Понять и запомнить! | Диапазон – это несколько выделенных ячеек Несколько выделенных ячеек, стоящих рядом, – называются «смежные ячейки». Несколько выделенных ячеек, стоящих отдельно друг от друга, – называются «несмежные ячейки» |
Диапазон может содержать:
2. Способы выделения смежных ячеек
1 способ (мышка):
2 способ (клавиатура и мышка):
- Щёлкните ЛМ на первой выделяемой ячейке.
- Нажмите клавишу Shift и не отпускайте ее.
- Щёлкните мышкой на последней ячейке выделяемого диапазона.
- Отпустите клавишу Shift,
3 способ (клавиатура и мышка):
- Щёлкните ЛМ на первой выделяемой ячейке.
- Нажмите клавишу Shift и не отпускайте ее.
- Воспользовавшись клавишами перемещения курсором «→», «↓», «←», «↑», постепенно выделите весть диапазон ячеек.
- Отпустите клавишу Shift.
4 способ (клавиатура и мышка) – мой любимый:
Запомните эту подсказку:
| Shift + Ctrl + ↓ | Выделение всех данных в столбце, начиная от текущей ячейки |
| Shift + Ctrl + → | Выделение всех данных в строчке, начиная от текущей ячейки |
| Замечание | Я ещё не встречала человека, который бы использовал этот замечательный способ выделения диапазона ячеек. Обычно выделяют перетаскиванием мыши. Хорошо, если таблица маленькая. А если на пару сотен строк? |
5 способ (Меню ленты Главная):
Выделение строки – на заголовке строки (курсор в виде черной жирной стрелки находится на имени строки).
Выделение столбца – на заголовке столбца (курсор в виде черной жирной стрелки находится на имени столбца).
Выделение ячеек всего листа – на кнопке, находящейся на пересечении заголовков столбцов и строк.
3. Выделение несмежных ячеек
- Щелкните ЛМ на первой выделяемой ячейке.
- Нажмите клавишу Ctrl и не отпускайте ее.
- Щелкайте мышкой на тех ячейках, которые нужно выделить.
- Отпустите клавишу Ctrl.
4. Перемещение данных
Шаг 1. Выделить данные, которые перемещаются
Шаг 2. Навести указатель мыши на границу выделенного, чтобы указатель мыши превратился в четырехстороннюю стрелку
Шаг 3. Нажать левую клавишу мыши и, не отпуская ее, переместить выделенные ячейки в новое место.
5. Копирование данных
Шаг 1. Выделить ячейки, которые копируются
Шаг 2. Навести указатель мыши на границу выделенного, чтобы указатель мыши превратился в четырехстороннюю стрелку
Шаг 3. Нажать на клавиатуре клавишу Ctrl и не отпускать ее
Шаг 4. Нажать ЛМ мыши и, не отпуская ее, переместить выделенный текст в новое место.
Шаг 5. Отпустить клавишу Ctrl.
| Обратите внимание! | Рядом с курсором «стрелка» появился знак «+». Это означает операцию «Копирование» |
6. Удаление данных
Чтобы удалить данные из ячейки (ячеек) надо выделить ячейку (ячейки) и нажать клавишу или Backspace:
7. Использование команд «Повторить» и «Отменить».
При редактировании текста в предусмотрено средство для возврата текста в то состояние, в каком он был до внесения изменений. Если отменить последнее действие или набор действий, то можно повторно выполнить эти отмененные операции. За данные действия отвечают команды Отменитьи Вернуть. Они находятся на Панели быстрого доступа.
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Описание
В этой статье приведены пошаговые инструкции по поиску данных в таблице (или диапазоне ячеек) с помощью различных встроенных функций Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание образца листа
В этой статье используется образец листа для иллюстрации встроенных функций Excel. Рассматривайте пример ссылки на имя из столбца A и возвращает возраст этого человека из столбца C. Чтобы создать этот лист, введите указанные ниже данные в пустой лист Excel.
Введите значение, которое вы хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
Определения терминов
В этой статье для описания встроенных функций Excel используются указанные ниже условия.
Определение
Вся таблица подстановки
Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».
Просматриваемый_массив
-или-
Лукуп_вектор
Диапазон ячеек, которые содержат возможные значения подстановки.
Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.
3 (третий столбец в инфо_таблица)
Ресулт_аррай
-или-
Ресулт_вектор
Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.
Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.
Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение "5" в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).
Функции
LOOKUP ()
Функция Просмотр находит значение в одной строке или столбце и сопоставляет его со значением в той же позицией в другой строке или столбце.
Ниже приведен пример синтаксиса формулы подСТАНОВКи.
= Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)
Следующая формула находит возраст Марии на листе "образец".
= ПРОСМОТР (E2; A2: A5; C2: C5)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как "Мария" находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).
Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.
Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
Функция ВПР или вертикальный просмотр используется, если данные указаны в столбцах. Эта функция выполняет поиск значения в левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Функцию ВПР можно использовать для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы ВПР :
= ВПР (искомое_значение; инфо_таблица; номер_столбца; интервальный_просмотр)
Следующая формула находит возраст Марии на листе "образец".
= ВПР (E2; A2: C5; 3; ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется "3" в качестве Колумн_индекс (столбец C). Так как "Мария" находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).
Чтобы получить дополнительные сведения о функции ВПР , щелкните следующий номер статьи базы знаний Майкрософт:
INDEX () и MATCH ()
Вы можете использовать функции индекс и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании поиска или функции ВПР.
Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:
= Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)
Следующая формула находит возраст Марии на листе "образец".
= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как "Мария" находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
СМЕЩ () и MATCH ()
Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, объединяющего смещение и сопоставление для достижения того же результата, что и функция Просмотр и ВПР.
= СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)
Эта формула находит возраст Марии на листе "образец".
= СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)
Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как "Мария" находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
Рассмотрим диапазон значений, в который регулярно заносятся новые данные.
Диапазон без пропусков и начиная с первой строки
В случае, если в столбце значения вводятся, начиная с первой строки и без пропусков, то определить номер строки последней заполненной ячейки можно формулой: =СЧЁТЗ(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 .
СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц .
Для пользователей программы Microsoft Excel не секрет, что данные в этом табличном процессоре размещаются в отдельных ячейках. Для того, чтобы пользователь мог обращаться к этим данным, каждому элементу листа присвоен адрес. Давайте выясним, по какому принципу нумеруются объекты в Экселе и можно ли изменить данную нумерацию.
Виды нумерации в Microsoft Excel
Способ 1: переключение режима нумерации
Прежде всего, давайте рассмотрим возможность переключения режима нумерации. Как уже говорилось ранее, адрес ячеек по умолчанию задается по типу A1. То есть, столбцы обозначаются буквами латинского алфавита, а строчки – арабскими цифрами. Переключение в режим R1C1 предполагает вариант, при котором цифрами задаются не только координаты строк, но и столбцов. Давайте разберемся, как произвести такое переключение.
-
Производим перемещение во вкладку «Файл».
Для того, чтобы вернуть обозначение координат по умолчанию, нужно провести ту же самую процедуру, только на этот раз снять флажок с пункта «Стиль ссылок R1C1».
Способ 2: маркер заполнения
Кроме того, пользователь сам может пронумеровать строки или столбцы, в которых расположены ячейки, согласно своим потребностям. Эта пользовательская нумерация может использоваться для обозначения строчек или колонок таблицы, для передачи номера строчки встроенным функциям Excel и в других целях. Конечно, нумерацию можно произвести вручную, просто вбивая с клавиатуры нужные числа, но намного проще и быстрее выполнить данную процедуру, используя инструменты автозаполнения. Особенно это актуально при нумерации большого массива данных.
Взглянем, как при помощи маркера заполнения можно произвести автонумерацию элементов листа.
-
Ставим цифру «1» в ту ячейку, с которой планируем начинать нумерацию. Затем наводим курсор на правый нижний край указанного элемента. При этом он должен трансформироваться в черный крестик. Он носит название маркера заполнения. Зажимаем левую кнопку мышки и тащим курсор вниз или вправо, в зависимости от того, что именно нужно пронумеровать: строчки или столбцы.
Способ 3: прогрессия
Ещё одним способом, с помощью которого можно пронумеровать объекты в Экселе, является использование инструмента под названием «Прогрессия».
-
Как и в предыдущем способе, устанавливаем цифру «1» в первую ячейку, подлежащую нумерации. После этого просто выделяем данный элемент листа, кликнув по нему левой кнопкой мыши.
В блоке настроек «Тип» для наших целей нужно установить переключатель в позицию «Арифметическая». Впрочем, он и так по умолчанию расположен в данной позиции, поэтому нужно лишь проконтролировать его положение.
Блок настроек «Единицы» становится активным только при выборе типа «Даты». Так как мы выбрали тип «Арифметическая», нас вышеуказанный блок интересовать не будет.
В поле «Шаг» следует установить цифру «1». В поле «Предельное значение» ставим количество нумеруемых объектов.
Если вы не желаете производить подсчет количества элементов листа, которые нужно пронумеровать, чтобы указать их в поле «Предельное значение» в окне «Прогрессия», то в этом случае нужно перед запуском указанного окна выделить весь диапазон, подлежащий нумерации.
После этого в окне «Прогрессия» выполняем все те же действия, которые были описаны выше, но на этот раз оставляем поле «Предельное значение» пустым.
Результат будет тот же: выделенные объекты окажутся пронумерованными.
Способ 4: использование функции
Пронумеровать элементы листа, можно также прибегнув к использованию встроенных функций Excel. Например, для построчной нумерации можно применять оператор СТРОКА.
Функция СТРОКА относится к блоку операторов «Ссылки и массивы». Её основной задачей является возврат номера строчки листа Excel, на который будет установлена ссылка. То есть, если мы укажем в качестве аргумента этой функции любую ячейку в первой строке листа, то она выведет значение «1» в ту ячейку, где располагается сама. Если указать ссылку на элемент второй строчки, то оператор выведет цифру «2» и т.д.
Синтаксис функции СТРОКА следующий:
Как видим, единственным аргументом данной функции является ссылка на ячейку, номер строки которой нужно вывести в указанный элемент листа.
Посмотрим, как работать с указанным оператором на практике.
-
Выделяем объект, который будет первым в нумерованном диапазоне. Щелкаем по значку «Вставить функцию», который размещен над рабочей областью листа Excel.
Но мы совершили нумерацию только строк, а для полноценного выполнения задачи присвоения адреса ячейки в виде номера внутри таблицы следует пронумеровать ещё и столбцы. Это также можно сделать при помощи встроенной функции Excel. Данный оператор ожидаемо имеет наименование «СТОЛБЕЦ».
Функция СТОЛБЕЦ также относится к категории операторов «Ссылки и массивы». Как нетрудно догадаться её задачей является выведение в указанный элемент листа номера столбца, на ячейку которого дается ссылка. Синтаксис этой функции практически идентичен предыдущему оператору:
Как видим, отличается только наименование самого оператора, а аргументом, как и в прошлый раз, остается ссылка на конкретный элемент листа.
Посмотрим, как выполнить поставленную задачу с помощью данного инструмента на практике.
-
Выделяем объект, которому будет соответствовать первый столбец обрабатываемого диапазона. Клацаем по пиктограмме «Вставить функцию».
Теперь все ячейки нашей условной таблицы имеют свою относительную нумерацию. Например, элемент, в котором на изображении ниже установлена цифра 5, имеет относительные пользовательские координаты (3;3), хотя абсолютный его адрес в контексте листа остаётся E9.
Способ 5: присвоение имени ячейке
В дополнение к вышеуказанным способам нужно отметить, что, несмотря на проведенное присвоения номеров столбцам и строкам определенного массива, наименования ячеек внутри него будут задаваться в соответствии с нумерацией листа в целом. Это можно увидеть в специальном поле имен при выделении элемента.
Для того, чтобы изменить имя, соответствующее координатам листа на то, которое мы задали с помощью относительных координат для нашего массива, достаточно выделить соответствующий элемент кликом левой кнопки мыши. Затем просто с клавиатуры в поле имени вбить то название, которое пользователь считает нужным. Это может быть любое слово. Но в нашем случае мы просто введем относительные координаты данного элемента. В нашем наименовании обозначим номер строки буквами «Стр», а номер столбца «Стол». Получаем наименование следующего типа: «Стол3Стр3». Вбиваем его в поле имен и жмем на клавишу Enter.
Теперь нашей ячейке присвоено наименование согласно её относительному адресу в составе массива. Таким же образом можно дать имена и другим элементам массива.
Как видим, существуют два вида встроенной нумерации в Экселе: A1 (по умолчанию) и R1C1 (включается в настройках). Данные виды адресации распространяются на весь лист в целом. Но кроме того, каждый пользователь может сделать свою пользовательскую нумерацию внутри таблицы или определенного массива данных. Существует несколько проверенных способов присвоения ячейкам пользовательских номеров: с помощью маркера заполнения, инструмента «Прогрессия» и специальных встроенных функций Excel. После того, как выставлена нумерация, можно на её основе присвоить имя конкретному элементу листа.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: