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

Связанный список в MS EXCEL

​Смотрите также​ раза, пока курсор​ прямоугольная область прилегающих​

​Диспетчер имен​ от строки формул.​ столбце В (у​ статье «Как добавить​ тогда в формулу​ обычно. Нажимаем «Проверка​Способ, который мы​Теперь подробнее.​ (май, июнь…), с​
​и нажмите кнопку​ на ячейку листа.​ Рассмотрим этот подход​Тестируем. Выбираем с помощью​Таблица​Создадим выпадающий список, содержимое​

• ​ не перейдет на​ ячеек. Данное определение​(вкладка​Кроме того, для выделения​ нас – это​ столбец в Excel​ перед адресом ячейки​ данных», выбираем «Список».​
• ​ сейчас рассмотрим удобен​Функция ПОИСКПОЗ() ищет в​ другой стороны Выпадающий​ОК​ Включить имя листа,​ в другой статье:​ выпадающего списка в​. См. файл примера​ которого зависит от​ G7. Таким образом,​ понятия легче воспринять​Формулы​

​ именованных и неименованных​ В2:В3). Снова через​ автоматически».​ напишем название листа​ В диалоговом окне​ тем, что при​
​ строке 5 (перечень​ список не должен​.​ за которым следует​ Расширяемый Связанный список.​
​ ячейке​ Связанный_список.xlsx​ значений другой ячейки.​ мы выделили блок​ на практических примерах.​, группа​ ячеек и диапазонов​ функцию «Проверка данных»​Как сделать в Excel​ и поставим восклицательный​ «Проверка вводимых значений»​ добавлении строк или​ месяцев) выбранный пользователем​ содержать пустые строки.​Выделите диапазон ячеек.​

​ восклицательный знак; Например,​Примечание:​A5​Список регионов и перечни​

​Обычный Выпадающий (раскрывающийся) список​​ диапазона D3:G7.​В формулах диапазон записывается​Определенные имена​ можно использовать команду​ выбираем «Тип данных»​ динамический диапазон​ знак.​ в строку «Источник»​ столбцов в таблицу,​ месяц (ячейка​ Динамический диапазон как​На панели быстрого доступа​

​= Лист1​Мы стараемся как​Регион – Америка,​ стран разместим на​​ отображает только один​​Как выделить несмежный диапазон​ адресами двух ячеек,​

​). Дополнительные сведения см.​Перейти​ – список. А​​- чтобы размер​​Как еще можно​

​ вставляем скопированную формулу​ все диапазоны в​​С1​​ раз и служит​​ нажмите кнопку​​! F2​ можно оперативнее обеспечивать​ вызываем связанный список​ листе​​ перечень элементов. Связанный​​ ячеек в Excel?​

​ которые разделенные двоеточием.​ в статье Определение​.​ в строке «Источник»​ диапазонов списков при​

• ​ применить готовые шаблоны​​ шапки таблицы. Но​​ таблице увеличиваются автоматически,​​с выпадающим списком)​​ для решения такой​камеры​.​ вас актуальными справочными​
• ​ в ячейке​Списки​ список – это​ Выделите мышкой блок​ Верхняя левая и​
• ​ и использование имен​Важно:​ пишем такую формулу​
• ​ добавлении или убавлении​

​ таблиц Excel, читайте​ формулу нужно доработать.​ не нужно их​ и возвращает соответствующий​ задачи.​.​

​Нажмите клавишу ВВОД.​ материалами на вашем​B5​.​ такой выпадающий список,​

​ B3:D8. Нажмите клавишу​​ правая нижняя ячейка,​​ в формулах.​​ Чтобы выделить именованные ячейки​​ =ДВССЫЛ(А2)​ ячеек менялся автоматически,​

• ​ в статье «Готовые​​ Допишем впереди –​​ настраивать.​
• ​ номер позиции в​
• ​Для создания динамического диапазона:​Щелкните место на листе​
• ​Содержимое ячейки отображается в​ языке. Эта страница​

​и балдеем –​Обратите внимание, что названия​ который может отображать​ F8 чтобы включить​ которая входит в​

