Ячейка в экселе: Функция ЯЧЕЙКА в Excel и примеры ее использования

Excel 21. Ячейка. Вставка и удаление – Эффективная работа в MS Office

Вставить новый столбец, строку или ячейку в таблицу часто бывает проще, чем переместить данные, чтобы освободить место для новых данных.

По окончании этого урока вы сможете:

1. Вставлять строчки, столбцы, ячейки
2. Удалять строчки, столбцы, ячейки

Шаг 1. Для вставки строки выделяем строчку (щелкаем ЛМ по имени строчки – курсор принимает вид толстой стрелки) → контекстное меню ПМ → команда Вставить:

Шаг 2. Для вставки столбца выделяем строчку (щелкаем ЛМ по имени столбца – курсор принимает вид толстой стрелки) → контекстное меню ПМ → команда Вставить:

Получаем такую картину:

Понять и запомнить!

1. Новая строка вставляется ВЫШЕ выделенной строки 2. Новый столбец вставляется СЛЕВА от выделенного столбца

Шаг 3. Чтобы вставить несколько столбцов или строк, выделяем то число столбцов или строк, которое нужно вставить → контекстное меню ПМ → команда Вставить:

Я выделила 4 строчки → контекстное меню ПМ → команда Вставить. А вот результат:

То же самое можно сделать для столбцов.

Иногда возникает необходимость вставить одну ячейку.

Шаг 4. Для вставки ячейки выделяем или ячейку (ячейки) ЛМ → контекстное меню ПМ→ команда Вставить.

Шаг 5. Маленькое диалоговое окно «Добавление ячеек» позволяет нам определить, куда будет вставляться новая ячейка. Оставляем по умолчанию:

Результат:

Весь столбец с данными сместился вниз – образовалась пустая ячейка D2.

Если выделить несколько ячеек, а затем дать команду «Вставить», то мы получим новые ячейки, количество которых будет равно выделенным ячейкам:

Я выделила 4 ячейки, дала команду «Вставить» и получила диапазон из4-х ячеек E2:E5.

Команды вставки столбцов, строчек, ячеек дублируются на ленте Главная → группа команд Ячейки → команда Вставить → команды выпадающего меню:

Использование команд контекстного меню довольно значительно экономит рабочее время, поэтому я показала именно этот способ.

Шаг 6. Для удаления столбца (или строчки) выделяем столбец (или строчку) (щелкаем ЛМ по имени строчки – курсор принимает вид толстой стрелки → контекстное меню ПМ → команда Вставить):

Шаг 7. Для удаления ячейки выделяем или ячейку (ячейки) ЛМ → контекстное меню ПМ→ команда Удалить.

Шаг 8. Маленькое диалоговое окно «Удаление ячеек» позволяет нам определить, в каком направлении произойдет сдвиг столбца за счет удаляемой ячейки:

В нашем случае – «ячейка со сдвигом вверх».

Команды удаления столбцов, строчек, ячеек дублируются на ленте Главная → группа команд Ячейки → команда Вставить → команды выпадающего меню:

Понять и запомнить!

Вставлять или удалять столбцы, строчки, ячейки можно только командами контекстного меню или командами лента Главная → группа команд Ячейки → команды Вставить или Удалить Кнопка клавиатуры Delete удаляет содержимое выделенных ячеек:

Небольшое видео по теме:

Теперь вы сможете:

1. Вставлять строчки, столбцы, ячейки
2. Удалять строчки, столбцы, ячейки

описание, что представляет собой, как включить, для чего служит

Содержание:

• Ячейка в Microsoft Excel – базовые понятия
• Как включить ячейки в Excel
• Работа с ячейками в Эксель
  • Выделение отдельных ячеек и диапазона
  • Поиск ячеек по содержимому
  • Объединение ячеек
  • Разделение ячеек
  • Переименование ячеек
  • Форматирование ячеек
  • Арифметические операции с ячейками

Содержание

• Ячейка в Microsoft Excel – базовые понятия
• Как включить ячейки в Excel
• Работа с ячейками в Эксель
  • Выделение отдельных ячеек и диапазона
  • Поиск ячеек по содержимому
  • Объединение ячеек
  • Разделение ячеек
  • Переименование ячеек
  • Форматирование ячеек
  • Арифметические операции с ячейками

Ячейка в Microsoft Excel – базовые понятия

Определение

Microsoft Excel — программа для работы с электронными таблицами, созданная корпорацией Microsoft для Microsoft Windows, Windows NT и Mac OS, а также Android, iOS и Windows Phone.

Ячейка MS Excel служит для хранения и обработки всех данных, введенных пользователем.

Чтобы понять, что представляет собой ячейка в MS Excel достаточно посмотреть, как она образуется, а именно — пересечением строки и столбца. За счет этого ячейка получает свое название, которое одновременно является ее координатами. Оно состоит из номеров столбца и строки. Для удобства работы имя ячейки можно сменить, координаты при этом останутся прежними.

