Извлечь первые N символов ячейки Excel

Простейшая задача — взять и отделить от ячейки первый символ, скопировав его в соседний столбец. Или выбрать и оставить в ней первые 2, 3, 4… N символов.

Например, в строках переменной длины первые 6 символов — определенный цифровой код, который нужно скопировать в отдельный столбец.

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

Рассмотрим все известные решения, снабдив их примерами. Итак, поехали.

Ошибка #ЧИСЛО!

Ошибка #ЧИСЛО! в Excel выводится, если в формуле содержится некорректное число. Например:

  1. Используете отрицательное число, когда требуется положительное значение.

Ошибки в Excel – Ошибка в формуле, отрицательное значение аргумента в функции КОРЕНЬ

Устранение ошибки: проверьте корректность введенных аргументов в функции.

  1. Формула возвращает число, которое слишком велико или слишком мало, чтобы его можно было представить в Excel.

Ошибки в Excel – Ошибка в формуле из-за слишком большого значения

Устранение ошибки: откорректируйте формулу так, чтобы в результате получалось число в доступном диапазоне Excel.

ИД можно создавать массово на основе ваших таблицы и заголовков столбцов/строк. Выделите необходимый диапазон данных и выберите пункт меню Создать из выделенного . Будет предложен такой диалог:

Укажите в нём, где располагаются имена ваших ИД. Если вы укажете, что имена расположены в верхней строке, то Excel создаст ИД для каждой строки и назовёт их теми идентификаторами, которые в верхней строке располагаются. В предложенном примере мы имеем 5 столбцов и после нажатия OK можно проконтролировать, что создано 5 ИД с соответствующими именами:

Тоже самое можно проделать и со строками, если на то есть необходимость.

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

  1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
  2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как присвоить имя ячейки в Excel

Всем привет. Сегодня расскажу о добавлении имени к ячейке или диапазону. Все знают, чтобы сослаться на ячейку в формуле, нужно указать её координаты – номер строки и имя столбца. А что, если можно присвоить имя ячейке (или диапазону) и обращаться по имени? Давайте сравним две формулы. Здесь я рассчитал рентабельность продаж без присвоения имён:

Формула без именованных ячеек

А здесь я присвоил имена ячейкам и сделал тот же самый расчёт:

Формула с поименованными ячейками

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

Именованную ячейку легко найти – выберите её в раскрывающемся списке Имя (слева от строки формул). Используя названия, вы не ошибётесь в ссылке, а при вводе, Excel предложит автозавершение.

Если я вас убедил, давайте учиться именовать ячейки!

Как выбрать имя для ячеек Эксель

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

  • Используйте содержательные имена, чтобы по ним можно было однозначно трактовать содержание. Например, «Курс_валюты», «Индекс»инфляции» и т.п. Выбирайте короткие, но информативные названия
  • Имя не должно начинаться с цифры. Не используйте знаков пунктуации, кроме подчеркивания(«_»), обратного слеш («\») и точки
  • Длина имени не может превышать 255 символов, но, повторюсь, старайтесь использовать слова покороче.
  • У вас не получится создать имя, совпадающее с координатами существующей ячейки. Рекомендую исключить применение названий, похожих на традиционные ссылки
  • В Excel есть служебные имена диапазонов, никогда их не используйте для именования своих массивов. Например, зарезервированы: «Область_печати», «Заголовки_для_печати» и др.

Использование поля «Имя» для назначение имени ячейкам

Один из лучших способов работы с поименованными ячейками – использовать поле со списком «Имя», слева от строки формул. Если имя ячейки не задано, в нем отображаются координаты активной ячейки. Если задано – вы увидите это имя. Чтобы задать название активной ячейки или диапазона – выделите диапазон и запишите его имя в поле «Имя». Нажмите Enter.

Поле «Имя» в Microsoft Excel

Чтобы активизировать ячейку с заданным именем – кликните по её названию в раскрывающемся списке поля «Имя»

Окно «Создание имени»

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

Активизируйте нужный массив или ячейку и выполните на ленте: Формулы – Определенные имена – Присвоить имя.

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

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