• ​На вкладке «​​ и диапазоны, необходимо​​Этой формулой мы говорим​
• ​ смотрите в статье​
• ​ таблицы Excel».​ ДВССЫЛ, поставим кавычки​
• ​Остается просто вносить​ диапазоне поиска (названия​

​на вкладке Формулы в​ или диаграмме нужное​ фигуре или надписи,​​ переведена автоматически, поэтому​​ появился список стран​ регионов (диапазон​

​ разные перечни элементов,​ специальный режим. В​ состав диапазона, например​​Главная​​ сначала определить их​ Excel, что список​ «Чтобы размер таблицы​​Как сделать связанный​​ и круглые скобки.​ нужные данные в​ месяцев должны быть​ группе Определенные имена​

​ изображение вставленного диапазон​ который был выбран.​​ ее текст может​​ для Региона Америка:​А2:А5​ в зависимости от​​ строке состояния появится​​ A1:B3.​» в группе​

​ имена на листе.​ нужно показывать, в​ Excel менялся автоматически».​ выпадающий список, фамилии​ Получилась такая формула.​ таблицу и все.​ уникальны, т.е. этот​ выберите команду Присвоить​ ячеек.​​Примечание:​​ содержать неточности и​ США, Мексика…​​на листе​​ значения другой ячейки.​ сообщение: «Расширить выделенный​Обратите внимание! Ячейка, от​ «​ Сведения об именовании​ зависимости от значения​Теперь нужно присвоить​ в котором можно​=ДВССЫЛ(«Товар[#Заголовки]»)​ Остальное сделает сама​ пример не годится​ имя;​Содержимое диапазон ячеек отображается​ Нельзя использовать эту процедуру​ грамматические ошибки. Для​Теперь заполняем следующую строку.​

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

​ связанных списков (другие​ выделите мышкой блок​ диапазона, остается активной.​» нажмите кнопку​ см. в статье​ А.​ спискам. У нас​ смотрите в статье​
​Так как в самой​ диапазоны и внесет​ На это число​ Месяц;​Чтобы отформатировать рисунок или​

​ линия, линия или​ эта статья была​A6​ совпадать с заголовками​ названия: связанные диапазоны,​ F2:K5. Как видите,​ Это значит, что​

excel2.ru

Динамическое отображение содержимого ячейки или диапазона ячеек в графическом объекте

​Найти и выделить​​ Определение и использование​Здесь все просто.​ в списках четыре​ «Связанный выпадающий список​ таблице с данными​ новые данные в​ столбцов смещается левый​в поле Область выберите​ выполнить другие операции,​ Рисованная кривая.​ вам полезна. Просим​Регион – Азия,​ столбцов, содержащих названия​ динамические списки) появляется​ в данном режиме​ при выделенном диапазоне​и нажмите кнопку​ имен в формулах.​ Но бывает название​ диапазона (четыре столбца).​ в Excel по​

​ диапазоны динамичные, то​ соответствующие выпадающие списки.​ верхний угол нашего​ лист Книга;​ щелкните рисунок правой​На диаграмме щелкните заголовок,​ вас уделить пару​ вызываем связанный список​ соответствующих стран (​ при моделировании иерархических​ ​ мы имеем возможность​​ данные из клавиатуры​Перейти​В поле​ диапазона (столбца) состоит​

​ Легко и быстро​ алфавиту».​ если мы добавим​У нас есть​ динамического диапазона (от​в поле Диапазон введите​ кнопкой мыши и​ подпись или текстовое​

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

1. ​ секунд и сообщить,​ в ячейке​В1:Е1​ структур данных. Например:​

   • ​ выделять стразу несколько​ будут введены в​​. Можно также нажать​​Имя​​ из нескольких слов.​​ сделать так.​​Выпадающие списки в Excel​​ столбец, это слово​ такие данные на​ ячейки​

     

   • ​ формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))​ выберите команду.​​ поле, которое нужно​​ помогла ли она​​B6​​).​​Отдел – Сотрудники отдела.​​ диапазонов. Чтобы вернутся​ его первую ячейку.​ сочетание клавиш CTRL​

     

2. ​, которое расположено слева​ Например, «Зимние пальто».​Как присвоить имя диапазону​бывают разные. Есть​ автоматически увеличится диапазон​

3. ​ листе.​А6​

4. ​нажмите ОК.​Например может потребоваться изменить​ связать с ячейкой​ вам, с помощью​

   ​и опять балдеем:​​Присвоим имена диапазонам, содержащим​ При выборе отдела​ в обычный режим​ Она отличается от​ + G на​​ от строка формул,​​ А в имени​​ в​

5. ​ простой​

   ​ таблицы и слово​Сделаем из этих данных​), высота диапазона не​

​Теперь подробнее.​​ границы или прозрачного​ листа, или выполните​ кнопок внизу страницы.​ Китай, Индия…​

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

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

   1. ​ отобразится в выпадающем​

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

   2. ​ F8.​​ фона.​​В списке​​ указанных ниже действий.​​ пробел. Имя диапазона​Выделяем диапазон ячеек​​ ячейке​​ списке. Например, добавим​ Делаем активной любую​

      

2. ​ равна 12 (при​ EXCEL задается координатами​

3. ​Формат рисунка​ чтобы выбрать его​ приводим ссылку на​ именах нельзя использовать​ диапазоны). Быстрее всего​ формируется список, содержащий​Как выделить большой диапазон​

   ​К диапазонам относятся:​​Перейти​Чтобы выделить именованную ячейку​ напишем так «Зимние_пальто».​ всех списков сразу​. Есть​​ слово «плащ». Получилось​​ ячейку этих данных.​

4. ​ желании ее также​

   ​ верхней левой и​.​ из списка элементов​ оригинал (на английском​

Динамическое отображение содержимого ячейки диапазона в рисунка

1. ​ символ пробела. Поэтому,​ это сделать так:​​ перечень фамилий всех​ ​ ячеек в Excel?​Несколько ячеек выделенных блоком​

   1. ​щелкните имя ячейки​ или диапазон, введите​ Но формула ДВССЫЛ​​ вместе с шапкой​​многоуровневые зависимые выпадающие списки​

   2. ​ так.​​ Заходим на закладку​​ можно сделать также​​ нижней правой ячейки​​Если вам необходимо постоянно​

   3. ​ диаграммы.​​ языке) .​​ при создании имен,​​выделитьячейки​​ сотрудников этого отдела​​ Клавиша F5 или​​ (=B5:D8).​

2. ​ или диапазона, который​

3. ​ имя и нажмите​ не найдет этот​​ таблицы списков –​ ​ в Excel​

4. ​Второй уровень связанных выпадающих​ «Главная», в разделе​ динамической – зависящей​ диапазона. Исходной ячейкой,​

   ​ добавлять значения в​Щелкните диаграмму.​

5. ​Отображение содержимого ячейки листа​ вышеуказанным способом, он​А1:Е6​ (двухуровневая иерархия);​ CTRL+G. В появившемся​

   ​Одна ячейка (=A2:A2).​ требуется выделить, либо​ клавишу ВВОД.​​ диапазон. Тогда формулу​​ у нас это​

support.office.com

Динамический диапазон в MS EXCEL

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

​Город – Улица –​ окне, в поле​Целая строка (=18:18) или​ введите ссылку на​Совет:​ нужно написать так.​ диапазон А1:D4. На​

​ зависимости от выбранных​Excel.​ «Форматировать как таблицу»​ в диапазоне).​ положение нашего динамического​ правильной работы Ваших​

Задача

​Работа с диаграммами​ поля или элемент​ на нижнее подчеркивание​

​Списки​ Номер дома. При​ «Ссылка» введите адрес:​ несколько строк (=18:22).​ ячейку в поле​ Кроме того, можно щелкнуть​ =ДВССЫЛ(ПОДСТАВИТЬ(A2;» «;»_»))​ закладке «Формулы» в​ данных в первом​Сначала выберем из​

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

​ «_». Например, если​(т.е. диапазон, охватывающий​ заполнении адреса проживания​ B3:D12345 (или b3:d12345)​Целый столбец (=F:F) или​Ссылка​ стрелку рядом с​Если список на​ разделе «Определенные имена»​ столбце выпадающего списка,​ выпадающего списка в​

​ шаблон таблицы.​ ячейке​B5​ понадобятся динамические диапазоны,​Конструктор​ связать ячейку, содержащую​ вместо Америка (ячейка​ все ячейки с​ можно из списка​ и нажмите ОК.​ несколько столбцов (=F:K).​.​ полем​ другом листе, то​ нажимаем функцию «Создать​ меняется выпадающий список​

​ ячейке Е1, чтобы​

• ​Обязательно ставим галочку у​С2​. Если не заданы​ которые автоматически увеличиваются​
• ​,​ данные, которые должны​
• ​В1​ названиями Регионов и​
• ​ выбрать город, затем​ Таким образом, вы​
• ​Несколько несмежных диапазонов (=N5:P8;E18:H25;I5:L22).​

​Например, введите в поле​
​Имя​ в формуле указываем​ из выделенного фрагмента».​ в ячейках второго​ эта ячейка не​ строки «Таблица с​формулу =СУММ(Продажи_за_месяц) получим​ аргументы функции СМЕЩ()​​ или уменьшаются в​​Макет​ отображаться фигуры, текстового​​) ввести «Северная Америка»​​ Стран);​ из списка всех​ без труда захватили​Целый лист (=1:1048576).​Ссылка​, чтобы открыть список​​ название этого листа.​​ В появившемся диалоговом​ столбца, третьего, т.д.​ была пустой.​ заголовками».​ сумму продаж в​смещ_по_строкам,смещ_по_столбцам​ зависимости от количества​и​ поля или элемент​ (соответственно подкорректировав ячейку​нажать кнопку «Создать из​ улиц этого города​ огромный диапазон, всего​​Все выше перечисленные виды​​значение​

​ именованных ячеек или​ Напишем так. =ДВССЫЛ(»Размеры!А2:А4»)​ окне оставляем галочку​Здесь разберём​Затем, как в​Нажимаем «ОК». Получилась такая​ выбранном месяце.​(как в нашем​ ваших данных.​Формат​ диаграммы. С помощью​А2​

​ выделенного фрагмента» (пункт​ – улицу, затем,​ за пару кликов.​ блоков являются диапазонами.​B3​ диапазонов, и выбрать​

​Нажимаем «ОК». Теперь​ только у строки​двухуровневый зависимый выпадающий список​

​ первом случае, ставим​

• ​ таблица.​Например, в мае.​ случае), то эта​Динамический диапазон — это​
• ​.​ команды​
• ​), то после нажатия​ меню Формулы/ Определенные​
• ​ из списка всех​

​В поле «Имя» (которое​

​​, чтобы выделить эту​ в нем нужное​ во втором столбце​​ «В строке выше».​​ в Excel​ в пустой ячейке​Сама эта таблица уже​Или, например, в апреле.​ ячейка является левой​ Именованный диапазон с​На вкладке​камеры​ кнопки Создать из​ имена/ Создать из​ домов на этой​ расположено слева от​Выделение диапазонов – это​​ ячейку, или​​ имя.​ установлены выпадающие списки,​Нажимаем «ОК». Всё, имена​.​ (не в таблице)​ имеет имя. Смотрим​Примечание:​ верхней ячейкой диапазона.​

​ изменяющимися границами. Границы​Формат​​, можно также отобразить​​ выделенного фрагмента будет​ выделенного фрагмента);​ улице – номер​

​ строки формул) задайте​

​ одна из основных​

​B1:B3​
​Чтобы выбрать две или​ которые меняются, в​ присвоены. На закладке​Например, в первом​ знак «равно». Выделяем​
​ в Диспетчере имен.​

​Вместо формулы с​ Нижняя правая ячейка​ диапазона изменяются в​в группе​ содержимое диапазон ячеек​ создано имя «Северная_Америка».​Убедиться, что стоит только​ дома (трехуровневая иерархия).​​ диапазон ячеек: B3:D12345​​ операций при работе​

Визуальное отображение динамического диапазона

​, чтобы выделить диапазон​ более ссылки на​ зависимости от того,​ «Формулы» нажимаем функцию​ столбце из выпадающего​​ данные столбца А​​Название этой таблицы можно​ функцией СМЕЩ() для​
​ диапазона определяется аргументами​

​ зависимости от количества​Текущий фрагмент​ путем связывания диапазон​ В этом случае​ галочка «В строке​

Применение динамического диапазона

​В этой статье рассмотрен​ (или b3:d12345) и​ с Excel. Диапазоны​ из трех ячеек.​ именованные ячейки или​ что написано в​ «Диспетчер имен».​ списка выбрали «Пальто».​ (без названия столбца).​

excel2.ru

Как сделать связанные выпадающие списки в Excel легко.

​ поменять. Нажимаем на​ подсчета заполненных месяцев​​высота ширина​ ​ значений в определенном​щелкните стрелку рядом​​ ячеек для рисунка.​ формула =ДВССЫЛ(A5) работать​ выше»;​ только двухуровневый связанный​ нажмите «Enter».​
​ используют:​ Чтобы выделить несколько​ диапазоны, щелкните стрелку​ ячейках первого столбца.​Здесь перечислены все наши​ Во втором столбце​ У нас -​ таблицу, заходим на​ можно использовать формулу​
​. В нашем случае​ диапазоне.​ с полем​Так как графический объект​ не будет, т.к.​Нажать ОК.​ список. Многоуровневый связанный​Способ 5 и 6​
​при заполнении данных;​ ячеек или диапазонов,​ рядом с полем​
​ Получилось так.​ диапазоны списков. Проверили​ появился выпадающий список​ это диапазон А2:А4.​ закладку «Конструктор» и​ с функцией ИНДЕКС():​ значение высоты =1,​Динамические диапазоны используются для​Область диаграммы​ связанный ячейку или​​ при выборе региона​Проверить правильность имени можно​ список рассмотрен в​
​ – это самое​при форматировании;​
​ укажите их в​имя​Копируем формулу вниз по​​ всё. Можно подкорректировать​ размеров этого пальто.​Копируем формулу выделенного​ в разделе «Свойства»​=$B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))​ а значение ширины​ создания таких структур,​, а затем выберите​ диапазон ячеек, изменения,​
​ «Северная Америка» функция​ через Диспетчер Имен​ одноименной статье Многоуровневый​ быстрое решение для​при очистке и удалении​ поле​и нажмите кнопку​ столбцу. Мы создали​ размер диапазона. Мы​ А, если в​ диапазона. Выходим из​ пишем свое название​Формула подсчитывает количество элементов​
​ диапазона равно результату​ как: Выпадающий (раскрывающийся)​ нужный элемент диаграммы.​ внесенные в данные​ ДВССЫЛ() не найдет​ (Формулы/ Определенные имена/​ связанный список.​
​ выделения больших диапазонов.​ ячеек;​
​Ссылка​ имя первого ссылку​ двухуровневый выпадающий список​ уменьшили размер диапазона​ первом столбце этой​ ячейки клавишей «Esc».​​ таблицы. Мы написали​ в строке 5​ вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5),​ список, Вложенный связанный​
​В строке формул введите​ для этой ячейки​ соответствующего имени. Поэтому​ Диспетчер имен). Должно​Создание иерархических структур​ Небольшие диапазоны в​при создании графиков и​через запятые.​ на ячейку или​ в Excel.​ «Юбка», чтобы в​ же ячейки из​ Выделяем ячейки, в​ имя таблицы –​ (функция СЧЁТЗ()) и​ т.е. 4 (в​ список и Связанный​ знак равенства (=).​ или диапазона ячеек,​
​ формулу можно подкорректировать,​
​ быть создано 5​
​ данных позволяет избежать​ пределах одного экрана​ диаграмм и т.п.​Примечание:​ диапазон, который требуется​Другой способ сделать​ выпадающем списке не​ выпадающего списка выбрали​ которых будем создавать​ «Товар».​ определяет ссылку на​ ​ строке 5 присутствуют​ список.​ ​На листе выделите ячейку,​
​ автоматически отображаются в​ чтобы она работала​ имен.​ неудобств выпадающих списков​ лучше выделять мышкой.​
​Способы выделения диапазонов:​ В списке​ выделить. Затем удерживая​ связанный выпадающий список​ было пустой строки.​ «Брюки», то во​ выпадающие списки второго​В этой таблице уже​ последний элемент в​
​ 4 месяца с​Имеется таблица продаж по​ содержащую данные или​ графический объект.​ при наличии пробелов​Можно подкорректировать диапазон у​ связанных со слишком​Чтобы выделить диапазон нескольких​Чтобы выделить диапазон, например​Перейти​ клавишу CTRL, щелкните​ в Excel, смотрите​Теперь устанавливаем​ втором столбце будет​ уровня. У нас​
​ все столбцы имеют​ строке (функция ИНДЕКС()),​ января по апрель).​ месяцам некоторых товаров​ текст, который вы​Если у вас нет​ в названиях Регионов:​ имени Регионы (вместо​ большим количеством элементов.​ столбцов нужно подвести​ A1:B3, нужно навести​
​можно просмотреть все​ имена других ячеек​ в статье «Как​первый выпадающий список в​ выпадающий список с​ – это диапазон​ динамические диапазоны. Это​ тем самым возвращает​ Итак, адрес нижней​ (см. Файл примера):​ хотите отобразить в​ фигуры или текстового​ =ДВССЫЛ(ПОДСТАВИТЬ(A5;» «;»_»)).​ =списки!$A$2:$A$6 установить =списки!$A$2:$A$5,​
​Связанный список можно​
​ курсор мышки на​
​ курсор мышки на​ именованные или неименованные​ или диапазонов в​ сделать связанные выпадающие​ ячейки столбца А​ размерами брюк.​ F1:F4 (окрашен зеленым​
​ значит, если мы​ ссылку на диапазон​ правой ячейки нашего​Необходимо найти сумму продаж​ заголовок, подпись или​
​ поля на листе,​Теперь о​ чтобы не отображалась​ реализовать в EXCEL,​ заголовок первого столбца​ ячейку A1 и​ ячейки или диапазоны,​ поле​

excel-office.ru

Связанные выпадающие списки в Excel.

​ списки в Excel​​.​Итак, сделаем две​​ цветом). Через функцию​ добавим в столбце​​B5:E5​​ динамического диапазона определен​ товаров в определенном​​ текстовое поле на​ сделайте следующее:​недостатках​ последняя пустая строка)​ с помощью инструмента​ и удерживая левую​ удерживая левую клавишу​
​ которые ранее были​​имя​ легко».​​У нас, в​
​ таблицы. Саму таблицу​ «Проверка данных» устанавливаем​ ячейку, то диапазон​.​ – это​ месяце. Пользователь должен​ диаграмме.​Для создания фигуры на​.​На листе​ Проверка данных (Данные/​ клавишу протянуть его​ мышки провести курсор​ выделены с помощью​
​.​Примечание:​ примере, мы выделяем​ сделаем на странице​ выпадающий список. В​ увеличится, и это​Выделить текущий динамический диапазон​E5​
​ иметь возможность выбрать​Совет:​
​ вкладке​
​При создании имен​Таблица​ Работа с данными/​ до заголовка последнего​ на ячейку B3.​ команды​Примечание:​Мы стараемся как​ диапазон A2:A3. И,​ книги «Таблица». А​
​ строку «Источник» диалогового​ слово сразу автоматически​ можно с помощью​.​ нужный ему месяц​ Можно также ввести ссылку​Вставка​ с помощью кнопки​
​, для ячеек​ Проверка данных) с​ столбца. В процессе​ Казалось бы, нет​Перейти​ Текущая выделенная ячейка останется​ можно оперативнее обеспечивать​ через «Проверки данных»​ списки сделаем на​
​ окна вставляем скопированную​ попадет в выпадающий​​ Условного форматирования. В​При заполнении таблицы данными​ и получить итоговую​ на ячейку листа.​в группе​ меню Создать из​A5:A22​
​ условием проверки Список​ мы наблюдаем подсказку​ ничего проще и​. Чтобы вернуться к​ выделенной вместе с​ вас актуальными справочными​ на закладке «Данные»,​
​ странице «Размеры». У​ формулу.​ ​ список. То же​
​ файле примера для​ о продажах за​ сумму продаж. Выбор​ Включите имя листа,​иллюстрации​ выделенного фрагмента, все​сформируем выпадающий список​ (пример создания приведен​ Excel: количество выделенных​ этого достаточно для​ ячейке или диапазону,​ ячейками, указанными в​ материалами на вашем​ устанавливаем выпадающие списки.​
​ нас есть такая​Но формулу снова​ самое и со​ ячеек диапазона​​ май, июнь и​ месяца пользователь должен​ за которым следует​нажмите кнопку​ именованные диапазоны для​ для выбора Региона.​ в данной статье)​ столбцов.​
​ практических знаний. Но​​ которые были выделены​ поле​​ языке. Эта страница​
​ Тип данных –​ таблица.​ дорабатываем. Ставим две​ столбцом. Пример посмотрим​B6:I14​ т.д., формула СЧЁТЗ(лист1!$B$5:$I$5)​ осуществлять с помощью​ восклицательный знак, например,​фигуры​ перечней Стран были​выделяем ячейки​
​ или с помощью​Выделение строк выполняется аналогичным​ попробуйте таким способом​ раньше, дважды щелкните​
​Имя​​ переведена автоматически, поэтому​ выбираем «Список». А​​И мы сделали такие​​ круглые скобки, четыре​ ниже.​
​применено правило Условного форматирования​
​ будет возвращать число​ Выпадающего списка.​Лист1​и выберите нужную​ созданы одинаковой длины​A5:A22​ элемента управления формы​ способом только курсор​ выделить блок B3:D12345.​ нужное имя ссылки​(это относится и​
​ ее текст может​ в строке «Источник»​ списки.​ кавычки, два знака​Итак, форматированную таблицу​ с формулой:​ заполненных ячеек (количество​Для решения задачи нам​! F2​ фигуру, которую требуется​
​ (равной максимальной длине​;​ Список (см. статью​ мышки с нажатой​Теперь щелкните по ячейке​ на ячейку в​
​ к диапазонам).​ содержать неточности и​ указываем имя диапазона.​Внимание!​ «&» и, вместо​ сделали. Всё. Осталось​=СТОЛБЕЦ(B6)=СТОЛБЕЦ(Продажи_за_месяц)​ названий месяцев) и​ потребуется сформировать два​Нажмите клавишу ВВОД.​ использовать.​ списка для региона​вызываем инструмент Проверка данных;​ Связанный список на​
​ левой клавишей нужно​ A1, после чего​ списке​Чтобы выделить неименованный диапазон​ грамматические ошибки. Для​
​ Например, «=Наименование_товара».​В списках названия​ слова «пальто» (это​ сделать выпадающие списки.​Условное форматирование автоматически выделяет​ соответственно определять новую​ динамических диапазона: один​Содержимое ячейки будет отображаться​
​Чтобы создать надпись, на​ Европа (5 значений)).​устанавливаем тип данных –​ основе элемента управления​
​ вести вдоль нумерации​ нажмите и удерживайте​Перейти​ или ссылку на​ нас важно, чтобы​Подробнее, как установить​ столбцов (В, С,​

excel-office.ru

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

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

​ или текстовое поле,​Вставка​ тому, что связанные​в поле Источник вводим:​Создание Связанного списка на​Для выделения диапазона целого​ а потом щелкните​Совет:​ на нужную ячейку​ вам полезна. Просим​

​ в статье «Выпадающий​ совпадать с названием​

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

​в группе​​ списки для других​ =Регионы​ основе Проверки данных​ листа следует сделать​ по ячейке B3.​ Чтобы быстро найти и​ или диапазон и​ вас уделить пару​ список в Excel».​

Выделение именованных и неименованных ячеек и диапазонов с помощью поля «Имя»

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

• ​Если вы еще не​текст​ регионов содержали пустые​Теперь сформируем выпадающий список​

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

• ​Примеры использования динамического диапазона,​ВНИМАНИЕ! При использовании функции​ будем использовать функцию​ сделали этого, добавьте​нажмите кнопку​​ строки.​​ для столбца Страна​ примере.​ мышки по верхнему​ блок A1:B3. Данную​ содержащие определенных типов​Совет:​ помогла ли она​зависимые выпадающие списки в​ это наименование товара​​Кстати, если вы​​ списков в Excel.​

  ​ например, можно посмотреть​​ СЧЕТЗ() необходимо убедиться​ СМЕЩ(), которая возвращает​камеры​Надпись​​Конечно, можно вручную откорректировать​​ (это как раз​Задача​

• ​ левому уголку листа,​ операцию выделения условно​ данных (например, формулы)​ Например, введите​ вам, с помощью​ столбце В​

  ​ – ячейки А2:А4​​ выделили диапазон снизу​​В любой пустой​​ в статьях Динамические​ в отсутствии пустых​​ ссылку на диапазон​​на панель быстрого​и нарисуйте текстовое​

​ диапазоны или даже​​ и будет желанный​​: Имеется перечень Регионов,​​ где пересекаются заголовки​ можно записать: A1​ или только ячейки,​B3​ кнопок внизу страницы.​.​ должны совпадать с​​ вверх, то ставите​​ ячейке пишем «=»​​ диаграммы. Часть5: график​​ ячеек! Т.е. нужно​​ в зависимости от​​ доступа.​ поле нужного размера​ вместо Именованных диапазонов​ Связанный список).​

Выделение именованных и неименованных ячеек и диапазонов с помощью команды «Перейти»

1. ​ состоящий из названий​​ строк и столбцов.​​ потом SHIFT+B3.​ которые удовлетворяют определенным​​, чтобы выделить эту​​ Для удобства также​​Это второй уровень​​ ячейками В1:D1).​​ адрес ячейки нижней.​​ (знак «равно»), выделяем​ с Прокруткой и​ заполнять перечень месяцев​ значения заданных аргументов.​

   

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

   ​ приводим ссылку на​​ выпадающих списков.​​Если наименований много,​​ Вообщем, с какой​​ ячейки шапки таблицы​ Масштабированием и Динамические​​ без пропусков.​​ Можно задавать высоту​ панели инструментов и​Нажмите кнопку фигуры или​ Но, при большом​B5:B22​ каждого Региона имеется​​ горячих клавиш CTRL+A.​​ стрелками клавиатуры. Щелкните​

   ​ видимые ячейки или​​B1:B3​​ оригинал (на английском​​Внимание!​ то столбец можно​ ячейки выделили диапазон,​ – это будет​ диаграммы. Часть4: Выборка​Теперь создадим еще один​​ и ширину диапазона,​​ выберите пункт​ текстового поля, к​ количестве имен делать​;​ свой перечень Стран.​Несмежные диапазоны складываются из​ по ячейке D3,​​ последнюю ячейку на​​, чтобы выделить диапазон​

​ языке) .​​Перед тем, как​ транспонировать в строку.​ ту ячейку и​ первый выпадающий список.​ данных из определенного​ динамический диапазон для​ а также смещение​Другие команды​ которому вы хотите​ это будет достаточно​вызываем инструмент Проверка данных;​ Пользователь должен иметь​​ нескольких других диапазонов.​​ а теперь удерживая​​ листе, содержащую данные​​ из трех ячеек.​Независимо от наличия определенных​

support.office.com

Выделение диапазона ячеек в Excel

​ устанавливать выпадающие списки​ Как это сделать,​ указываете (первую выделенную​Копируем это адрес. Нажимаем​ диапазона.​ суммирования продаж.​ по строкам и​

​.​ связать содержимое ячейки​ трудоемко.​устанавливаем тип данных –​ возможность, выбрав определенный​Чтобы их выделять просто​ SHIFT, нажмите клавишу​ или форматирование), нажмите​

​Примечание:​ именованных ячеек или​ в столбце В,​ смотрите в статье​ в диапазоне). Мы​ клавишу «Esc» -​Здесь мы рассмотрим​Для создания динамического диапазона:​ столбцам.​В списке​ на листе.​

​Кроме того, при​

1. ​ Список;​ Регион, в соседней​
2. ​ удерживайте нажатие клавиши​
3. ​ «стрелка вправо» три​ кнопку​
4. ​ В поле​ диапазонов на листе,​
5. ​ выберите в первой​
6. ​ «Как поменять местами​

​ выделили диапазон сверху​ так выходим из​

​ другой вариант,​

Работа с выделенным диапазоном ячеек MS Excel

​на вкладке Формулы в​Создадим динамический диапазон для​Выбрать команды из​В строке формул введите​ добавлении новых Регионов​

• ​в поле Источник вводим:​
• ​ ячейке выбрать из​
• ​ CTRL, а дальше​ раза пока курсор​
• ​Выделить​Имя​

​ чтобы быстро найти​

1. ​ верхней ячейке столбца​ столбцы и строки​ вниз, п.э. поставим​ ячейки.​как легко​ группе Определенные имена​ Выпадающего списка, содержащего​выберите пункт​ знак равенства (=).​ придется вручную создавать​ =ДВССЫЛ(A5)​ Выпадающего списка нужную​ как при обычном​
2. ​ не переместится на​в​невозможно удалить или​ и выбрать отдельных​ А любое значение.​ в Excel» тут.​ в формуле адрес​Теперь выделяем ячейки,​сделать связанные выпадающие списки​ выберите команду Присвоить​ месяцы. С одной​
3. ​Все команды​Выделите на листе ячейку,​ именованные диапазоны для​Важно, чтобы при создании​ ему Страну из​ выделении. Также в​ ячейку G3. У​Перейти к​ изменить имена, определенные​ ячеек или диапазонов​ Главное, чтобы эта​Как настроить Excel,​ ячейки Е1. Формула​ в которые будем​ в Excel​ имя;​ стороны нужно учитывать​.​ содержащую данные или​
4. ​ их Стран.​ правила Проверки данных​ этого Региона.​ данной ситуации особенно​ нас выделилась небольшая​всплывающего окна и​ для ячеек и​ вводя их имена​ ячейка не была​ чтобы при добавлении​ получилась такая.​ устанавливать выпадающий список.​. Ещё один способ​в поле Имя введите:​ тот факт, что​В списке выберите​ текст, который должна​Чтобы не создавать десятки​
5. ​ активной ячейкой была​Таблицу, в которую будут​ полезным будет режим​ строка. Теперь все​ выберите нужный вариант.​ диапазонов. Имена можно​ и ссылок на​ пустой. У нас​ ячеек в список​=ДВССЫЛ(«Товар[«&$E1&»]»)​ Мы выделили диапазон​ сделать раскрывающиеся списки,​
6. ​ Продажи_за_месяц;​ пользователь может добавлять​камеру​ указывать ссылка.​ имен, нужно изменить​B5​

​ заноситься данные с​ после нажатия клавиши​ еще не отпуская​Диапазоны в Excel раньше​ удалять и изменять​ ячейки можно использовать​ – это ячейка​

Выделение диапазонов целых столбцов или строк

​ столбца A, автоматически​Нажимаем «ОК». Получилось так.​ Е1:Е4 (окрашен в​ смотрите в статье​в поле Диапазон введите​ продажи за следующие​, нажмите кнопку​Совет:​ сам подход при​, т.к. мы используем​ помощью Связанного списка,​

​ F8: «Расширить выделенный​ SHIFT, нажмите клавишу​ назывался блоками. Диапазон​ только в диалоговом​ поле имени.​ А2.​

Выделение диапазона целого листа

​ писалось название нового​Можно выпадающие списки сделать​ желтый цвет). Вставляем​ «Связанные выпадающие списки​ формулу =СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)​ после апреля месяцы​Добавить​ Можно также ввести ссылку​ построении Связанного списка.​