Осторожно! Если преподаватель обнаружит плагиат в работе, не избежать крупных проблем (вплоть до отчисления). Если нет возможности написать самому, закажите тут.

Рисунок 1. Ячейка Exel

Как включить ячейки в Excel

Ячейки по определению являются базовыми элементами документа в Эксель. Соответственно, чтобы начать работу с ними, необходимо этот файл создать. В версии MS Excel 2016 года формат документа можно выбирать из готовых шаблонов. Для создания документа необходимо выбрать один из макетов и кликнуть по нему.

Рисунок 2. Создание документа в MS Excel

Работа с ячейками в Эксель

Важно понимать разницу между работой с самими ячейками MS Excel и операциями над данными, которые в них хранятся. Вторая тема гораздо более обширна, содержит в себе массу особенностей. В этом материале речь пойдет о работе непосредственно с ячейками и затронет только базовые арифметические действия с данными.

Выделение отдельных ячеек и диапазона

Самый простой и очевидный способ выделения нужной ячейки в экселе — по клику курсора. Если же таблица объемна и содержит в себе много строк и столбцов, листать ее вручную не слишком удобно. Чтобы быстро перейти к нужной ячейке, достаточно вписать ее координаты в поле «Имя», которое находится в левой части окна над таблицей, и нажать клавишу «Enter». После этого нужный участок становится активным.

Рисунок 3. Выделение ячейки и окно «Имя»

Выделение нескольких ячеек также производится при помощи мыши, либо через поле «Имя». Для первого метода необходимо зажать левую клавишу мыши и обвести требуемую область. Во втором случае в поле вводится диапазон от одной ячейки до другой через двоеточие. Например, «B2:E7».

Рисунок 4. Выделение диапазона ячеек

Поиск ячеек по содержимому

Чтобы найти ячейку, не зная ее координат можно воспользоваться поиском по данным, находящимся в ней. Это можно сделать через панель инструментов на вкладке «Главная» или с помощью стандартного сочетания клавиш «Ctrl» + «F».

Рисунок 5. Поиск ячеек по содержимому Рисунок 6. Окно поиска

Если содержащих искомую информацию ячеек несколько, при нажатии на кнопку «Найти все» система выдаст список всех ячеек с данным значением.

Рисунок 7. Поиск нескольких ячеек с одинаковым содержимым

Объединение ячеек

В MS Excel есть три способа объединения ячеек: объединение с переносом данных в центр, объединение по строкам и обычное объединение ячеек без изменения положения содержимого. Все они находятся в панели инструментов на вкладке «Главная».

Рисунок 8. Объединение ячеек

Отличия способов объединения:

Рисунок 9. Объединить и поместить в центре Рисунок 10. Объединить по строкам Рисунок 11. Объединить ячейки

Разделение ячеек

Функция разделения ячеек находится в том же блоке, что и объединение. Для отмены слияния не обязательно выбирать функцию из списка, достаточно кликнуть на обозначенный значок.

Переименование ячеек

Смена названия ячейки может использоваться для пояснения того, какие данные в ней содержаться. Переименование производится через поле «Имя» в левой верхней части окна.

Рисунок 12. Смена названия ячейки

Форматирование ячеек

Для получения доступа к форматированию ячеек необходимо кликнуть правой кнопкой мыши в главном рабочем поле и выбрать из всплывающего меню «Формат ячеек».

Рисунок 13. Форматирование ячеек

Форматирование ячеек в Excel можно условно представить в виде трех основных блоков:

• формат записи и отображения данных;
• формат текста;
• внешний вид.

Блок «Формат записи и отображения данных» находится на вкладке «Число» в меню «Формат ячеек». Исходя из выбранного формата, программа обрабатывает и отображает данные по-разному. В большинстве случаев для работы достаточно трех форматов: общего, числового и текстового.

Рисунок 14. Формат данных, содержащихся в ячейке

Блок «Формат текста» находится на вкладках «Выравнивание» и «Шрифт». В первой настраивается расположение данных внутри ячейки, а именно: отступы от краев, особенности переноса слов и угол наклона текста.

На второй вкладке определяется, как будет выглядеть сам текст: его шрифт, цвет, толщина, размер символов и другие детали.

Рисунок 15. Формат текста в ячейках

Блок «Внешний вид» находится на вкладках «Граница» и «Заливка» настраивается то, как будут выглядеть сами ячейки.

Рисунок 16. Изменение внешнего вида ячеек

Рассматривать функционал последней вкладки в меню «Формат ячеек» не имеет смысла в отрыве от настроек защиты данных, содержащихся в документе.

Арифметические операции с ячейками