Окно создания имени в MS Excel

Создание имён из выделенных таблиц

Предположим, у вас есть таблица исходных данных для расчётов. В столбце «А» записаны названия параметров, а в столбце «В» — их значения. Нужно назвать эти значения именами, указанными в столбце А.

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

Программа сама создаёт названия и присваивает имена из столбца слева.

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

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

Использование диспетчера имён

Отличный инструмент работы с именами в Эксель называется «Диспетчер имён». Здесь собрано много команд и настроек работы с поименованными ячейками.

Чтобы запустить диспетчер – выполните: Формулы – Определение имени – Диспетчер имён, или нажмите Ctrl+F3.

Диспетчер имён Эксель

В открывшемся окне вы увидите список уже заданных имён и кнопки управления. С помощью диспетчера вы сможете:

  1. Увидеть весь список заданных имён и содержание именованных ячеек
  2. Отфильтровать список для последующей чистки и корректировки. Для этого нажмите фильтр, выберите один или несколько параметров отбора
  3. Изменить название

Выпадающий список в Excel с подстановкой данных (+ с использованием функции СМЕЩ)

В приведенном случае СМЕЩ

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

Чтобы создать динамический перечень с поддержкой ввода новой информации, необходимо:

  1. Осуществить выделение интересующей ячейки.
  2. Раскрыть вкладку «Данные» и нажать по «Проверка данных».
  3. В открывшемся окошке снова осуществляем выбор пункта «Список» и источником данных указываем такую формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
  4. Нажимаем «ОК».

Здесь содержится функция СЧЕТЕСЛИ

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

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

Манипуляции с именованными областями

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

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

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

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

как сделать именованный диапазон в excel

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

как сделать именованный диапазон в excel

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

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

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

как сделать именованный диапазон в excel

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

  1. Выделяем массив, над которым требуется выполнить операцию. Клацаем по выделению правой кнопкой мыши. В открывшемся списке останавливаем выбор на варианте «Присвоить имя…».
  2. Открывается окошко создания названия. В область «Имя» следует вбить наименование в соответствии с озвученными выше условиями. В области «Диапазон» отображается адрес выделенного массива. Если вы провели выделение верно, то вносить изменения в эту область не нужно. Жмем по кнопке «OK».
  3. Как можно видеть в поле имён, название области присвоено успешно.

как сделать именованный диапазон в excel

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

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

как сделать именованный диапазон в excel

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

  1. Выделяем массив. На вкладке «Формулы», клацаем по крупному значку «Диспетчер имен», расположенному всё в той же группе «Определенные имена». Или же можно вместо этого применить нажатие сочетания клавиш Ctrl+F3.
  2. Активируется окно Диспетчера имён. В нем следует нажать на кнопку «Создать…» в верхнем левом углу.
  3. Затем запускается уже знакомое окошко создания файлов, где нужно провести те манипуляции, о которых шёл разговор выше. То имя, которое будет присвоено массиву, отобразится в Диспетчере. Его можно будет закрыть, нажав на стандартную кнопку закрытия в правом верхнем углу.

как сделать именованный диапазон в excel

Урок: Как присвоить название ячейке в Экселе

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

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

Читайте также:  Как запустить макрос в Excel разными способами

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

  1. Прежде всего, на листе со списком присваиваем диапазону наименование любым из тех способов, о которых шла речь выше. В итоге, при выделении перечня в поле имён у нас должно отображаться наименование данного массива. Пусть это будет наименование «Модели».
  2. После этого перемещаемся на лист, где находится таблица, в которой нам предстоит создать выпадающий список. Выделяем область в таблице, в которую планируем внедрить выпадающий список. Перемещаемся во вкладку «Данные» и щелкаем по кнопке «Проверка данных» в блоке инструментов «Работа с данными» на ленте.
  3. В запустившемся окне проверки данных переходим во вкладку «Параметры». В поле «Тип данных» выбираем значение «Список». В поле «Источник» в обычном случае нужно либо вручную вписать все элементы будущего выпадающего списка, либо дать ссылку на их перечень, если он расположен в документе. Это не очень удобно, особенно, если перечень располагается на другом листе. Но в нашем случае все намного проще, так как мы соответствующему массиву присвоили наименование. Поэтому просто ставим знак «равно» и записываем это название в поле. Получается следующее выражение:

как сделать именованный диапазон в excel

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

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

как сделать именованный диапазон в excel

  1. Прежде всего, каждой строке соответствующего филиала в таблице присвоим название. Для Филиала 1 выделяем область с ячейками, в которых содержатся данные о выручке по нему за 3 месяца. После выделения в поле имен пишем наименование «Филиал_1» (не забываем, что название не может содержать пробел) и щелкаем по клавише Enter. Наименование соответствующей области будет присвоено. При желании можно использовать любой другой вариант присвоения наименования, о котором шел разговор выше.
  2. Таким же образом, выделяя соответствующие области, даем названия строкам и других филиалов: «Филиал_2», «Филиал_3», «Филиал_4», «Филиал_5».
  3. Выделяем элемент листа, в который будет выводиться итог суммирования. Клацаем по иконке «Вставить функцию».
  4. Инициируется запуск Мастера функций. Производим перемещение в блок «Математические». Останавливаем выбор из перечня доступных операторов на наименовании «СУММ».
  5. Происходит активация окошка аргументов оператора СУММ. Данная функция, входящая в группу математических операторов, специально предназначена для суммирования числовых значений. Синтаксис представлен следующей формулой:

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

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

Итак, устанавливаем курсор в поле «Число1». Так как мы дали названия диапазонам, которые требуется сложить, то не нужно ни вписывать координаты в поле, ни выделять соответствующие области на листе. Достаточно просто указать название массива, который подлежит сложению: «Филиал_1». В поля «Число2» и «Число3» соответственно вносим запись «Филиал_3» и «Филиал_5». После того, как вышеуказанные манипуляции были сделаны, клацаем по «OK».

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

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

Урок: Как посчитать сумму в Майкрософт Эксель

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

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

  1. Чтобы перейти в Диспетчер, перемещаемся во вкладку «Формулы». Там следует кликнуть по иконке, которая так и называется «Диспетчер имен». Указанная иконка располагается в группе «Определенные имена».
  2. После перехода в Диспетчер для того, чтобы произвести необходимую манипуляцию с диапазоном, требуется найти его название в списке. Если перечень элементов не очень обширный, то сделать это довольно просто. Но если в текущей книге располагается несколько десятков именованных массивов или больше, то для облегчения задачи есть смысл воспользоваться фильтром. Клацаем по кнопке «Фильтр», размещенной в правом верхнем углу окна. Фильтрацию можно выполнять по следующим направлениям, выбрав соответствующий пункт открывшегося меню:
    • Имена на листе;
    • в книге;
    • с ошибками;
    • без ошибок;
    • Определенные имена;
    • Имена таблиц.

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

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

Помогла ли вам эта статья?

Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:

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

Все это в сумме не даст вам скучать 😉

Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.

Способ 1. Умная таблица

Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):

Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name).

Теперь можно использовать динамические ссылки на нашу «умную таблицу»:

  • Таблица1 – ссылка на всю таблицу кроме строки заголовка (A2:D5)
  • Таблица1 – ссылка на всю таблицу целиком (A1:D5)
  • Таблица1 – ссылка на диапазон-столбец без первой ячейки-заголовка (C2:C5)
  • Таблица1 – ссылка на «шапку» с названиями столбцов (A1:D1)

Такие ссылки замечательно работают в формулах, например:

=СУММ(Таблица1) – вычисление суммы по столбцу «Москва»

=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)

Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:

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

При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT), которая превращает текст в ссылку:

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

Способ 2. Динамический именованный диапазон

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

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

Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.

Ищем последнюю ячейку с помощью ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.

Суть трюка проста. ПОИСКПОЗ перебирает в поиске ячейки в диапазоне сверху-вниз и, по идее, должна остановиться, когда найдет ближайшее наименьшее значение к заданному. Если указать в качестве искомого значение заведомо больше, чем любое имеющееся в таблице, то ПОИСКПОЗ дойдет до самого конца таблицы, ничего не найдет и выдаст порядковый номер последней заполненной ячейки. А нам это и нужно!

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

Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.

Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» — последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:

Формируем ссылку с помощью ИНДЕКС

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

ИНДЕКС(диапазон; номер_строки; номер_столбца)

Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.

Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.

И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:

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

Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager). В открывшемся окне нажмите кнопку Создать (New), введите имя нашего диапазона и формулу в поле Диапазон (Reference):

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

Ссылки по теме

  • Использование функции ВПР (VLOOKUP) для связывания таблиц и подстановки значений
  • Как создать автоматически наполняющийся выпадающий список
  • Как создать сводную таблицу для анализа большого массива данных

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

Итак, несколько советов, которые сделают вашу работу с именованными диапазонами в Excel более быстрой и продуктивной.

Многоразовое создание именованного диапазона в один прием

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

К примеру, у вас имеется набор данных (как показано ниже) и вы хотите создать отдельные именованные диапазоны для каждой колонки. Вместо того чтобы создавать их по одному, вы можете воспользоваться сочетанием клавиш CTRL + SHIFT + F3, которое откроет диалоговое окно Создание имен из выделенного диапазона. Тоже самое окно доступно во вкладке Формулы -> Определенные имена –> Создать из выделенного. Теперь вы можете создать больше одного диапазона – по строкам, столбцам, оба варианта.

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

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

Чтобы открыть диалоговое окно Диспетчер имен, перейдите по вкладке Формулы в группу Определенные имена и щелкните по кнопке Диспетчер имен. Либо нажатием сочетаний клавиш Ctrl + F3.

Использование формулы СМЕЩ

Именованные диапазоны и вполовину не были бы такими полезными и интересными без формулы СМЕЩ. Функция СМЕЩ помогает позиционировать и расширять данный диапазон. Результатом использования ее может стать мощный динамический диапазон, который имеет способность расширяться и изменяться.

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

На самом деле не уверен, это конструктивная особенность или ошибка. Используя относительные ссылки (A1 вместо $A$1) при определении именованного диапазона, они не остаются на том же месте, как бы вы этого не хотели. Давайте рассмотрим этот случай на примере. Предположим, вы хотите создать диапазон, который смещается вниз на 10 строк от ячейки A1. Первое, что приходит в голову, это написать формулу =СМЕЩ(A1;10;0).

Пока все хорошо. Если вы захотите воспользоваться этим именованным диапазоном, необходимо подобрать для нее ячейку (скажем B1) и ввести что-то типа =мой_имен_диап. Где мой_имен_диап — это имя, которое вы дали диапазону на предыдущем шаге.

Но если вы выберите другую ячейку и снова откроете диспетчер имен, формула, которую вы ввели до этого =СМЕЩ(Лист1!A1;10;0), магическим образом преобразится (к примеру, =СМЕЩ(Лист1!A1048576;10;0)). Это происходит потому, что при создании именованного диапазона мы использовали относительные ссылки, т.е. целевой диапазон будет все время смещаться в зависимости от адреса, выбранной на данный момент, ячейки.

Использование F2 для изменения именованного диапазона

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

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

Возможно у вас имеются свои трюки по использованию именованных диапазонов?! Не хотите поделиться?)

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

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

Когда вы открываете электронную таблицу в Excel, столбцы обозначаются буквами в алфавитном порядке, начиная с A, а строки нумеруются, начиная с 1. Это настройка по умолчанию для электронной таблицы Excel. Это позволяет легко узнать, где вы находитесь в электронной таблице. Обозначение «А1» означает, что вы находитесь в первом столбце и первой строке. Обозначение ABC123 можно изменить на альтернативный стиль, известный как стиль R1C1, выбрав «Инструменты», а затем «Параметры». Перейдите на вкладку «Общие» и выберите «Стиль ссылок R1C1». «R» обозначает строку, а «C» обозначает столбец. Это может сбивать с толку, потому что в стиле по умолчанию первое обозначение — это столбец, а второе — строка. B2 (стиль по умолчанию) становится R2C2 в этом формате.