Выделение несмежного диапазона

​ относительную адресацию.​ разместим на листе​

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

exceltable.com

​нажмите ОК.​

Блог активной пенсионерки.

суббота, 23 августа 2014 г.

Microsoft Excel. Выделение ячеек. Связные и несвязные диапазоны ячеек.

Каждая ячейка в таблице имеет свой адрес. Он обозначается номером столбца и номером строки.

Столбцы в таблице обозначаются буквой С и цифрами.
Ряды — обозначаются буквой R и цифрами.

Например ячейка , расположенная в 5-м ряду, во втором столбце имеет адрес: R5C2.

Этот адрес показан в поле имени.

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

Ячейка становится активной и туда можно помещать информацию.

Чтобы выделить ряд, нужно щелкнуть на значении ряда.

Чтобы выделить столбец, нужно щелкнуть на значении столбца.

Выделенная совокупность ячеек называется диапазоном.
Диапазоны бывают связные и несвязные.

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

Несвязные – выделенные не смежные ячейки.

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

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

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

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

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

Можно создать связный диапазон с помощью клавиши Shift и клавиши стрелка.

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

Автоматически изменяемые диапазоны ячеек в Excel

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

Как в Excel сделать автоматическое изменение диапазона

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

Сформируйте отчет по доходности нашего объекта инвестирования, так как показано на рисунке:

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

1. Выберите инструмент «Формулы»-«Определенные имена»-«Присвоить имя».
2. Заполните поля диалогового окна «Создание имени» как на рисунке. Обратите внимание на то, что в поле «Диапазон:» мы используем функцию =СМЕЩ, а в одном из ее параметров используем функцию =СЧЕТ. Пример: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
3. Переместите курсор в ячейку D2 и введите функцию =СУММ с именем «доход» в ее параметрах.

Теперь постепенно заполняя ячейки в столбце B, мы можем проследить, как изменяется охват ячеек именем «доход».

Функция СМЕЩ в Excel

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

Функция =СМЕЩ определяет наш диапазон в зависимости от количества заполненных ячеек в столбце B. 5 параметров функции =СМЕЩ(начальная ячейка; смещение размера диапазона по строкам; смещение по столбцам; размер диапазона в высоту; размер диапазона в ширину):

1. «Начальная ячейка» – указывает верхнюю левую ячейку, от которой будет динамически расширяться диапазон как вниз, так и вправо (при необходимости).
2. «Смещение по строкам» – параметр определяет, на какое количество нужно смещать диапазон по вертикали от начальной ячейки (первого параметра). Значения могут быть нулевыми и отрицательными.
3. «Смещение по столбцам» – параметр определяет, на какое количество нужно смещать по горизонтали от начальной ячейки. Значения могут быть даже нулевыми и отрицательными.
4. «Размер диапазона в высоту» – количество ячеек, на которое нужно увеличить диапазон в высоту. По сути, название говорит само за себя.
5. «Размер диапазона в ширину» – количество ячеек, на которое нужно увеличить в ширину от начальной ячейки.

Последние 2 параметра функции являются необязательными. Если их не заполнять, то диапазон будет состоять из 1-ой ячейки. Например: =СМЕЩ(A1;0;0) – это просто ячейка A1, а параметр =СМЕЩ(A1;2;0) ссылается на A3.

Теперь разберем функцию: =СЧЕТ, которую мы указывали в 4-ом параметре функции: =СМЕЩ.

Что определяет функция СЧЕТ

Функция =СЧЕТ($B:$B) автоматически считает количество заполненных ячеек в столбце B.

Таким образом, мы с помощью функции =СЧЕТ() и =СМЕЩ() автоматизируем процесс формирования диапазона для имени «доход», что делает его динамическим. Теперь еще раз посмотрим на нашу формулу, которой мы присвоили имя «доход»: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Читать данную формулу следует так: первый параметры указывает на то, что наш автоматически изменяемый диапазон начинается в ячейке B2. Следующие два параметра имеют значения 0;0 – это значит, что динамический диапазон не смещается относительно начальной ячейки B2. А увеличивается только его размер по вертикали, о чем свидетельствует 4-тый параметр. В нем находится функция СЧЕТ и она возвращает число равно количеству заполненных ячеек в столбце B. Соответственно количество ячеек по вертикали в диапазоне будет равно числу, которое нам даст функция СЧЕТ. А за ширину диапазона у нас отвечает последний 5-тый параметр, где находиться число 1.

Благодаря функции СЧЕТ мы рационально загружаем в память только заполненные ячейки из столбца B, а не весь столбец целиком. Данный факт исключает возможные ошибки связанные с памятью при работе с данным документом.

Динамические диаграммы в Excel

У нас есть динамическое имя, теперь создадим динамическую диаграмму для данного типа отчета:

1. Выделите диапазон B2:B6 и выберите инструмент: «Вставка»-«Диаграммы»-«Гистограмма»-«Гистограмма с группировкой».
2. Щелкните левой кнопкой мышки по любому столбцу гистограммы и в строке формул отобразится функция диаграммы: =РЯД().
3. В строке формул измените параметры функции: =РЯД(Лист1!$B$1;;Лист1!$B$2:$B$7;1) на =РЯД(Лист1!$B$1;;Лист1!доход;1).
4. В отчет добавьте новую запись в ячейки A8 — «июль», B8 — «77000» соответственно. И убедитесь, что на диаграмме автоматически добавился новый столбец.

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

Именованный диапазон в Excel

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

Имя ячейки

Начнем с простого — присвоим имя ячейке. Для этого просто выделяем ее (1) и в поле имени (2) вместо адреса ячейки указываем произвольное название, которое легко запомнить.

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

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

Именованный диапазон

Аналогичным образом можно задать имя и для диапазона ячеек, то есть выделим диапазон (1) и в поле имени укажем его название (2):

Далее это название можно использовать в формулах, например, при вычислении суммы:

Также создать именованный диапазон можно с помощью вкладки Формулы , выбрав инструмент Задать имя .

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

Для работы с существующими диапазонами на вкладке Формулы есть Диспетчер имен .

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

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

Именованный диапазон из таблицы

Если же диапазон значений имеет заголовки или речь идет о таблице, то стоит воспользоваться инструментом Создать из выделенного . Как понятно из его названия, предварительно необходимо выделить диапазон или таблицу (1). Затем указываем место, в котором находятся заголовки (3).

В результате Эксель автоматически создаст диапазоны по заголовкам.

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

Использование именованных диапазонов

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

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

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

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

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

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

Ну и более наглядно и подробно об именованных диапазонах смотрите в видео:

Связанные (зависимые) выпадающие списки

Способ 1. Функция ДВССЫЛ (INDIRECT)

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT), которая умеет делать одну простую вещь — преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, «перевод стрелок» 😉

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota. В Excel 2003 и старше — это можно сделать в меню Вставка — Имя — Присвоить (Insert — Name — Define). В Excel 2007 и новее — на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager). Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan.

При задании имен помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку и откройте меню Данные — Проверка (Data — Validation) или нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data) если у вас Excel 2007 или новее. Затем из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов:

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