Все вычисления, включая арифметические действия, в MS Excel проводятся в окне формул.

Примечание

Запись всех операций должна начинаться со знака «=».

Выглядит это следующим образом:

Рисунок 17. Запись арифметического действия в окно формул

Для примера возьмем операцию сложения. В этом случае последовательность действий будет следующая:

• выделить ячейку, в которой будет храниться результат операции;
• перейти в окно формул;
• начать запись со знака «=»;
• выделить ячейку, содержащую первое слагаемое;
• поставить знак «+» и выделить ячейку со вторым слагаемым;
• нажать клавишу «Enter».

Функционал Эксель не ограничивается базовыми математическими операциями. В программу встроено огромное количество функций: экономических, статистических, инженерных и так далее. Кроме того, программа способна обрабатывать матрицы, массивы, текстовые строки и многое другое. Окно выбора функций содержит краткое описание для каждой из них.

Рисунок 18. Встроенные функции MS Excel

Насколько полезной была для вас статья?

У этой статьи пока нет оценок.

Выделите текст и нажмите одновременно клавиши «Ctrl» и «Enter»

Поиск по содержимому

Что такое ячейка в Excel

следующий → ← предыдущая Ячейка является неотъемлемой частью MS-Excel. Это объект рабочих листов Excel. Всякий раз, когда вы открываете Excel, рабочий лист Excel содержит ячейки для хранения информации в них. Вы вводите контент и свои данные в эти ячейки. Ячейки являются строительными блоками рабочего листа Excel. Таким образом, вы должны знать каждый пункт об этом. На листе Excel ячейка представляет собой прямоугольник прямоугольной формы. Это небольшая единица электронной таблицы Excel. На листе Excel около 17 миллиардов ячеек, которые объединены горизонтальными и вертикальными линиями. Рабочий лист Excel содержит ячейки в строках и столбцах. Строки обозначаются числами, а столбцы — буквами. Это означает, что строки идентифицируются цифрами, а столбцы — алфавитами. Какие данные могут входить в ячейку Excel состоит из группы ячеек на рабочем листе. Вы можете вводить данные в любую из этих ячеек. Excel позволяет пользователю вводить данные любого типа в ячейки Excel, такие как числовые, текстовые данные, данные о дате и времени. Все, что вы вводите в ячейку, отображается внутри ячейки, а также в строке формул. Дважды нажмите на любую ячейку, чтобы сделать ее редактируемой, и запишите в нее данные. В Excel вы можете вводить данные любого типа в ячейки Excel, такие как число, строка, текст, дата, время и т. д. Кроме того, пользователи также могут выполнять над ними операции. Как определить номер сотового? В Excel вы можете легко определить номер ячейки, в которой вы сейчас находитесь. Вы можете найти номер ячейки в поле имени или также в заголовках строк и столбцов. Выделенная строка и столбец в заголовке — это номер ячейки, когда ячейка выбрана. Смотрите скриншот ниже: В противном случае просмотрите номер ячейки в поле Имя текущей выбранной ячейки и получите номер ячейки, например, D15. Введите данные в ячейку Чтобы ввести данные/информацию в ячейку, дважды нажмите на любую ячейку, чтобы сделать ее редактируемой, и запишите в нее данные. Давайте разберемся на примере. Удалить данные ячейки Выберите ячейку вместе с данными внутри нее и нажмите кнопку Backspace или Удалить , чтобы удалить содержимое ячейки. Он будет удалять по одной букве за раз, что означает, что 1 нажатие назад / удаление удалит только одну букву этой ячейки. Вы также можете удалить данные ячейки за один раз. Для этого выберите данные ячейки, а затем нажмите кнопку Backspace или Удалить . Содержимое выбранной ячейки будет удалено. Вы также можете использовать эту кнопку Удалить для удаления содержимого нескольких ячеек. Для этого вам нужно выбрать ячейки с данными, данные которых вы хотите удалить, и нажать клавишу Удалить на клавиатуре. Данные выбранных ячеек будут удалены. Удалить ячейку Существует огромная разница между удалением данных ячейки и удалением самой ячейки. Так что не путайте их. Чтобы удалить ячейки, вам нужно выполнить немного другие шаги, которые мы обсуждаем ниже: Шаг 1: Выберите одну или несколько ячеек, которые вы хотите удалить. Например, A3, A4 и B3, B4. Шаг 2: Щелкните правой кнопкой мыши любую из выбранных ячеек и выберите команду Удалить , присутствующую в списке. Шаг 3: Отметьте соответствующий переключатель и нажмите кнопку OK . Мы выбрали опцию Shift Cells Up , чтобы сместить оставшиеся данные ячеек выбранного столбца в верхнюю строку. Шаг 4: Выбранные ячейки будут удалены, а оставшиеся ячейки сместятся вверх на месте удаленных ячеек. Диапазон ячеек Диапазон ячеек — один, имеющий начальную и конечную точки. Когда в последовательности выбрано несколько ячеек, она называется 9.0022 диапазон ячеек . Диапазон ячеек отображается от начальной до конечной ячейки. Выбранные ячейки должны располагаться последовательно без пробелов в выделении. Например, , Диапазон ячеек A1:A8 В этом диапазоне выбрана ячейка от A1 до A8. Это означает, что всего выбрано 8 ячеек. Диапазон ячеек A1:B8 В этом диапазоне ячеек выбраны ячейки от A1 до A8 и от B1 до B8. Это означает, что всего выбрано 16 ячеек. Как выделить несколько ячеек Иногда необходимо выбрать большой диапазон данных ячеек на листе Excel. Вы можете легко выбрать большую группу ячеек или диапазон ячеек двумя способами. Либо мышкой, либо шифтом и клавишей со стрелкой. 1. Продолжение выбора Во-первых, мы покажем вам непрерывное выделение нескольких ячеек, используя оба метода. Выбор ячеек мышью Нажмите на ячейку, удерживайте левую клавишу мыши и перетащите, пока не выделите все нужные ячейки. Выберите ячейки с помощью Shift и клавиши со стрелкой Есть еще один способ выделения нескольких ячеек одновременно. Вы можете использовать клавишу Shift с клавишами со стрелками (выбрать направление) для выбора нескольких ячеек. Сначала щелкните одну ячейку на листе Excel. Продолжайте нажимать клавишу Shift и используйте необходимую клавишу со стрелкой в ​​соответствии с выбором, чтобы выбрать несколько ячеек. 2. Разбросанный выбор Excel также позволяет выбирать несколько ячеек из разных строк и столбцов, не выполняя какой-либо процесс непрерывного выбора, как указано выше. Мы можем сделать это только с помощью клавиши Ctrl. Выберите разрозненные ячейки с помощью клавиши CTRL В Excel можно выделить две или более ячеек из разных строк и разных столбцов. Вы можете использовать клавишу CTRL , чтобы удерживать выделение, а затем выбрать ячейки для выбора. Помните, что будут выбраны только те ячейки, в которых есть данные. Пустые ячейки нельзя выделить даже с помощью клавиши Ctrl. Вырезать, копировать и вставлять данные ячеек Вырезание, копирование и вставка — наиболее часто используемые операции любого инструмента. Excel позволяет своим пользователям копировать или вырезать содержимое из одного места и вставлять его в другую ячейку Excel. Excel также предоставляет команды быстрого доступа для этих операций. В Excel используется CTRL + C для копирования, CTRL + P для вставки скопированного содержимого и CTRL + X для вырезания. Эти сочетания клавиш одинаковы практически для всех инструментов. Скопируйте и вставьте данные ячейки Шаг 1: Выберите ячейку, данные которой вы хотите скопировать, и нажмите CTRL+C, чтобы скопировать данные. Шаг 2: Теперь перейдите туда, куда вы хотите вставить скопированные данные, и нажмите CTRL+P команда быстрого доступа для размещения данных. Шаг 3: Ваши данные были скопированы из одной ячейки и вставлены в другую. Вырезать и вставить данные ячейки Шаг 1: Выберите ячейку, данные которой вы хотите вырезать, и нажмите команду CTRL+X . Шаг 2: Теперь перейдите в ячейку, в которую вы хотите вставить данные вырезания, и нажмите команду быстрого доступа CTRL+P , чтобы поместить туда данные. Шаг 3: Ваши данные были помещены из одной ячейки и вставлены в другую. Как увеличить размер ячейки В Excel вы можете увеличить размер ячеек следующими способами: Увеличить высоту строки от заголовка строки Увеличить ширину столбца из заголовка столбца Объедините две или более ячеек, чтобы увеличить размер ячейки Увеличьте размер шрифта, чтобы увеличить размер ячейки При необходимости вы можете использовать любой из этих методов. Следующая темаИспользование MS Excel ← предыдущая следующий →

