Excel Использование Имен Ячеек в Формулах • Разделение окон

Как изменить диапазон ячеек в excel

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

Существуют обязательные требования к наименованию группы ячеек:

  • В нём не должно быть пробелов;
  • Оно обязательно должно начинаться с буквы;
  • Его длина не должна быть больше 255 символов;
  • Оно не должно быть представлено координатами вида A1 или R1C1;
  • В книге не должно быть одинаковых имен.

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

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

Создание именованного диапазона

Прежде всего, узнаем, как создать именованный диапазон в Экселе.

    Самый быстрый и простой вариант присвоения названия массиву – это записать его в поле имен после выделения соответствующей области. Итак, выделяем массив и вводим в поле то название, которое считаем нужным. Желательно, чтобы оно легко запоминалось и отвечало содержимому ячеек. И, безусловно, необходимо, чтобы оно отвечало обязательным требованиям, которые были изложены выше.

Выше был назван самый быстрый вариант наделения наименованием массива, но он далеко не единственный. Эту процедуру можно произвести также через контекстное меню

Открывается окошко создания названия. В область «Имя» следует вбить наименование в соответствии с озвученными выше условиями. В области «Диапазон» отображается адрес выделенного массива. Если вы провели выделение верно, то вносить изменения в эту область не нужно. Жмем по кнопке «OK».

Ещё один вариант выполнения указанной задачи предусматривает использование инструментов на ленте.

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

Активируется окно Диспетчера имён. В нем следует нажать на кнопку «Создать…» в верхнем левом углу.

Операции с именованными диапазонами

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

На одном листе у нас перечень моделей компьютерной техники. У нас стоит задача на втором листе в таблице сделать выпадающий список из данного перечня.

    Прежде всего, на листе со списком присваиваем диапазону наименование любым из тех способов, о которых шла речь выше. В итоге, при выделении перечня в поле имён у нас должно отображаться наименование данного массива. Пусть это будет наименование «Модели».

После этого перемещаемся на лист, где находится таблица, в которой нам предстоит создать выпадающий список. Выделяем область в таблице, в которую планируем внедрить выпадающий список. Перемещаемся во вкладку «Данные» и щелкаем по кнопке «Проверка данных» в блоке инструментов «Работа с данными» на ленте.

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

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

Итак, мы имеем таблицу, в которой помесячно расписана выручка пяти филиалов предприятия. Нам нужно узнать общую выручку по Филиалу 1, Филиалу 3 и Филиалу 5 за весь период, указанный в таблице.

    Прежде всего, каждой строке соответствующего филиала в таблице присвоим название. Для Филиала 1 выделяем область с ячейками, в которых содержатся данные о выручке по нему за 3 месяца. После выделения в поле имен пишем наименование «Филиал_1» (не забываем, что название не может содержать пробел) и щелкаем по клавише Enter. Наименование соответствующей области будет присвоено. При желании можно использовать любой другой вариант присвоения наименования, о котором шел разговор выше.

Таким же образом, выделяя соответствующие области, даем названия строкам и других филиалов: «Филиал_2», «Филиал_3», «Филиал_4», «Филиал_5».

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

Инициируется запуск Мастера функций. Производим перемещение в блок «Математические». Останавливаем выбор из перечня доступных операторов на наименовании «СУММ».

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

Всего оператор СУММ может насчитывать от одного до 255 аргументов. Но в нашем случае понадобится всего три аргумента, так как мы будет производить сложение трёх диапазонов: «Филиал_1», «Филиал_3» и «Филиал_5».

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

Управление именованными диапазонами

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

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

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

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

После того, как редактирование данных окончено, жмем на кнопку «OK».

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

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

После этого запускается диалоговое окно, которое просит подтвердить свою решимость удалить выбранный элемент. Это сделано во избежание того, чтобы пользователь по ошибке не выполнил данную процедуру. Итак, если вы уверены в необходимости удаления, то требуется щелкнуть по кнопке «OK» в окошке подтверждения. В обратном случае жмите по кнопке «Отмена».

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Одно и то же количество значений (ячеек) можно совершенно по разному разместить на рабочем листе Excel. Например, имеем 30 значений в ячейках одного столбца, то есть размер диапазона. Эти же значения можно разместить:

