Ячейка в Excel — это основной структурный элемент листа, куда вы можете вводить данные и другой контент. На этом уроке мы изучим основы работы с ячейками и их содержимым для выполнения расчетов, анализа и организации данных в Excel.
Основные сведения о ячейках в Excel
Каждая электронная таблица Excel состоит из тысяч прямоугольников, называемых ячейками. Ячейка — это пересечение строки и столбца. Столбцы в Excel обозначаются буквами (A, B, C), а строки — цифрами (1, 2, 3).
В зависимости от строки и столбца каждой ячейке в Excel присваивается имя, также известное как адрес. Например, C5 — это ячейка на пересечении столбца C и строки 5. При выборе ячейки ее адрес отображается в поле Имя. Обратите внимание, что при выборе ячейки подсвечиваются заголовки строки и столбца, на пересечении которых она находится.
Microsoft Office Excel имеет возможность выделять сразу несколько ячеек. Набор из двух или более ячеек называется диапазоном. Любой диапазон, как и ячейка, имеет свой адрес. В большинстве случаев адрес диапазона состоит из адресов верхней левой и нижней правой ячеек, разделенных двоеточием. Такой диапазон называется непрерывным или непрерывным. Например, диапазон, состоящий из ячеек B1, B2, B3, B4 и B5, будет записан как B1:B5.
На следующем рисунке выделены два разных диапазона ячеек:
- Диапазон А1:А8
- Диапазон А1:В8
Если столбцы вашего рабочего листа представлены числами, а не буквами, вам необходимо изменить стиль связывания по умолчанию в Excel. Подробнее читайте в уроке: Какой стиль у ссылок в Excel.
Подробнее: Vba excel присоединиться
Выделение ячеек в Excel
Чтобы ввести данные или отредактировать содержимое ячейки, ее необходимо сначала выделить.
- Нажмите на ячейку, чтобы выбрать ее.
- Выбранная ячейка будет иметь границу, а заголовки столбцов и строк будут выделены. Ячейка останется выделенной, пока вы не выберете любую другую ячейку.
Вы также можете выбирать ячейки с помощью клавиш со стрелками на клавиатуре (клавиши со стрелками).
Выделение диапазона ячеек в Excel
При работе с Excel часто бывает необходимо выделить большую группу ячеек или диапазон.
- Щелкните первую ячейку в диапазоне и, не отпуская кнопку мыши, перемещайте мышь до тех пор, пока не будут выбраны все соседние ячейки, которые вы хотите выделить.
- Отпустите кнопку мыши, нужный диапазон будет выделен. Ячейки останутся выделенными, пока вы не выберете любую другую ячейку.
Microsoft Excel
приемы • приемы • решения
Что такое в Excel зависимые и влияющие ячейки
На многих листах ячейки могут содержать сложные отношения. Отладку формул можно упростить, если вы понимаете две ключевые концепции: влияющие и зависимые ячейки.
- Ячейки влияния: вызывает вычисление результата формулы. Непосредственно влияющая ячейка указывается непосредственно в формуле, а косвенно влияющие ячейки не используются непосредственно в формуле, а применяются ячейкой, на которую ссылается формула.
- Зависимые ячейки: эти ячейки формулы зависят от конкретной (влиятельной) ячейки. Все ячейки с формулами, использующими эту ячейку, зависят от влияющей ячейки. Ячейка с формулой может зависеть прямо или косвенно.
Выявление затронутых ячеек в ячейке, содержащей формулу, часто проливает свет на то, почему формула работает неправильно. И наоборот, также полезно знать, какие ячейки формулы зависят от конкретной ячейки. Например, если вы собираетесь удалить формулу, вы можете проверить, не зависит ли от нее что-то.
Идентификация влияющих ячеек
Вы можете идентифицировать ячейки, используемые формулой в активной ячейке, несколькими способами.
- Нажмите клавишу F2. Ячейки, используемые непосредственно в формуле, будут выделены цветом, а их цвет будет соответствовать ссылке на ячейку в формуле.
- Откройте диалоговое окно «Выбрать группу ячеек» (выберите «Главная», «Редактировать», «Найти» и выберите «Выбрать группу ячеек»). Установите переключатель в положение «Ячейки влияния», а затем в положение «Только прямое управление» или «Все уровни». Нажмите «ОК», и Excel выберет нужные ячейки для формулы.
- Нажмите Ctrl+, чтобы выбрать все непосредственно затрагивающие ячейки на текущем листе.
- Нажмите Ctrl+Shift+[, чтобы выбрать все влияющие ячейки (прямые и косвенные) на текущем листе.
- Выберите Формулы Зависимости формул, влияющие на ячейки, и Excel нарисует стрелки, указывающие на влияющие ячейки. Нажмите эту кнопку несколько раз, чтобы увидеть дополнительные уровни влияния. Щелкните Формулы Зависимости формул Удалить стрелки, чтобы скрыть стрелки.
Обратите внимание, что все эти методы ограничиваются идентификацией затронутых ячеек только на листе, содержащем формулу.
Идентификация зависимых ячеек
Вы можете определить ячейки с формулами, использующими конкретную ячейку, следующими способами.
- Откройте диалоговое окно «Выбор группы ячеек». Установите переключатель на зависимые ячейки, а затем только напрямую (чтобы найти непосредственно зависимые ячейки) или на все уровни (чтобы найти прямо и косвенно зависимые ячейки). Нажмите «ОК». Excel выберет ячейки, которые зависят от активной ячейки.
- Нажмите Ctrl+] для выбора всех непосредственно зависимых ячеек на текущем листе.
- Нажмите Ctrl+Shift[+], чтобы выбрать все зависимые ячейки (прямые и косвенные) на текущем листе.
- Выберите Формулы Зависимости формул Зависимые ячейки, и Excel нарисует стрелки, указывающие на зависимые ячейки. Нажмите кнопку несколько раз, чтобы увидеть дополнительные уровни влияния. Щелкните Формулы Зависимости формул Удалить стрелки, чтобы скрыть стрелки.
Опять же, эти методы ограничены идентификацией зависимых ячеек только на текущем листе.
Функция Получить.Ячейку
Задача: Вы хотите выбрать все ячейки на листе, которые не содержат формул.
Записка Багузина. Эту задачу можно решить довольно просто, если вы используете Excel 2013 или более позднюю версию. Примените функцию ФОРМУЛА (ссылка). Функция проверяет содержимое ячейки и возвращает ИСТИНА или ЛОЖЬ. Однако подход Билла Джелена интересен сам по себе, так как открывает окно в мир макрофункций (вероятно неизвестных большинству пользователей).
Решение. До появления VBA макросы писались в формате xlm (Excel Macro). В языке использовались макрофункции, то есть функции листа макросов Excel 4.0. Microsoft по-прежнему поддерживает этот язык для совместимости с более ранними версиями Excel (дополнительные сведения см в разделе Что такое макросы). Система макросов xlm унаследована от более ранних версий Excel (4.0 и более ранних). Более поздние версии Excel по-прежнему запускают макросы xlm, но, начиная с Excel 97, пользователи не имеют возможности записывать макросы xlm.
Язык xlm содержит, среди прочего, функцию Get.Cell (GET.CELL), которая предоставляет гораздо больше информации, чем современная функция CELL(). На самом деле Get.Cell может сообщить вам о 66 различных атрибутах ячейки, в то время как функция CELL возвращает только 12 параметров. Функция Get.Cell весьма полезна, за исключением одного «но»… Нельзя войти прямо в ячейку (рисунок 1).
Рис. 1. Функция Get.Cell недоступна для ввода в листе Excel
Скачать заметку в формате Word или pdf, примеры в формате Excel (с макросами)
Однако есть решение. Вы можете определить имя на основе функции, а затем ссылаться на это имя в любой ячейке. Например, чтобы узнать, содержит ли ячейка A1 формулу, введите =Get.Cell(48,A1). Здесь 48 — это аргумент, отвечающий за анализ того, является ли содержимое ячейки формулой. В более общем случае, когда вы хотите применить условное форматирование, используйте формулу =Получить.Ячейка(48,ДВССЫЛ(» RC » ,ЛОЖЬ)). Если вы не знакомы с функцией ДВССЫЛ, я предлагаю вам прочитать примеры функции ДВССЫЛ. Эта функция нужна нам для обозначения ссылки на ячейку, в которой мы сейчас находимся. Мы не можем указать какие-либо конкретные ячейки, поэтому используем ссылку в стиле R1C1, где RC означает ссылку относительно текущей ячейки. В стиле ссылок А1, чтобы обратиться к текущей ячейке, нам нужно было бы написать этот фрагмент формулы в форме = ДВССЫЛ (АДРЕС (СТРОКА(); СТОЛБЦ(); 4)). Дополнительные сведения см в разделе Зачем нужен стиль ссылок R1C1.
Чтобы использовать формулу =Get.Cell() для выбора ячеек с условным форматированием, выполните следующие действия (для Excel 2007 или более поздней версии):
- Чтобы определить новое имя, перейдите в ФОРМУЛА -> Присвоить имя. В открывшемся окне (рис. 2) выберите подходящее имя, например, IfFormula. В поле Формула введите =Get.Cell(48,ДВССЫЛ(» RC » ,ЛОЖЬ)). Нажмите «ОК». Щелкните Закрыть.
- Выберите ячейки, к которым вы хотите применить условное форматирование (рис. 3); в нашем примере это B3:B15.
- Перейдите на ГЛАВНУЮ -> Условное форматирование -> Создать правило. В открывшемся окне выберите Использовать формулу для определения отформатированных ячеек. В нижней половине диалогового окна введите =IfFormula, как показано на рис. 1. 3. Excel может автоматически добавлять кавычки =»IfFormula» забери их Нажмите кнопку «Формат», в открывшемся окне «Формат ячеек» перейдите на вкладку «Заливка» и выберите цвет заливки. Нажмите «ОК.
Рис. 2. Окно создания имени
Рис. 3. Создайте новое правило условного форматирования
Чтобы выделить ячейки, не содержащие формулы, используйте параметр формата =NO(IfFormula).
Будь осторожен. Иногда при копировании ячеек, содержащих формулу, на другой лист есть риск сбоя Excel (со мной такого никогда не случалось).
Альтернативные стратегии: Преимущество предыдущего метода в том, что формат будет обновляться автоматически каждый раз, когда кто-то меняет содержимое ячеек, заменяя формулы константами или наоборот. Если вам просто нужно получить один снимок ячеек, содержащих формулы, выполните следующие действия:
- Выделите все ячейки; для этого встаньте на одну из ячеек диапазона и нажмите Ctrl+A (A — это английский язык).
- Нажмите Ctrl+G, чтобы открыть окно перехода.
- В левом нижнем углу этого окна нажмите кнопку Выбрать.
- В открывшемся диалоговом окне Выбор группы ячеек выберите формулы, нажмите кнопку ОК.
- На вкладке ГЛАВНАЯ выберите цвет заливки, например красный.
Синтаксис функции: GET.CELL(тип_числа; ссылка). Полный список первого аргумента функции Get.Cell см., например, здесь. Обратите внимание, что в некоторых случаях функциональность современных версий Excel существенно изменилась, и функция не будет возвращать допустимое значение. Для некоторых аргументов числового типа удобнее использовать функцию ЯЧЕЙКА.
Некоторые примеры функции GET.CELL.
Type_number = 1. Абсолютная ссылка верхней левой ячейки аргумента ссылки в виде текста в текущем стиле: $A$1 или R1C1 (рис. 4). Проще использовать формулу =ЯЧЕЙКА(» адрес » ;ссылка)
Рис. 4. Определение адреса верхней левой ячейки диапазона
Type_number = 63. Возвращает номер цвета заливки ячейки (рис. 5).
Рис. 5. Определите номер цвета заливки ячейки
Функция ЯЧЕЙКА() в EXCEL
Функция CELL(), английская версия CELL(), возвращает информацию о формате, адресе или содержимом ячейки. Функция может возвращать подробную информацию о формате ячейки, что в некоторых случаях устраняет необходимость в VBA. Функция особенно полезна, если вам нужно отобразить полный путь к файлу в ячейках.
Синтаксис функции CELL()
CELL(detail_type, [ссылка])
info_type — текстовое значение, указывающее желаемый тип информации о ячейке. В следующем списке показаны возможные значения для аргумента info_type и соответствующие результаты.
ссылка — необязательный аргумент. Ячейка, о которой нужно получить информацию. Если этот аргумент опущен, информация, указанная в аргументе инфо-типа, возвращается для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА() возвращает информацию только для верхней левой ячейки диапазона.
Использование функции
Файл примера содержит основные примеры использования функции:
Большая часть информации о ячейке касается ее формата. Такой альтернативный источник информации может случиться только с VBA.
Наиболее интересными аргументами являются адрес и имя файла, которые позволяют быстро отобразить имя файла и путь в ячейке. Об этом читайте в статье Найти название текущей книги .
Обратите внимание, что если в экземпляре MS EXCEL открыто несколько рабочих книг (см примечание ниже), функция CELL() с аргументами адреса и имени файла вернет имя файла, из которого она была изменена в последний раз. Например, в окне MS EXCEL открыты 2 книги: Database.xlsx и Report.xlsx. В рабочей книге Database.xlsx есть формула =CELL(«filename») для отображения имени текущего файла в ячейке, т.е. Database.xlsx (с полным путем и листом, на котором эта формула включена). Если мы перейдем в окно рабочей книги Report.xlsx и изменим, например, содержимое ячейки, то вернувшись в окно рабочей книги Database.xlsx (CTRL+TAB), мы увидим, что ячейка с формулой =CELL(» имя файла «) содержит имя Report.xlsx. Это может быть источником ошибки. Хорошая новость заключается в том, что при открытии рабочей книги функция пересчитывает ее значение (вы также можете пересчитать рабочую книгу, нажав клавишу F9). При открытии файлов в разных экземплярах MS EXCEL такого эффекта не происходит, формула =ЯЧЕЙКА(«имя файла») вернет имя файла в той ячейке, в которую введена эта формула.
Примечание. Вы можете открыть несколько рабочих книг EXCEL в одном окне MS EXCEL (в одном экземпляре MS EXCEL) или в нескольких окнах MS EXCEL. Рабочие книги обычно открываются в экземпляре MS EXCEL (когда вы просто открываете их последовательно из проводника Windows или с помощью кнопки Office в окне MS EXCEL). Второй экземпляр MS EXCEL можно открыть, запустив файл EXCEL.EXE, например, через меню «Пуск». Чтобы убедиться, что файлы открыты в экземпляре MS EXCEL, последовательно нажмите комбинацию клавиш CTRL+TAB — будут отображены все окна рабочей книги, открытые в этом окне MS EXCEL. Для открытых книг в разных окнах MS EXCEL (экземпляры MS EXCEL) это сочетание клавиш не работает. Удобно открывать Книги в разных экземплярах, чьи расчеты занимают много времени. При изменении формул MS EXCEL пересчитывает только те книги, которые открыты в текущем экземпляре.
Другие возможности функции ЯЧЕЙКА(): определение типа значения, номера столбца или строки, мало востребованы, т.к дублируются стандартными функциями ETEXT(), ЧИСЛО(), СТОЛБЦ() и т.д.