зачем использовать $ в формуле Excel

При написании формулы Excel $ в ссылках на ячейки смущает многих пользователей. Но объяснение очень простое. Знак доллара в ссылке на ячейку Excel служит только одной цели — он сообщает Excel, следует ли изменять ссылку, когда формула копируется в другие ячейки. И этот краткий учебник содержит полную информацию об этой замечательной функции.

Значение ссылки на ячейку Excel трудно переоценить. Получите представление о разнице между абсолютными, относительными и смешанными ссылками, и вы на полпути к овладению мощью и универсальностью формул и функций Excel.

Все вы, наверное, видели знак доллара ($) в формулах Excel и задавались вопросом, что это такое. Действительно, вы можете ссылаться на одну и ту же ячейку четырьмя различными способами, например, A1, $A$1, $A1 и A$1.

Знак доллара в ссылке на ячейку Excel влияет только на одну вещь — он указывает Excel, как обрабатывать ссылку, когда формула перемещается или копируется в другие ячейки. Короче говоря, использование знака $ перед координатами строки и столбца создает абсолютную ссылку на ячейку, которая не изменится. Без знака $ ссылка является относительной и будет меняться.

Если вы пишете формулу для одной ячейки, вы можете использовать любой тип ссылки и получить правильную формулу в любом случае. Но если вы собираетесь копировать формулу в другие ячейки, выбор соответствующего типа ссылки на ячейку имеет решающее значение. Если вам повезет, вы можете бросить монетку 🙂 Если вы хотите быть серьезным, потратьте несколько минут на изучение всех тонкостей абсолютных и относительных ссылок на ячейки в Excel и того, когда какую из них использовать.