3) в четырех столбцах с неравным количеством в каждом;

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

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

Использование имен ячеек и диапазонов — Информатика, информационные технологии
Чтобы узнать, что же на самом деле содержит ячейка – формулу или число, — необходимо ее выделить и посмотреть на строку формул – область находящуюся сразу же над наименованиями столбцов. В нашем случае в ней как раз отображается формула, которую мы только что вводили.
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Так же мы познакомимся с функцией автозаполнения ячеек и узнаем, каким образом можно вставлять в таблицу дополнительные строки и столбцы, а так же объединять в ней ячейки. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Теперь «зацепите» курсором правый нижний угол квадратика и перетащите маркер вправо до ячейки G7. После того как вы отпустите клавишу, приложение само скопирует формулу в отмеченные ячейки, при этом автоматически изменив адреса клеток, содержащихся в выражении, подставив правильные значения.

Основы Excel

    Прежде всего, на листе со списком присваиваем диапазону наименование любым из тех способов, о которых шла речь выше. В итоге, при выделении перечня в поле имён у нас должно отображаться наименование данного массива. Пусть это будет наименование «Модели».

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

Статьи к прочтению:

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

Чтобы выделить Сделайте следующее Текст в ячейке Если режим редактирования в ячейке включен, выделите ячейку, дважды щелкните ее и выделите текст в…

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Чтобы присвоить имя ячейке или диапазону необходимо выделить их и в меню Вставка выбрать пункт Имя, а затем пункт Присвоить. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Выберите ячейку и команду Вставка\Примечание. Введите текст примечания в соответствующее поле. После окончания ввода текста нажмите кнопку мыши вне области примечания. Примечание можно просмотреть при наведении на ячейку указателя мыши.
Excel_2010

Excel 10. Диапазон и вычисления в нем – Эффективная работа в MS Office

  1. Выделите ячейку или диапазон ячеек.
  2. В группе Определенные имена вкладки Формулы нажмите кнопку Присвоить имя.
  3. В окне Создание имени в поле Имя введите имя ячейки или диапазона (рис. 1.18).

Обычно ссылки на диапазоны ячеек вводятся непосредственно в формулы, например =СУММ(А1:А10) . Другим подходом является использование в качестве ссылки имени диапазона. В статье рассмотрим какие преимущества дает использование имени.

Наименование товара Цена за 1 шт Количество товара
Эскимо 11,00р.
Пломбир 10,00р.
Фруктовое 8,50р.
Шоколадное 6,80р.
Сливочное 6,30р.

Составление формул

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

Excel_2010

Excel_2010

Чтобы посчитать суммарный расход за январь в ячейке B7 можно написать следующее выражение: «=18250+5100+6250+2500+3300» и нажать Enter, после чего вы увидите результат вычисления. Это является примером применения простейшей формулы, составление которой ничем не отличается от вычислений на калькуляторе. Разве что знак равно ставится вначале выражения, а не в конце.

С учетом этого давайте изменим нашу формулу вычисления суммарных ежемесячных расходов.

Excel_2010

Excel_2010_formula

Установите курсор на пустой клетке сбоку нужной строки (в нашем случае это H2). Затем нажмите кнопку Сумма на закладке Главная в группе Редактирование. Теперь, вернемся к таблице и посмотрим, что же произошло.

Excel_2010_formula

В выбранной нами ячейке появилась формула с интервалом ячеек, значения которых требуется просуммировать. При этом опять появилась пунктирная выделительная рамка. Только в этот раз она обрамляет не одну клетку, а весь диапазон ячеек, сумму которых требуется посчитать.

После нажатия Enter, в выбранной ячейке появится результат, но на этом возможности кнопки Сумма не заканчиваются. Щелкните на стрелочку рядом с ней и откроется список, содержащий функции для вычисления средних значений (Среднее), количества введенных данных (Число), максимальных (Максимум) и минимальных (Минимум) значений.