Когда вы создаете электронную таблицу, вы можете назвать отдельные столбцы и строки, чтобы объяснить ваши данные. (Помните, что строки и столбцы будут ABC123 или R1C1, в зависимости от ваших предпочтений). Как правило, вы начинаете называть заголовки столбцов в A1 и строки в A2. Это ячейки, которые вы можете изменить для обозначения заголовков столбцов. Например, вы можете назвать столбцы «Фамилия», «Имя», «Адрес» и так далее. Вы можете назвать строки «январь», «февраль» и так далее. При добавлении или изменении столбцов может возникнуть необходимость переименовать заголовки столбцов. Самый простой способ изменить заголовок столбца в ячейке — щелкнуть в ячейке, чтобы сделать ее активной, а затем ввести новый заголовок столбца. Заголовок столбца может состоять из букв и цифр, но если вы используете все цифры, столбец будет автоматически выровнен по правому краю. Чтобы изменить выравнивание столбца по левому краю, выберите «Формат», затем «Ячейки», щелкните вкладку «Выравнивание», а затем выберите «Горизонтально (слева)». Возможно, вам также придется изменить формат на «Текст», а не по умолчанию «Общий». Кроме того, вы можете ввести новое имя непосредственно в строке «Формула», расположенной чуть выше обозначений столбцов.

Как добавить префикс для нескольких (или всех) записей в столбце

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

Формула для оператора & выглядит следующим образом:

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

Например. Мы создаем список в столбце C. Если первая ячейка в столбце, для которой нам нужно добавить префикс, – это A3, а префикс – это TWC, формула будет иметь вид:

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

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

Нажмите Enter, когда закончите, и он добавит префикс к каждой записи в предполагаемом столбце.

В качестве альтернативы вы можете использовать функцию CONCATENATE для добавления префикса:

В примере, упомянутом выше, функция станет:

Как добавить суффикс для нескольких (или всех) записей в столбце

Мы могли бы использовать две функции для добавления суффикса к нескольким (или всем) записям в столбце. Первый использует оператор &, а второй – функцию CONCATENATE.

Процедура добавления суффикса с использованием оператора & выглядит следующим образом:

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

Например. Мы создаем список в столбце C. Если первой ячейкой в ​​столбце, для которого нам нужно добавить суффикс, является A3, а суффикс находится в состоянии ожидания, формула будет иметь вид:

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

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

Когда закончите, нажмите Enter, и он добавит суффикс к каждой записи в предполагаемом столбце.

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

Как настроить зависимые выпадающие списки в Excel с поиском

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

Для второго перечня нужно ввести формулу:

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

ПОИСКПОЗ возвращает номер ячейки с выбранным в первом списке (E6) городом в указанной области SA:$A.
СЧЕТЕСЛИ считает количество совпадений в диапазоне со значением в указанной ячейке (E6).


Мы получили связанные выпадающие списки в Excel с условием на совпадение и поиском диапазона для него.

Пример!

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

Допустим, формула имеет следующий вид:

Mx(z) = R *( z b1 ) — F1 *( z b2 ) — F1 *( z b3 ) — F1 *( z b4 ) — F2 *( z b5 ) — q *( z b1 ) 2 /2

Открываем Excel и создаем таблицу.

1. В ячейки B3-B14 вводим наименования параметров, в ячейки C3-C14 вписываем их буквенно-цифровые обозначения так, как они обозначаются в вышеприведенной формуле (можно со знаками «=», Excel их отбросит при автоматическом присвоении имен), а в ячейки D3-D13 заносим числовые значения исходных данных.

2. Если сейчас ввести в ячейку D14 формулу да еще с применением различных видов ссылок на ячейки (относительных – типа «A1», абсолютных — типа «$A$1» или смешанных – «A$1» или «$A1»), то в строке формул мы увидим нечто трудно читаемое, что изображено на скриншоте ниже.

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

3.1. Выделяем диапазон C3-D14.