• Что такое ссылка на ячейку Excel
• Относительная ссылка на ячейку
• Абсолютная ссылка на ячейку
• Использование относительных и абсолютных ссылок на ячейки в одной формуле
• Ссылка на смешанную ячейку в Excel
• Ссылки на весь столбец и всю строку
• Переключение между различными типами ссылок (клавиша F4)

Что такое ссылка на ячейку Excel?

Проще говоря, ссылка на ячейку в Excel — это адрес ячейки. Он сообщает Microsoft Excel, где искать значение, которое вы хотите использовать в формуле.

Например, если вы введете простую формулу =A1 в ячейку C1, Excel вытянет значение из ячейки A1 в C1:

Как уже упоминалось, пока вы пишете формулу для одной ячейки , вы можно использовать любой тип ссылки, со знаком доллара ($) или без него, результат будет таким же:

вы выбираете правильный тип ссылки, чтобы формула правильно копировалась в другие ячейки. В следующих разделах приведены подробные пояснения и примеры формул для каждого типа ссылки на ячейку.

Примечание. Помимо стиля ссылок A1 , в котором столбцы определяются буквами, а строки цифрами, существует также стиль ссылок R1C1 , в котором строки и столбцы обозначаются цифрами (R1C1 обозначает строку 1, столбец 1).

Поскольку A1 является стилем ссылок по умолчанию в Excel и используется большую часть времени, в этом руководстве мы будем обсуждать только ссылки типа A1. Если кто-то в настоящее время использует стиль R1C1, вы можете отключить его, нажав Файл > Параметры > Формулы , а затем снимите флажок Справочный стиль R1C1 .

Относительная ссылка на ячейку Excel (без знака $)

Относительная ссылка в Excel — это адрес ячейки без знака $ в координатах строки и столбца, например A1 .

Когда формула с относительными ссылками на ячейки копируется в другую ячейку, ссылка изменяется в зависимости от относительного положения строк и столбцов. По умолчанию все ссылки в Excel являются относительными. В следующем примере показано, как работают относительные ссылки.

Предположим, у вас есть следующая формула в ячейке B1:

=A1*10

Если вы скопируете эту формулу в другую строку в том же столбце, скажем, в ячейку B2, формула будет скорректирована для строки 2 ( A2*10), потому что Excel предполагает, что вы хотите умножить значение в каждой строке столбца A на 10.

Если вы скопируете формулу с относительной ссылкой на ячейку в другой столбец в той же строке, Excel изменит ссылка столбца соответственно:

И если вы скопируете или переместите формулу Excel с относительной ссылкой на ячейку в другую строку и другой столбец , то ссылки на столбцы и строки изменятся:

Как видите, использование относительных ссылок на ячейки в формулах Excel — это очень удобный способ выполнения одних и тех же вычислений по всему рабочему листу. Чтобы лучше проиллюстрировать это, давайте обсудим пример из реальной жизни.

Использование относительной ссылки в Excel — пример формулы

Предположим, у вас есть столбец с ценами в долларах США (столбец B) на вашем рабочем листе, и вы хотите преобразовать их в евро. Зная курс конвертации доллара США в евро (0,93 на момент написания), формула для строки 2 будет такой же простой, как =B2*0,93 . Обратите внимание, что мы используем относительную ссылку на ячейку Excel без знака доллара.

При нажатии клавиши Enter будет рассчитана формула, и результат сразу появится в ячейке.

Наконечник. По умолчанию все ссылки на ячейки в Excel являются относительными ссылками. Таким образом, при написании формулы вы можете добавить относительную ссылку, щелкнув соответствующую ячейку на листе, вместо того, чтобы вводить ссылку на ячейку вручную.

Для скопируйте формулу вниз по столбцу , наведите указатель мыши на маркер заполнения (небольшой квадрат в правом нижнем углу выделенной ячейки). При этом курсор изменится на тонкий черный крест, и вы удерживаете и перетаскиваете его по ячейкам, которые хотите заполнить автоматически.