Excel_2010_summa

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Для фиксации заголовков следует выделить ячейку, по которой следует зафиксировать заголовки все строки выше выделенной ячейки и все столбцы слева от выделенной ячейки будут зафиксированы и в меню Окно выбрать команду Закрепить области. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Для группирования элементов таблицы необходимо выделить строки или столбцы, которые необходимо сгруппировать, в меню Данные выбрать пункт Группа и структура и выбрать пункт Группировать. Так можно создать несколько уровней структуры.

Работа с формулами Excel. Использование встроенных функций Excel

Открывается окошко создания названия. В область «Имя» следует вбить наименование в соответствии с озвученными выше условиями. В области «Диапазон» отображается адрес выделенного массива. Если вы провели выделение верно, то вносить изменения в эту область не нужно. Жмем по кнопке «OK».

Упорядочивание данных

Разделение окон

Чтобы одновременно просматривать различные части большой таблицы можно разделить окно таблицы на подокна.

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

Чтобы отменить разделение окон, следует в меню Окно выбрать команду Снять разделение или дважды щелкнуть на вешке разбиения.

Фиксация заголовков

Иногда необходимо зафиксировать часть таблицы. Например, зафиксировать заголовок таблицы, чтобы он всегда присутствовал на экране.

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

Отменяется фиксация заголовков командой Снять закрепление областей меню Окно.

Группирование элементов таблицы

Excel позволяет группировать элементы в таблице. Например, можно сгруппировать данные по месяцам одного квартала.

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

На рисунке пример таблицы, которая содержит 3 уровня детализации:

первый уровень детализации группирует строки со 2 по 10,

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

Чтобы снять группирование, следует выделить необходимые элементы строки (столбцы), в меню Данные выбрать пункт Группа и структура, затем пункт Разгруппировать.

Как изменить диапазон ячеек в excel
Происходит активация окошка аргументов оператора СУММ. Данная функция, входящая в группу математических операторов, специально предназначена для суммирования числовых значений. Синтаксис представлен следующей формулой:
специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Теперь при наведении курсора на любую ячейку диапазона, к которой мы применили проверку данных, справа от неё появляется треугольник. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
После нажатия Enter, в выбранной ячейке появится результат, но на этом возможности кнопки Сумма не заканчиваются. Щелкните на стрелочку рядом с ней и откроется список, содержащий функции для вычисления средних значений (Среднее), количества введенных данных (Число), максимальных (Максимум) и минимальных (Минимум) значений.

Excel 2010 для начинающих: Формулы, автозаполнение и редактирование таблиц

  1. На рабочем листе выделить данные, по которым следует построить диаграмму, включая ячейки, содержащие имена категорий или рядов;
  2. Выбрать команду Диаграмма в меню Вставка;
  3. В диалоговых окнах Мастера диаграмм следует выбрать тип, формат и другие параметры диаграммы;
  4. Закончив настройку диаграммы в Мастере диаграмм нажмите кнопку Готово.

В выбранной нами ячейке появилась формула с интервалом ячеек, значения которых требуется просуммировать. При этом опять появилась пунктирная выделительная рамка. Только в этот раз она обрамляет не одну клетку, а весь диапазон ячеек, сумму которых требуется посчитать.

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

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

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

Диапазон Excel

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

Шаг 4. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Присвоить имя):

Диапазон Excel

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

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

Диапазон Excel

Шаг 6. Щелкаем ЛМ по первому имени в этом списке:

Диапазон Excel

Диапазон с именем «Наименование_курса» выделился полностью.

Понять и запомнить! Разница между двумя способами:
1. Имя диапазона определяется автоматически
2. Имя диапазона можно задать по своему желанию

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

Диапазон Excel

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

Диапазон Excel

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

Диапазон Excel

Диапазон Excel

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

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

Диапазон Excel

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Именованные диапазоны добавляют интерактивность в книгу, делают длинные формулы короткими и, при правильном использовании, обеспечивают механизм обмена информации по всей книге. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Обычно, при создании именованного диапазона из заданного набора данных, необходимо написать (или выбрать) адрес именованного диапазона и дать ему имя. Тем не менее, во многих случаях, когда у вас уже имеются заголовки для данных на листе, существует более простой вариант создания.

Именованные диапазоны в Excel — несколько трюков использования | Exceltip

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: