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

Excel 4. Содержимое ячеек – Эффективная работа в MS Office

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

1. Делать первые рабочие операции по форматированию таблицы
2. Находить нужную ячейку
3. Вводить данные в ячейки и редактировать содержимое ячеек
4. Делать поиск и замену текста.

Скачайте таблицу Excel тут. Это простенькая табличка, но для начала сойдёт.

1. Первые операции по форматированию таблицы

С чего начинать работу в Excel? Посмотрите на таблицу: слова не помещаются в самих ячейках. Можно, конечно, поработать курсором изменения ширины столбца:

Но в большой таблице вы замучаетесь это делать.

Поэтому первое, что надо делать…

Шаг 1. Выделяем всю таблицу (подводим курсор к пересечению имён строчек и столбцов и щёлкаем по зелёному треугольнику):

Шаг 2. Вызываем диалоговое окно «Формат ячеек» (лента Главная → группа команд Ячейки → команда Формат → команда Формат ячеек из выпадающего меню):

Шаг 3. Задаём режим «Переносить текст»:

ОК.

Шаг 4. Не снимая выделения таблицы, вызываем ПМ контекстное меню (Внимание! Курсор находится на строчке названия столбцов) и выбираем команду «Ширина столбцов»:

Шаг 5. Устанавливаем ширину столбца заведомо большую ширины содержимого ячейки:

ОК.

Шаг 6. Устанавливаем автоподбор высоты и ширины ячеек :

1. лента Главная → группа команд Ячейки → команда Формат → команда Автоподбор высоты строки и …
2. лента Главная → группа команд Ячейки → команда Формат → команда Автоподбор ширины столбца

А вот результат:

Для чего в самом начала мы задали режим «Переносить текст»? Давайте уменьшим ширину первого столбца, так, чтобы тексту стало «тесно» в ячейке:

Мы то с вами знаем, что текст был, но пропал. И такие таблицы с пропавшим текстом часто попадают на стол начальнику. Поэтому возьмите за правило: периодически делайте операцию «Автоподбор высоты»:

1. Выделите таблицу
2. Лента Главная → группа команд Ячейки → команда Формат → команда Автоподбор высоты строки

Есть более простой способ задать режим «Переносить текст». отмените ваши действия до первоначального состояния таблицы.

Шаг 7. Задаём режим «Переносить текст» (лента Главная → группа команд Выравнивание→ команда «Переносить текст»):

Как видите, автоподбор высоты произошёл автоматически. Но автоподбор по ширине придётся задавать, как на Шаге 6.

При дальнейшей работе с таблицей Excel может возникнуть необходимость добавления текста в определённую ячейку (и не в одну).

2. Поиск нужной ячейки

Когда вы выделяете ячейку мышью или клавишами управления курсором, выделенная ячейка становится текущей. Текущую ячейку можно отличить по рамке вокруг неё. Адрес текущей ячейки всегда отображается в Поле имени:

Но можно сделать обратную операцию: ввести в поле «Имя Ячейки» идентификатор ячейки и курсор выделит ячейку с этим именем (вводим в поле «Имя ячейки» букву в английском регистре и цифру, например B7 и нажимаем Enter):

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

Клавиша	Перемещение курсора
←	На одну ячейку влево
→	На одну ячейку вправо
↓	На одну ячейку вниз
↑	На одну ячейку вверх
Ноmе	В начало текущей строки
End	В конец текущей строки
Tab	На одну ячейку вправо
Shift + Tab	На одну ячейку влево
Enter	На одну ячейку вниз
Shift + Enter	На одну ячейку вверх
Ctrl +Home	В начало рабочего листа
Ctrl +End	На пересечение последних строки и столбца, которые содержат данные
PageUg	На одну экранную страницу вверх в том случае, если у вас несколько листов
PageDown	На одну экранную страницу вниз

3.

Ввод и редактирование содержимого ячеек

Все, что вы набираете на клавиатуре, появляется и в текущей ячейке, и в строке формул:

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