Вот оно! Формула копируется в другие ячейки с относительными ссылками, правильно настроенными для каждой отдельной ячейки. Чтобы убедиться, что значение в каждой ячейке вычисляется правильно, выберите любую из ячеек и просмотрите формулу в строке формул. В этом примере я выбрал ячейку C4 и вижу, что ссылка на ячейку в формуле относится к строке 4, как и должно быть:

Абсолютная ссылка на ячейку Excel (со знаком $)

Абсолютная ссылка в Excel — это адрес ячейки со знаком доллара ($) в координатах строки или столбца, например $A$1 .

Знак доллара фиксирует ссылку на данную ячейку, так что она остается неизменной независимо от того, куда перемещается формула. Другими словами, использование $ в ссылках на ячейки позволяет скопировать формулу в Excel без изменения ссылок.

Например, если у вас есть 10 в ячейке A1 и вы используете абсолютная ссылка на ячейку ( $A$1 ), формула =$A$1+5 всегда будет возвращать 15, независимо от того, в какие другие ячейки копируется эта формула. С другой стороны, если вы напишите ту же формулу с относительной ссылкой на ячейку ( A1 ), а затем скопируете ее в другие ячейки в столбце, для каждой строки будет рассчитано другое значение. На следующем рисунке показана разница:

Примечание. Хотя мы говорили, что абсолютная ссылка в Excel никогда не меняется, на самом деле она меняется, когда вы добавляете или удаляете строки и/или столбцы на листе, и это изменяет расположение ячейки, на которую указывает ссылка. В приведенном выше примере, если мы вставим новую строку вверху листа, Excel достаточно умен, чтобы изменить формулу, чтобы отразить это изменение:

В реальных рабочих листах очень редко используются только абсолютные ссылки в формуле Excel. Однако существует множество задач, требующих использования как абсолютных, так и относительных ссылок, как показано в следующих примерах.

Примечание. Абсолютную ссылку на ячейку не следует путать с абсолютным значением, которое представляет собой величину числа без учета его знака.

Использование относительных и абсолютных ссылок на ячейки в одной формуле

Довольно часто вам может понадобиться формула, в которой некоторые ссылки на ячейки корректируются для столбцов и строк, в которые копируется формула, а другие остаются фиксированными для определенных ячеек. Другими словами, вы должны использовать относительные и абсолютные ссылки на ячейки в одной формуле.

Пример 1. Относительная и абсолютная ссылки на ячейки для расчета чисел

В нашем предыдущем примере с ценами в долларах США и евро вы можете не захотеть жестко указывать обменный курс в формуле. Вместо этого вы можете ввести это число в какую-нибудь ячейку, скажем, C1, и исправить ссылку на эту ячейку в формуле, используя знак доллара ($), как показано на следующем снимке экрана:

В этой формуле (B4*$C$1) , существует два типа ссылок на ячейки:

• B4 — относительный ссылка на ячейку, которая корректируется для каждой строки, и
• $C$1 — абсолютная ссылка на ячейку , которая никогда не изменяется независимо от того, куда копируется формула.

Преимущество этого подхода заключается в том, что ваши пользователи могут рассчитывать цены в евро на основе переменного обменного курса без изменения формулы. После изменения коэффициента конверсии все, что вам нужно сделать, это обновить значение в ячейке C1.

Пример 2. Относительные и абсолютные ссылки на ячейки для расчета дат

Другим распространенным использованием абсолютных и относительных ссылок на ячейки в одной формуле является расчет дат в Excel на основе сегодняшней даты.

Предположим, у вас есть список дат доставки в столбце B, и вы вводите текущую дату в C1 с помощью функции TODAY(). Что вам нужно знать, так это то, через сколько дней будет доставлен каждый товар, и вы можете рассчитать это, используя следующую формулу: =B4-$C$1

И снова мы используем два типа ссылок в формуле:

• Относительный для ячейки с первой датой доставки (B4), поскольку вы хотите, чтобы эта ссылка на ячейку менялась в зависимости от строки, в которой находится формула.
• Абсолютный для ячейки с сегодняшней датой ($C$1), потому что вы хотите, чтобы эта ссылка на ячейку оставалась постоянной.

Подводя итог, всякий раз, когда вы хотите создать статическую ссылку на ячейку Excel, которая всегда ссылается на одну и ту же ячейку, не забудьте включить в формулу знак доллара ($), чтобы создать абсолютную ссылку в Excel.

Ссылка на смешанную ячейку Excel

Ссылка на смешанную ячейку в Excel — это ссылка, в которой фиксирована либо буква столбца, либо номер строки. Например, $A1 и A$1 являются смешанными ссылками. Но что означает каждый? Это очень просто.