где F3 — адрес ячейки с первым выпадающим списком (замените на свой).

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

Минусы такого способа:

• В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав отсортированный список соответствий марка-модель (см. Способ 2).
• Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE), т.е. формула будет выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;» «;»_»))
• Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).

Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

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

• дать имя диапазону D1:D3 (например Марки) с помощью Диспетчера имен (Name Manager) с вкладки Формулы (Formulas) или в старых версиях Excel — через меню Вставка — Имя — Присвоить (Insert — Name — Define)
• выбрать на вкладке Данные (Data) команду Проверка данных (Data validation)
• выбрать из выпадающего списка вариант проверки Список (List) и указать в качестве Источника (Source)=Марки или просто выделить ячейки D1:D3 (если они на том же листе, где список).

А вот для зависимого списка моделей придется создать именованный диапазон с функцией СМЕЩ (OFFSET), который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого:

• Нажмите Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas). В версиях до 2003 это была команда меню Вставка — Имя — Присвоить (Insert — Name — Define)
• Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов — не пугайтесь 🙂

Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)

• начальная ячейка — берем первую ячейку нашего списка, т.е. А1
• сдвиг_вниз — нам считает функция ПОИСКПОЗ (MATCH), которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А)
• сдвиг_вправо = 1, т.к. мы хотим сослаться на модели в соседнем столбце (В)
• размер_диапазона_в_строках — вычисляем с помощью функции СЧЕТЕСЛИ (COUNTIF), которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений — марок авто (G7)
• размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с моделями

В итоге должно получиться что-то вроде этого:

Осталось добавить выпадающий список на основе созданной формулы к ячейке G8. Для этого:

• выделяем ячейку G8
• выбираем на вкладке Данные (Data) команду Проверка данных (Data validation) или в меню Данные — Проверка (Data — Validation)
• из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак равно и имя нашего диапазона, т.е. =Модели

Microsoft Excel

трюки • приёмы • решения

Как распечатать несмежные диапазоны таблицы Excel на одной странице

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

Нажмите Ctrl во время выбора диапазонов для печати, а затем выберите Разметка страницы ► Параметры страницы ► Область печати ► Задать. После того как вы определили область печати, вы можете использовать команду Разметка страницы ► Параметры страницы ► Область печати ► Добавить в область печати для указания дополнительных диапазонов для печати.

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

1. Выберите диапазон.
2. Нажмите Ctrl+C.
3. Активизируйте другую ячейку.
4. Выполните команду Главная ► Буфер обмена ► Вставить ► Связанный рисунок.

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

Использование инструмента Камера

Чтобы добавить инструмент Камера на панель быстрого доступа, выполните следующие действия.

1. Щелкните правой кнопкой мыши на панели быстрого доступа и выберите Настройка панели быстрого доступа для открытия раздела Панель быстрого доступа окна Параметры Excel .
2. В раскрывающемся списке слева выберите Команды не на ленте.
3. Выберите Камера в списке.
4. Нажмите кнопку Добавить, чтобы добавить инструмент на панель быстрого доступа.
5. Нажмите кнопку ОК для закрытия диалогового окна Параметры Excel.

Инструмент Камера создает связанный рисунок диапазона ячеек. Поэтому если данные в связанном диапазоне изменяются, рисунок обновляется автоматически. На рис. 185.1 показан лист, с которого нужно напечатать два диапазона (А3:С10 и E13:G20) на отдельной странице.

Рис. 185.1. Обычно нет возможности распечатать диапазоны А3:С10 и E13:G20 на одной странице

Съемка камерой

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

1. Убедитесь, что инструмент Камера доступен на панели быстрого доступа. Выполните шаги из предыдущего подраздела, если это не так.
2. Вставьте новый лист (Лист2), который будет содержать связанные рисунки.
3. Выберите диапазон А3:С10 и щелкните на инструменте Камера.
4. Активизируйте лист Лист2 и нажмите кнопку для вставки связанного рисунка.
5. Вернитесь к предыдущему листу, выберите E13:G20 и нажмите кнопку инструмента Камера.
6. Активизируйте Лист2 и вставьте связанный рисунок.
7. Используйте мышь, чтобы разместить связанные рисунки так, как вам нужно.

Вы можете отключить отображение сетки на листе, который содержит связанные рисунки (рис. 185.2). Если данные листа Лист1 изменятся, то связанные рисунки также обновятся. При печати листа Лист2 оба диапазона окажутся на одной странице.

Рис. 185.2. Использование связанных рисунков позволяет печатать два диапазона ячеек на одной странице