3.2. Выбираем в главном меню программы MS Excel «Вставка» — «Имя» — «Создать».

3.3. В появившемся окошке «Создать имена» выбираем «По тексту в столбце слева» и закрываем окно кнопкой «ОК».

Теперь ячейкам D3-D14 присвоены имена в соответствии с записями в ячейках C3-C14. После ввода формулы в ячейку D14 вверху в строке формул мы увидим достаточно легко читаемое выражение.

Обратите внимание на то, как Excel назначил имена ячейкам!

У имен переменных F1 , F2 , R , b1 , b2 , b3 , b4 , b5 , справа появилось нижнее подчеркивание. Дело в том, что Excel не может разным ячейкам листа дать одинаковые имена! Поэтому, например, ячейке D6 присвоено имя F1_ , а не просто F1 , так как на листе уже есть ячейка с именем-адресом F1.

Основные действия с диапазонами

Выделение диапазонов

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

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

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

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

Сравнение диапазонов

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

Например, для построчного сравнения часто используется логическая функция «ЕСЛИ» и какой-либо из операторов сравнения (также можно использовать и другие функции, например «СЧЕТЕСЛИ» из категории статистические для проверки вхождения элементов одного списка в другой).

Также для поиска отличий по столбцам или по строкам используется стандартное средство Excel, которое находится на вкладке «Главная», в группе кнопок «Редактирование», в меню кнопки «Найти и выделить». Если в этом меню выбрать пункт «Перейти» и далее нажать кнопку «Выделить», то в диалоговом окне «Выделение группы ячеек» можно выбрать одну из опций «Отличия по строкам» или «Отличия по столбцам».

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

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

Изменение (преобразование) диапазонов значений

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

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

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

Как редактировать имена с помощью диспетчера имен

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

05_clicking_formulas_tab

В разделе «Определенные имена» вкладки «Формулы» нажмите «Диспетчер имен».

06_clicking_name_manager

Откроется диалоговое окно «Диспетчер имен». Чтобы отредактировать существующее имя, выберите имя в списке и нажмите «Изменить». Например, мы собираемся отредактировать название «Продукты».

07_clicking_edit_on_name_manager

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

ПРИМЕЧАНИЕ. Вы увидите, что раскрывающийся список «Область» недоступен. Когда вы редактируете существующее имя, вы не можете изменить «Scope» этого имени. Вы должны выбрать область при первом создании имени. Если вы хотите, чтобы областью действия была конкретная рабочая таблица, а не вся рабочая книга, вы можете создать имя таким способом, который позволит вам изначально указать область. Мы покажем вам, как это сделать, в следующем разделе.

08_edit_name_dialog

Например, скажем, мы добавили еще один продукт в нашу «Базу данных продуктов» и хотим включить его в диапазон ячеек под названием «Продукты». Когда мы нажимаем кнопку «Развернуть диалог», диалоговое окно «Редактировать имя» сжимается и содержит только поле редактирования «Относится к». Мы выбираем диапазон ячеек непосредственно на листе «База данных продуктов», включая строку, содержащую добавленный продукт. Имя листа и диапазон ячеек автоматически вводятся в поле «Относится к». Чтобы подтвердить свой выбор и вернуться в полное диалоговое окно «Редактировать имя», нажмите кнопку «Свернуть диалоговое окно». Нажмите «ОК» в диалоговом окне «Изменить имя», чтобы принять изменения в имени.

08a_selecting_new_cell_range

Об искусстве программирования

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

Вручную человек легко решает задачи подобного рода, например, извлечение фамилии из ФИО. Для этого у человека есть разум, который помогает ему в поиске и выборе. У компьютера и его основного «работника» процессора разума НЕТ! Процессор умеет только считать. Цифры для компьютера должны быть окончательными и однозначными.

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

Компьютер же может только считать, повторю это в который раз! Складывать, умножать, сравнивать… Он даже вычитает путем сложения с отрицательными числами, и делит не совсем так, как мы могли бы сделать, скажем, «столбиком».

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

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

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

Ссылка на основную публикацию