Excel Диапазон и вычисления в нем

После прохождения этого руководства вы сможете:

1. Работа с диалоговыми окнами «Вставить функцию» и «Аргументы функции
2. Назовите диапазон ячеек и работайте с именами диапазонов
3. Введите формулу с абсолютным и относительным адресом ячейки.

Загрузите файл здесь. Откройте файл. (Признаюсь — мне не принадлежат подобные креативы. Я только что получил их из Интернета)

1. Диалоговые окна «Вставка функции» и «Аргументы функции»

Шаг 1. Введите Максимум в ячейку A7 и перейдите в ячейку B7:

Шаг 2. Откройте диалоговое окно «Вставить функцию» (команда ленты «Формулы Вставить функцию):

Как только мы вызвали диалог «Вставить функцию», в вычисляемой ячейке «=» появился знак «=».

Шаг 3. Выберите «МАКС» из списка последних 10 использованных функций (поле «Выбрать функцию»).

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

В поле «Функция поиска» вы можете ввести описание нужной функции, и Excel поможет вам в поиске.

Когда вы нажмете кнопку раскрывающегося меню в поле «Категории», вы увидите список всех категорий функций:

Нашли функцию MAX? OK!

Но нас с вами немного раздражает: диалоговое окно «Аргументы функций» перекрывает ряд чисел:

Шаг 4. Щелкните значок стрелки справа от поля «Число!». Диалоговое окно свернется, и вам нужно будет выбрать диапазон, в котором будет выполняться поиск максимума.

Шаг 5. Щелкните значок стрелки справа от «Число!» — диалоговое окно «Аргументы функции» раскрывается. Щелкните ОК.

В более ранних версиях Excel это называлось «Мастер функций.

2. Имя диапазона

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

1 способ.

Шаг 1. Выберите диапазон ячеек B1: B6.

Шаг 2. Задайте имя для диапазона (лента «Формулы», группа команд «Определенные имена Создать из выделенного):

Появится диалоговое окно «Создать имена из выбранного диапазона». Оставьте параметр «в строке выше» активным. OK.

способ 2.

Шаг 3. Выберите диапазон ячеек A1: A6.

Шаг 4. Назовите диапазон (имена, определенные группой команд на ленте формул, присвоить имя):

Откроется диалоговое окно «Создать имя».

1. По умолчанию содержимое первой ячейки в выбранном диапазоне становится именем диапазона. Но никто не мешает нам попросить другое имя. Обратите внимание, что пробел заменен подчеркиванием. Что ты можешь сделать? Требование Excel.
2. Определение объема имени: вся книга или конкретный лист.
3. Пояснения, которые мы считаем необходимыми для этого диапазона. Объяснения не связаны с расчетами, операциями или чем-то еще. Это нам «напоминание.
4. Конкретное значение диапазона. Но мы можем отрегулировать значение диапазона. Например, мы добавили записи, которые следует отнести к этому диапазону. Также рядом с ним находится кнопка со стрелкой, которая позволит нам временно свернуть диалог.

Шаг 5. Нажмите кнопку раскрывающегося меню имени ячейки (независимо от того, где находится активная ячейка):

Шаг 6. Щелкните LM на имени в этом списке:

Диапазон с именем «Course_Name» полностью выделен.

Шаг 7. Щелкните в ячейке по вашему выбору. Набираем «= su»:

LM дважды щелкните «СУММ».

Шаг 8. Выбираем диапазон по имени (набираем прямо в ячейку «c». Тут же появляется выпадающий список, в котором есть функции, начинающиеся с «c» и имя нашего диапазона). LM дважды щелкните имя диапазона:

Шаг 9. И убедитесь, что у вас есть закрывающая скобка! Необходимо соблюдать требования Excel к написанию синтаксиса формул.

Шаг 10. Нажмите Enter:

Посмотрим на строку формул: = СУММ (Цена). То есть по названию определил соответствующий интервал.

Шаг 11. Формулы ленты Команда Группы Команда Определенные имена Диспетчер имен. Откроется диалоговое окно «Диспетчер имен»:

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

3. Абсолютный и относительный адрес ячейки

Скопируем таблицу. Attention here! Если вы просто выделите весь диапазон и перетащите его в другое место на листе (Excel 5), вы увидите следующее изображение:

Поэтому мы потратим драгоценное время на настройку ширины и высоты ячеек. Так что не торопитесь.

Шаг 1. Выделите таблицу со значениями и скопируйте в буфер обмена:

Шаг 2. Вставьте содержимое буфера обмена в ячейку F1 специальной пастой, сохраняя ширину столбца:

Советую постепенно запоминать специальные кнопки вставки — вы сэкономите время и нервы.

Теперь посчитаем стоимость курса на группу от 20 человек (больше набирать нет смысла). Для первой таблицы введите число «20» в диапазоне C2: C6 в каждую ячейку. Для второй таблицы введите число «20» в ячейку H1:

Шаг 3. В ячейке D2 введите формулу «= ПРОДУКТ (B2; C2). Это может быть проще:« = B2 * C2»:

Шаг 4. Теперь давайте расширим эту формулу по столбцу стоимости курса. Это можно сделать двумя способами.

1 способ.

1. Подведите курсор к зеленому квадрату в правом нижнем углу ячейки с формулой: курсор превращается в черный крест (курсор для заполнения таблицы — Excel 3)
2. Нажать LM и, не отпуская, растянуть на весь диапазон:

способ 2.

1. Подведите курсор к зеленому квадрату в правом нижнем углу ячейки с формулой: курсор превращается в черный крест (курсор для заполнения таблицы — Excel 3)
2. Дважды щелкните LM:

Давайте посмотрим на формулы в каждой ячейке.

Шаг 5. Отобразите формулы (лента «Формулы», группа команд «Зависимости формул», команда «Показать формулы):

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

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

Поработаем со второй таблицей.

Шаг 6. В ячейке H2 введите формулу «= G2 * H1»:

Теперь нам нужно указать, что при копировании формулы ссылка будет только на ячейку H1, то есть адрес этой ячейки будет абсолютным. Чтобы указать абсолютный адрес в Excel, используйте знак «$» или знак доллара. Если не поленитесь, введите вручную (Shift + 4 в английской раскладке клавиатуры). Но лучше использовать функциональную клавишу F4, которой все равно, какая раскладка клавиатуры):

Если вы работаете на ноутбуке, не забудьте одновременно нажать клавишу Fn.

Шаг 7. Поместите курсор на имя ячейки «H1» в формуле и нажмите Fn на клавиатуре:

Попробуйте щелкнуть.

1. Относительный адрес. Относительная адресация — это адрес ячейки, содержащей исходные данные, которая изменяется при копировании и перемещении формулы (используется по умолчанию).
2. Абсолютный адрес. Абсолютная адресация — это адрес ячейки, содержащей исходные данные, который не изменяется при копировании и перемещении формулы. Этот адрес выглядит так: $ A $ 1. Знак доллара означает, что значения строки и столбца «заморожены».
3. Смешанный или частичный адрес. Частичная абсолютная адресация указывается, если номер строки или имя столбца не изменяется при копировании формулы. В этом случае символ $ в первом случае ставится перед номером строки, а во втором — перед названием столбца: B $ 5; 12 дирхамов.

Шаг 8. Нажмите Enter и скопируйте формулу по диапазону:

Шаг 9. Показываем формулы (лента «Формулы», группа команд «Зависимости формул», команда «Показать формулы):

При копировании формулы адрес с курсом меняется соответственно, и адрес ячейки H1 повторяется — это абсолютный адрес.

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