Как вы помните, абсолютная ссылка Excel содержит 2 знака доллара ($), которые блокируют столбец и строку. В смешанной ссылке на ячейку только одна координата является фиксированной (абсолютной), а другая (относительная) будет изменяться в зависимости от относительного положения строки или столбца:

• Абсолютный столбец и относительная строка , например $A1. Когда формула с этим типом ссылки копируется в другие ячейки, знак $ перед буквой столбца блокирует ссылку на указанный столбец, чтобы она никогда не менялась. Относительная ссылка на строку без знака доллара зависит от строки, в которую копируется формула.
• Относительный столбец и абсолютная строка , например A$1. В этом типе ссылки ссылка на строку не изменится, а ссылка на столбец изменится.

Ниже вы найдете пример использования обоих смешанных типов ссылок на ячейки, который, как мы надеемся, упростит понимание.

Использование смешанной ссылки в Excel — пример формулы

В этом примере мы снова будем использовать нашу таблицу конвертации валюты. Но в этот раз мы не будем ограничиваться только конвертацией доллара в евро. Что мы собираемся сделать, так это преобразовать цены в долларах в ряд других валют, используя единую формулу 9.0258 !

Для начала введем коэффициенты конверсии в какую-нибудь строку, скажем, строку 2, как показано на скриншоте ниже. А затем вы пишете только одну формулу для верхней левой ячейки (C5 в этом примере) для расчета цены в евро:

=$B5*C$2

Где $B5 — цена в долларах в той же строке, а C$2 — курс конвертации доллара США в евро.

А теперь скопируйте формулу в другие ячейки столбца C и после этого автоматически заполните другие столбцы той же формулой, перетащив маркер заполнения. В результате у вас будет 3 разных столбца цен, рассчитанных правильно на основе соответствующего обменного курса в строке 2 того же столбца. Чтобы убедиться в этом, выберите любую ячейку в таблице и просмотрите формулу в строке формул.

Например, выберем ячейку D7 (в столбце GBP). Здесь мы видим формулу =$B7*D$2 , которая берет цену в долларах США в B7 и умножает ее на значение в D2, которое представляет собой курс обмена USD-GBP, как раз то, что доктор прописал 🙂

А теперь давайте разберемся, как получается, что Excel точно знает, какую цену брать и на какой курс ее умножать. Как вы могли догадаться, это смешанные ссылки на ячейки делают свое дело ($B5*C$2).

• $B5 — абсолютный столбец и относительная строка . Здесь вы добавляете знак доллара ($) только перед буквой столбца, чтобы привязать ссылку к столбцу A, поэтому Excel всегда использует исходные цены в долларах США для всех преобразований. Ссылка на строку (без знака $) не заблокирована, поскольку вы хотите рассчитать цены для каждой строки отдельно.
• C$2 — относительный столбец и абсолютная строка . Поскольку все обменные курсы находятся в строке 2, вы блокируете ссылку на строку, помещая знак доллара ($) перед номером строки. И теперь, независимо от того, в какую строку вы скопируете формулу, Excel всегда будет искать обменный курс в строке 2. А поскольку ссылка на столбец является относительной (без знака $), она будет скорректирована для столбца, к которому относится формула. скопировано.

Как сослаться на весь столбец или строку в Excel

При работе с рабочим листом Excel с переменным количеством строк может потребоваться ссылка на все ячейки в определенном столбце. Чтобы сослаться на весь столбец, просто дважды введите букву столбца и двоеточие между ними, например, A:A .

Ссылка на весь столбец

Как и ссылки на ячейки, ссылка на весь столбец может быть абсолютной и относительной, например:

• Абсолютная ссылка на столбец , как $A:$A
• Относительная ссылка на столбец, например A:A

И снова вы используете знак доллара ($) в абсолютной ссылке столбца , чтобы привязать ее к определенному столбцу, чтобы ссылка всего столбца не менялась при копировании формулы в другие ячейки.

Относительная ссылка столбца изменится, когда формула будет скопирована или перемещена в другие столбцы, и останется неизменной, когда вы скопируете формулу в другие ячейки в том же столбце.

Ссылка на всю строку

Чтобы сослаться на всю строку, вы используете тот же подход, за исключением того, что вы вводите номера строк вместо букв столбцов:

• Абсолютная ссылка на строку , как $1:$1
• Относительная ссылка на строку, например 1:1

Теоретически можно также создать смешанную ссылку на весь столбец или смешанную полную ссылку — на строку, как $A:A или $1:1 соответственно. Я говорю «теоретически», потому что не могу придумать никакого практического применения таких ссылок, хотя пример 4 доказывает, что формулы с такими ссылками работают именно так, как и предполагалось.

Пример 1. Ссылка на весь столбец Excel (абсолютная и относительная)