Содержимое ячейки можно редактировать двумя способами.

1. Выделяем ячейку и, щёлкнув на строке формул, редактируем содержимое в строке формул.
2. Дважды щелкаем на ячейку, а затем помещаем текстовой курсор на место, куда хотите внести изменения.

Вводим понятия «Текстовый режим» и «Табличный режим».

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

Одиночный щелчок по ячейке – Табличный режим. При вводе данных полностью заменяется содержимое ячейки Двойной щелчок по ячейке – Текстовый режим. Редактируем имеющееся содержимое ячейки

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

Это подводный камень. При вставке текста из буфера обмена в ячейку эта ячейка утрачивает режим «переносить по словам». Можно, конечно опять вызвать диалоговое окно «Формат ячеек» и назначить этот режим для этой конкретной ячейки. Но лучше воспользоваться возможностями специальной вставки Буфера обмена:

Если во время ввода данных вы передумали продолжать ввод, то его можно отменить, нажав на клавишу . Если во время набора текста вы допустили ошибку, то исправьте ее с помощью клавиши Backspace или Delete.

4. Поиск и замена данных

Шаг 1. Вызываем диалоговое окно «Найти и заменить» (лента Главная → группа команд Редактирование → команда Найти и выделить → команда Заменить):

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

Укажите подходящие режимы.

Шаг 2. Заменяем «5» на «6» (в поле «Найти» введите текст, который нужно найти, → в поле «Заменить» на задается текст, на который нужно заменить

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

Нажимаем «Заменить все»:

ОК. Результат:

Для замены всех данных или только их части в нескольких ячейках выполните следующие действия:

1. Выделите ячейки, в которых необходимо заменить все данные или только их часть, либо щёлкните на ячейке А1, если требуется произвести изменения во всем рабочем листе.
2. В поле «Найти» введите текст, который нужно заменить.
3. В поле «Заменить на» ведите текст, на который должна быть заменяемый текст.

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

1. Делать первые рабочие операции по форматированию таблицы
2. Находить нужную ячейку
3. Вводить данные в ячейки и редактировать содержимое ячеек
4. Делать поиск и замену текста.

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

Содержание:

• Ячейка в 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

При написании формулы 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 — пример формулы

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

Для начала введем коэффициенты конверсии в какую-нибудь строку, скажем, в строку 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:

=SUM($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)

-ссылка на столбец в Excel

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

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

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

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

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

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

1. Выберите ячейку с формулой.
2. Войдите в режим редактирования, нажав клавишу F2 или дважды щелкнув ячейку.
3. Выберите ссылку на ячейку, которую хотите изменить.
4. Нажмите F4 для переключения между четырьмя типами ссылок на ячейки.

Если вы выбрали относительную ссылку на ячейку без знака $, например A1, повторное нажатие клавиши F4 переключает между абсолютной ссылкой с обоими знаками доллара, например $A$1, абсолютной строкой A$1, абсолютным столбцом $A1, а затем вернуться к относительной ссылке A1.

Примечание. Если вы нажмете F4, не выбрав ссылку на ячейку, ссылка слева от указателя мыши будет выбрана автоматически и изменена на другой тип ссылки.

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

Вас также может заинтересовать

Что такое ячейка в 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.0448 диапазон ячеек . Диапазон ячеек отображается от начальной до конечной ячейки. Выбранные ячейки должны располагаться последовательно без пробелов в выделении. Например , Диапазон ячеек 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 вы можете увеличить размер ячеек следующими способами: Увеличить высоту строки от заголовка строки Увеличить ширину столбца из заголовка столбца Объедините две или более ячеек, чтобы увеличить размер ячейки Увеличьте размер шрифта, чтобы увеличить размер ячейки При необходимости вы можете использовать любой из этих методов. Оставить комментарий Добавить комментарий Отменить ответ Ваш адрес email не будет опубликован. Обязательные поля помечены * Комментарий * Имя * Email * Сайт