Предположим, у вас есть несколько чисел в столбце B, и вы хотите узнать их общее и среднее значение. Проблема в том, что каждую неделю в таблицу добавляются новые строки, поэтому писать обычную формулу СУММ() или СРЗНАЧ() для фиксированного диапазона ячеек не получится. Вместо этого вы можете ссылаться на весь столбец B:

=СУММ($B:$B) — используйте знак доллара ($), чтобы сделать абсолютной ссылкой на весь столбец , который блокирует формулу в столбце B.

=СУММ(B:B) — напишите формулу без $, чтобы сделать относительную ссылку на весь столбец , которая изменится, когда вы скопируете формулу в другой столбцы.

Совет. При написании формулы щелкните букву столбца, чтобы в формулу была добавлена ​​ссылка на весь столбец. Как и в случае со ссылками на ячейки, Excel по умолчанию вставляет относительную ссылку (без знака $):

Таким же образом мы пишем формулу для расчета средней цены во всем столбце B:

=СРЗНАЧ(B:B)

В этом примере мы используем относительную ссылку на весь столбец, поэтому наша формула правильно корректируется, когда мы копируем ее в другие столбцы:

Примечание. При использовании ссылки на весь столбец в формулах Excel никогда не вводите формулу где-либо в пределах одного столбца. Например, может показаться хорошей идеей ввести формулу =СУММ(B:B) в одну из самых нижних пустых ячеек в столбце B, чтобы получить итог в конце того же столбца. Не делай этого! Это создаст так называемую циклическая ссылка , и формула вернет 0.

Пример 2. Ссылка на всю строку Excel (абсолютная и относительная)

Если данные в вашем листе Excel организованы в строки, а не в столбцы, вы можете ссылаться на всю строку в вашей формуле. Например, вот как мы можем рассчитать среднюю цену в строке 2:

=СРЗНАЧ($2:$2) — абсолютная ссылка на всю строку привязана к определенной строке с помощью знака доллара ( $).

=СРЗНАЧ(2:2) — относительная ссылка на всю строку изменится при копировании формулы в другие строки.

В этом примере нам нужна относительная ссылка на всю строку, потому что у нас есть 3 строки данных, и мы хотим вычислить среднее значение в каждой строке, скопировав одну и ту же формулу:

Пример 3. Как сослаться на весь столбец исключение нескольких первых строк

Это очень актуальная проблема, потому что очень часто первые несколько строк в рабочей таблице содержат вводную или пояснительную информацию, и вы не хотите включать их в свои расчеты. К сожалению, Excel не поддерживает ссылки типа B5:B, которые включали бы все строки в столбце B, начиная со строки 5. Если вы попытаетесь добавить такую ​​ссылку, ваша формула, скорее всего, вернет ошибку #ИМЯ.

Вместо этого вы можете указать максимальную строку , чтобы ваша ссылка включала все возможные строки в данном столбце. В Excel 2016, 2013, 2010 и 2007 максимальное количество строк составляет 1 048 576 и 16 384 столбца. Более ранние версии Excel имеют максимальное количество строк 65 536 и максимальное количество столбцов 256.

Таким образом, чтобы найти среднее значение для каждого столбца цен в таблице ниже (столбцы B–D), вы вводите следующую формулу в ячейку F2, а затем копируете в ячейки G2 и h3:

=СРЗНАЧ(B5:B1048576)

Если вы используете функцию СУММ, вы также можете вычесть строки, которые хотите исключить:

=СУММ(В:В)-СУММ(В1:В4)

Пример 4. Использование смешанного целого -ссылка на столбец в Excel

Как я упоминал несколькими абзацами ранее, вы также можете создать смешанную ссылку на весь столбец или на всю строку в Excel:

• Смешанную ссылку на столбец, например $A:A
• Смешанная ссылка на строку, например $1:1

Теперь посмотрим, что произойдет, если скопировать формулу с такими ссылками в другие ячейки. Предположим, вы вводите формулу =СУММ($B:B) в какой-то ячейке, F2 в этом примере. Когда вы копируете формулу в соседнюю правую ячейку (G2), она изменяется на =СУММ($B:C) , потому что первая B фиксируется знаком $, а вторая — нет. В результате формула суммирует все числа в столбцах B и C. Не уверен, что это имеет какое-то практическое значение, но вам может быть интересно узнать, как это работает:

Предостережение! Не используйте слишком много полных ссылок на столбцы/строки на листе, так как они могут замедлить работу Excel.

Как переключаться между абсолютными, относительными и смешанными ссылками (клавиша F4)

Когда вы пишете формулу Excel, знак $, конечно, можно ввести вручную, чтобы изменить относительную ссылку на ячейку на абсолютную или смешанную. Или вы можете нажать клавишу F4, чтобы ускорить процесс. Для работы сочетания клавиш F4 вы должны находиться в режиме редактирования формулы:

1. Выберите ячейку с формулой.