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

Содержание

Функция СУММЕСЛИ

Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;»> 5″)

Это видео — часть учебного курса Сложение чисел в Excel.

Советы: 

  • При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; «Иван»; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны «Иван».

  • Если необходимо выполнить суммирование ячеек в соответствии с несколькими условиями, используйте функцию СУММЕСЛИМН.

Важно: Функция СУММЕ ЕСЛИ возвращает неправильные результаты, если она используется для совпадения строк длиной более 255 знаков или строки #VALUE!.

Синтаксис

СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

Аргументы функции СУММЕСЛИ описаны ниже.

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

  • Условие   .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Можно включит под вопросицу (?), которая соответствует любому одиночному символу, звездочка (*) — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (

    ~).

    Например, условия могут быть выражены как 32, «>32», B5, «3?», «яблок*», «*~?», или СЕГОДНЯ().

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

  • Диапазон_суммирования   .Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе

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

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

    диапазон

    Диапазон_суммирования.

    Фактические суммные ячейки

    A1:A5

    B1:B5

    B1:B5

    A1:A5

    B1:K5

    B1:B5

Примеры

Пример 1

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Стоимость имущества

Комиссионные

Данные

1 000 000 ₽

70 000 ₽

2 500 000 ₽

2 000 000 ₽

140 000 ₽

3 000 000 ₽

210 000 ₽

4 000 000 ₽

280 000 ₽

Формула

Описание

Результат

=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)

Сумма комиссионных за имущество стоимостью больше 1 600 000 ₽.

630 000 ₽

=СУММЕСЛИ(A2:A5; «>160000»)

Сумма по имуществу стоимостью больше 1 600 000 ₽.

9 000 000 ₽

=СУММЕСЛИ(A2:A5;300000;B2:B5)

Сумма комиссионных за имущество стоимостью 3 000 000 ₽.

210 000 ₽

=СУММЕСЛИ(A2:A5;»>» &C2;B2:B5)

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

490 000 ₽

Пример 2

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. Кроме того, вы можете настроить ширину столбцов в соответствии с содержащимися в них данными.

Категория

Продукты

Объем продаж

Овощи

Помидоры

23 000 ₽

Овощи

Сельдерей

55 000 ₽

Фрукты

Апельсины

8 000 ₽

Масло

4 000 ₽

Овощи

Морковь

42 000 ₽

Фрукты

Яблоки

12 000 ₽

Формула

Описание

Результат

=СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7)

Объем продаж всех продуктов категории «Фрукты».

20 000 ₽

=СУММЕСЛИ(A2:A7;»Овощи»;C2:C7)

Объем продаж всех продуктов категории «Овощи».

120 000 ₽

=СУММЕСЛИ(B2:B7;»*ы»;C2:C7)

Объем продаж всех продуктов, названия которых заканчиваются на «ы» («Помидоры» и «Апельсины»).

43 000 ₽

=СУММЕСЛИ(A2:A7;»»;C2:C7)

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

4 000 ₽

К началу страницы

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Функция СУММЕСЛИМН суммирует все аргументы, которые соответствуют нескольким условиям

Функция СУММКВ суммирует несколько значений, предварительно возведя каждое из них в квадрат

Функция СЧЁТЕСЛИ подсчитывает только те значения, которые соответствуют одному условию

Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям

Функция ЕСЛИМН (Office 365, Excel 2016 и более поздние версии)

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Математические и тригонометрические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Сообщение об ошибке при сортировке диапазона, содержаного объединенные ячейки в Excel — Office

  • Чтение занимает 2 мин
  • Применяется к:
    Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007

В этой статье

Примечание

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

Симптомы

При сортировке диапазона в листах Microsoft Excel Excel не сортировать диапазон. Кроме того, может появиться следующее сообщение об ошибке:

Эта операция требует одинакового размера объединенных ячеек.

Причина

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

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

Обходной путь

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

Общее решение

  1. Выберите весь диапазон, который необходимо сортировать.
  2. В группе Выравнивание на вкладке Главная выберите диалоговое окно Выравнивание.
  3. Выберите вкладку Выравнивание, а затем очистим поле ячейки Merge.
  4. Нажмите кнопку ОК.

Примечание

Это может изменить расположение данных в диапазоне.

Пример решения

  1. В таблицу введите следующие данные:

    A1: B1: Name C1: Value
    A2: B2: Sue  C2: 1
    A3: B3: Tom  C3: 2
    
  2. Объединение ячеек A1 и B1, A2 и B2, а также A3 и B3. Для этого выполните следующие действия:

    1. Выберите каждую пару ячеек.

    2. Выберите диалоговое окно Выравнивание в группе Выравнивание на вкладке Главная.

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

    4. Нажмите кнопку ОК.

    Не объединяй ячейки в столбце C.

  3. Выберите ячейки A1:C3, выберите фильтр сортировки & в группе редактирования на вкладке Главная, а затем нажмите настраиваемый сорт.

  4. В поле Сортировка выберите «Column C» рядом с Sort By и выберите ОК. Сообщение об ошибке должно быть описано выше.

  5. Чтобы устранить проблему, сделайте одно из следующих:

    1. Разгружая ячейки A1:B3, чтобы в выборе не было объединенных ячеек.
    2. Объединяем ячейки C1 и D1, C2 и D2 и C3 и D3, чтобы столбец C был таким же размером (слит) как столбец A/B. Затем выберите ячейки A1:D3 и повторите шаги 3 и 4 с помощью единого размера диапазона.

Excel. Как менять диапазон ячеек в формулах

Безусловно, понятие диапазона в Excel – одно из ключевых. Что это такое? Все мы знаем, что лист состоит из ячеек. Вот если несколько из них содержат какую-то информацию, то это диапазон. Простыми словами, это две и более ячейки в документе.

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

Как выбирать ячейки, строки и столбцы

Ячейка – это элемент, содержащий определенную информацию или который может ее содержать. Строка – это ячейки в ряд. Столбец, соответственно, в колонку. Все просто. 

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

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

1

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

2

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

3

Диапазон ячеек: пример

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

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

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

4

Как заполнить диапазон

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

  1. Ввести нужное значение в ячейку B2. Оно может быть как числовым, так и текстовым. Также возможно введение формулы. В нашем случае это цифра 2.

    5

  2. Далее нажимаем маркер автозаполнения (как раз тот самый квадратик, который мы ранее просили не нажимать) и перетаскиваем его вниз до конца диапазона.

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

6

Автозаполнение – одна из самых востребованных функций Excel. Она позволяет осуществлять запись в ячейки диапазона не только одного значения, но и целого набора данных, которые соответствуют определенной закономерности. Например, числовой ряд 2, 4, 6, 8, 10 и так далее.

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

78

Аналогично можно заполнять диапазон нужными датами, которые также подчиняются определенной закономерности. Для этого, давайте, введем дату 13 июня 2013 года и дату 16 июня 2013 года в американском формате.

9

После этого осуществляем уже знакомое нами перетаскивание.

10

Перемещение диапазона

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

Потом нужно просто переместить его в нужное место и отпустить мышь.

1112

Копирование и вставка диапазона

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

Чтобы это сделать, нужно выделить диапазон, сделать по нему правый клик мыши и нажать «Копировать». Также можно воспользоваться комбинацией клавиш Ctrl + C.

13

Также можно найти специальную кнопку на вкладке «Главная» в группе «Буфер обмена». 

Следующий этап – вставить нужную информацию в другое место. Для этого необходимо найти ячейку, которая будет служить левым верхним углом диапазона, после чего аналогично вызвать контекстное меню, но при этом найти пункт «Вставить». Также можно воспользоваться стандартной комбинацией Ctrl + V, которая работает абсолютно в любых программах.

14

Как вставить определенную строку или столбец

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

15

Только после этого нужно сделать правый клик мыши и нажать на кнопку «Вставить», которая находится чуть ниже.

16

Таким вот способом у нас получилось вставить строку.

17

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

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

Назначить имя диапазону можно через Диспетчер имен, который можно найти в меню Формулы – Определенные имена – Диспетчер имен.

Но вообще, способов несколько. Давайте рассмотрим некоторые примеры.

Пример 1

Предположим, перед нами стоит задача определить объем продажи товаров. Для этой цели у нас отведен диапазон B2:B10. Чтобы присвоить имя, необходимо использовать абсолютные ссылки.

18

В общем, наши действия следующие:

  1. Выделить необходимый диапазон.
  2. Перейти на вкладку «Формулы» и там найти команду «Присвоить имя».
  3. Далее появится диалоговое окно, в котором необходимо указать имя диапазона. В нашем случае это – «Продажи».
  4. Там еще находится поле «Область», которое дает возможность выбрать лист, на котором этот диапазон находится.
  5. Проверьте, чтобы был указан правильный диапазон. Формула должна быть следующей: =’1сезон’!$B$2:$B$10
  6. Кликните «ОК».

    19

Теперь можно вместо адреса диапазона вводить его имя. Так, с помощью формулы =СУММ(Продажи) можно вычислить сумму продаж для всех товаров.

20

Аналогично можно осуществить расчет среднего объема продаж с помощью формулы =СРЗНАЧ(Продажи).

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

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

Пример 2

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

На этом скриншоте диапазоны следующие.

B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10

Соответственно, нам нужно разместить формулы в ячейках B11, C11, D11 и E11.

21

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

Для этого необходимо открыть диспетчер имен, ввести имя в диалоговом окне. Механизм тот же самый. Перед тем, как нажимать «ОК», нужно убедиться, что в строку «Диапазон» введена формула =’4сезона’!B$2:B$10

В этом случае адресация смешанная. Как вы видите, перед названием колонки не указано знака доллара. Это позволяет суммировать значения, которые находятся в одинаковых строках, но разных столбцов. 

Далее методика действий та же самая. 

Теперь нам нужно ввести в ячейку B11 формулу =СУММ(Сезонные_Продажи). Далее, используя маркер автозаполнения, переносим ее в соседние ячейки, и получается такой результат.

22

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

23

Пример 3

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

=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

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

Более того, если начать вводить имя диапазона, Excel его автоматически предложит вместе с другими формулам.

24

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

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

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

25

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

  1. Открыть окно «Присвоить имя».
  2. Заполнить поля так, как указано на скриншоте.

    26

Важно обратить внимание, что вместо диапазона используется формула с функцией СМЕЩ совместно с функцией СЧЕТ.

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

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

Оцените качество статьи. Нам важно ваше мнение:

Типы аргументов и значений в Numbers на Mac

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

Любой тип значения

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

Массивы и функции, возвращающие массивы

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

Булево выражение и тип значения

Логическое (булево) выражение — это выражение, результатом которого является логическое значение ИСТИНА или ЛОЖЬ. Булево (логическое) значение — это либо непосредственно логическое значение ИСТИНА (1) или ЛОЖЬ (0), либо ссылка на ячейку, которая содержит или дает в результате вычисления логическое значение ИСТИНА или ЛОЖЬ. Обычно булево значение является результатом вычисления логического выражения, однако булево значение можно также задать как непосредственный аргумент функции или содержимое ячейки. Булево значение обычно используется для определения выражения, возвращаемого функцией ЕСЛИ.

Значение множества

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

Условное выражение

Условие — это выражение, которое может включать в себя операторы сравнения, константы, оператор объединения строк (амперсанд) и ссылки. Результатом сравнения условия с другим значением должно являться логическое значение ИСТИНА или ЛОЖЬ.

Константное выражение

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

СЦЕПИТЬ(«кот»;»ы»)

Значение даты/времени

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

Значение длительности

Значение длительности — это период времени или ссылка на ячейку, содержащую период времени. Значения длительности состоят из недель («н» или «нед.»), дней («д» или «дн.»), часов («ч» или «час.»), минут («м» или «мин.»), секунд («с» или «сек.») и миллисекунд («мс» или «миллисек.»)

Значение списка

Список — это последовательность других значений с разделением значений запятыми. Например:

ВЫБОР(3;»1-й»;»второй»;7;»последний»)

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

ОБЛАСТИ((B1:B5,C10:C12))

Значение аргумента перечислимого типа

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

Числовое значение

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

Значение диапазона

Значение диапазона — это ссылка на один диапазон ячеек (может быть и одной ячейкой). Значение диапазона имеет дополнительный атрибут, указываемый в описании аргумента. Это тип значений, которые должен содержать диапазон.

Значение ссылки

Значение ссылки — это ссылка на отдельную ячейку или диапазон ячеек.

  • Если диапазон содержит более одной ячейки, начальная и конечная ячейки разделяются двоеточием. Например:

    СЧЕТ(A3:D7).

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

    Таблица 2::B2.

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

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

    СУММ(Слайд 2::Таблица 1::C2:G2).

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

Строковое значение

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

Информатика. Тема 10. Основы работы с электронными таблицами (на примере табличного процессора MS Excel). Тест для самопроверки

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

2. Данные в электронной таблице могут быть …
текстом
числом
оператором
формулой

3. Фильтрацию в MS Excel можно проводить с помощью …
составного фильтра
автофильтра
простого фильтра
расширенного фильтра

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

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

6. Диаграмма изменится, если внести изменения в данные таблицы, на основе которых она создана
Да
Нет

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

8. Фильтрация данных в MS Excel – это процедура, предназначенная для …
отображения на экране записей таблицы, значения в которых соответствуют условиям, заданным пользователем
расположения данных исходной таблицы в наиболее удобном для пользователя виде
графического представления данных из исходной таблицы
изменение порядка записей

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

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

11. Результат вычислений в ячейке С1

20
15
10
5

12. Вид ссылки на ячейку A2 листа Январь рабочей книги Бюджет.xls
{Бюджет.xls}[Январь]A2
Бюджет.Январь.A2
[Бюджет.xls]Январь!A2
A2

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

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

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

16. В формуле содержится ссылка на ячейку A$1. Эта ссылка изменится при копировании формулы в нижележащие ячейки
Да
Нет

17. Основной элемент электронной таблицы:
поля
ячейки
данные
объекты

18. Результат вычислений в ячейке B1

5
3
1
0

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

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

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

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

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

24. Ячейка электронной таблицы определяется …
именами столбцов
областью пересечения строк и столбцов
номерами строк
именем, присваиваемым пользователем

25. В формуле содержится ссылка на ячейку $A1. Эта ссылка изменится при копировании формулы в нижележащие ячейки
Да
Нет

26. Электронная таблица – это …
устройство ввода графической информации в ПЭВМ
компьютерный эквивалент обычной таблицы, в ячейках которой записаны данные различных типов
устройство ввода числовой информации в ПЭВМ
программа, предназначенная для работы с текстом

27. Результат вычислений в ячейке B1

4
3
1
0

28. Для подведения итога по данным, расположенным в нескольких независимых таблицах можно использовать …
инструмент «Итоги» из меню «Данные»
инструмент «Сводная таблица» из меню «Данные»
«Надстройки» MS Excel
инструмент «Консолидация» из меню «Данные»

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

Диапазоны в Excel раньше назывался блоками. Диапазон – это выделенная прямоугольная область прилегающих ячеек. Данное определение понятия легче воспринять на практических примерах.

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

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

К диапазонам относятся:

  1. Несколько ячеек выделенных блоком (=B5:D8).
  2. Одна ячейка (=A2:A2).
  3. Целая строка (=18:18) или несколько строк (=18:22).
  4. Целый столбец (=F:F) или несколько столбцов (=F:K).
  5. Несколько несмежных диапазонов (=N5:P8;E18:h35;I5:L22).
  6. Целый лист (=1:1048576).

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



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

Выделение диапазонов – это одна из основных операций при работе с Excel. Диапазоны используют:

  • при заполнении данных;
  • при форматировании;
  • при очистке и удалении ячеек;
  • при создании графиков и диаграмм и т.п.

Способы выделения диапазонов:

  1. Чтобы выделить диапазон, например A1:B3, нужно навести курсор мышки на ячейку A1 и удерживая левую клавишу мышки провести курсор на ячейку B3. Казалось бы, нет ничего проще и этого достаточно для практических знаний. Но попробуйте таким способом выделить блок B3:D12345.
  2. Теперь щелкните по ячейке A1, после чего нажмите и удерживайте на клавиатуре SHIFT, а потом щелкните по ячейке B3. Таким образом, выделился блок A1:B3. Данную операцию выделения условно можно записать: A1 потом SHIFT+B3.
  3. Диапазоны можно выделять и стрелками клавиатуры. Щелкните по ячейке D3, а теперь удерживая SHIFT, нажмите клавишу «стрелка вправо» три раза пока курсор не переместится на ячейку G3. У нас выделилась небольшая строка. Теперь все еще не отпуская SHIFT, нажмите клавишу «стрелка вниз» четыре раза, пока курсор не перейдет на G7. Таким образом, мы выделили блок диапазона D3:G7.
  4. Как выделить несмежный диапазон ячеек в Excel? Выделите мышкой блок B3:D8. Нажмите клавишу F8 чтобы включить специальный режим. В строке состояния появится сообщение: «Расширить выделенный фрагмент». И теперь выделите мышкой блок F2:K5. Как видите, в данном режиме мы имеем возможность выделять стразу несколько диапазонов. Чтобы вернутся в обычный режим работы, повторно нажмите F8.
  5. Как выделить большой диапазон ячеек в Excel? Клавиша F5 или CTRL+G. В появившемся окне, в поле «Ссылка» введите адрес: B3:D12345 (или b3:d12345) и нажмите ОК. Таким образом, вы без труда захватили огромный диапазон, всего за пару кликов.
  6. В поле «Имя» (которое расположено слева от строки формул) задайте диапазон ячеек: B3:D12345 (или b3:d12345) и нажмите «Enter».

Способ 5 и 6 – это самое быстрое решение для выделения больших диапазонов. Небольшие диапазоны в пределах одного экрана лучше выделять мышкой.

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

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

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

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

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

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

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

Чтобы их выделять просто удерживайте нажатие клавиши CTRL, а дальше как при обычном выделении. Также в данной ситуации особенно полезным будет режим после нажатия клавиши F8: «Расширить выделенный фрагмент».

Выбор ячеек. Работа с диапазоном ячеек

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

Пример.

  1. Адрес диапазона, образованного в виде части строки 1, будет А1:Н1.

  2. Адрес диапазона, образованный в виде столбца B, будет В1:В10.

  3. Адрес диапазона, образованный в виде прямоугольника, будет D4:F5.

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

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

Когда вы записываете формулу, в качестве исходных данных вы указываете адреса ячеек, откуда берутся данные. Допустим, мы вычисляем перемещение предмета. Для этого нам нужно взять данные из ячейки скорости А2 и умножить на данные ячейки времени В2. При этом формула примет вид А2*В2. Физический смысл формулы в таком виде не понятен, поэтому при наборе часто возможны ошибки.

Кроме принятых по умолчанию, в Excel возможно использовать уникальные имена ячеек и диапазонов. Допустим, в нашем примере, ячейку А2 мы назовем Скорость, а ячейку В2 – Время. Тогда формула примет вид – Скорость*Время. Ошибок при вводе такой формулы будет гораздо меньше. Поэтому рекомендуется по возможности ячейкам присваивать имена, отражающие физический смысл хранящихся в них данных.

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

Ячейки и диапазоны Excel — Microsoft Office

Ячейки и диапазоны

Расположение ячейки на листе определяется буквой столбца и номером строки (например, «A1»). Его часто называют адресом ячейки .
Все ячейки на листе разделены серыми линиями, известными как линии сетки, а группа смежных ячеек известна как Диапазон ячеек .
Существует три типа операторов ссылки на ячейки, которые можно использовать для идентификации ячеек и диапазонов.Двоеточие, запятая и пробел.
Двоеточие (:) — используется для определения диапазона ячеек (например, «A1: D4»). Диапазон ячеек описывается с помощью ссылок на верхнюю левую ячейку и нижнюю правую ячейку, разделенных двоеточием.
Запятая (,) — используется для определения несмежных ячеек (например, «A1», «B2»). Запятая также известна как оператор объединения.
Пробел () — используется для объединения пересечения двух блоков ячеек (например, «A1: D4 B2: C3»).При использовании этого оператора два блока ячеек должны перекрываться. В противном случае появится сообщение об ошибке. Пространство также известно как оператор пересечения.
Несколько диапазонов ячеек обычно называются несмежными или несмежными диапазонами. Это диапазоны ячеек, которые не находятся в красивом прямоугольном блоке.


Ввод данных

В ячейки можно вводить данные трех различных типов. Иногда это число увеличивается до четырех, поскольку некоторые люди выделяют дату и время как отдельный тип.
Числовой — Это в основном числа или значения. Сюда входят даты и время, которые рассматриваются как серийные номера. Это позволяет манипулировать датами и временем в формулах, например добавлять и вычитать даты.
Текст — это в основном слова или любые текстовые данные. Их также называют ярлыками или строками. Текстовые записи считаются метками, потому что вы не можете выполнять с ними какие-либо вычисления.
Формулы — Все они начинаются со знака равенства (=) и позволяют выполнять математические операции.
Если вы выберете ячейку и начнете вводить текст, вы перезапишете содержимое этой ячейки.
Вы можете вводить (и редактировать) содержимое ячеек либо с помощью строки формул, либо напрямую с помощью ячейки.
Если у вас нет возможности редактировать содержимое ячеек напрямую, проверьте (Инструменты> Параметры) (вкладка «Редактировать», «Редактировать прямо в ячейке»).


Выбор данных

Прежде чем вы сможете ввести данные в ячейку, вы должны сначала выбрать ячейку.
На листе может быть одновременно выбрано несколько ячеек, но одновременно может быть только одна Активная ячейка .
Активная ячейка всегда окружена черной рамкой.
Адрес активной ячейки всегда отображается в поле «Имя» слева от строки формул.
Когда выбран диапазон ячеек, только верхняя левая ячейка считается активной ячейкой.
Если выбран диапазон ячеек, то активная ячейка обозначается как белая.
Чтобы отменить выбор ячейки или диапазона ячеек, щелкните в любом месте за пределами диапазона или нажмите любую из клавиш со стрелками.
Существует большое количество сочетаний клавиш, которые могут помочь вам выбрать данные, и, вероятно, наиболее полезной является (Ctrl + Shift + 8) , которая выбирает текущую область.


Перемещение данных

Всякий раз, когда вы копируете ячейки, Excel сохраняет данные во временной памяти, называемой буфером обмена.
Данные останутся там до тех пор, пока не будут заменены другими данными. Он будет заменен только последующей командой «Вырезать» или «Копировать».
Сочетания клавиш (Ctrl + C) , (Ctrl + X) и (Ctrl + V) соответствуют копированию, вырезанию и вставке данных соответственно и совместимы с другими приложениями.
Вы также можете просто нажать Enter, чтобы вставить текущую запись из буфера обмена.


Сортировка данных

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


Удаление данных

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


Отображение данных

Иногда, если число начинается с отрицательного знака, они отображаются как тире, только если ширина столбца недостаточна для всего числа.Просто нажмите F2 на ячейку или преобразуйте ее в формулу (префикс со знаком равенства).
Если вы использовали имена диапазонов строк и столбцов, чтобы обеспечить быстрый способ передачи параметров в функции рабочего листа, тогда «ЧИСЛО!» отображается, хотя получено правильное значение ??


Скрытие данных от печати

Требуется распечатать некоторые ячейки (а не всю строку или столбец, вы можете щелкнуть правой кнопкой мыши и скрыть их) в области печати? Один из приемов — установить белый цвет шрифта в этих ячейках.
Выберите ячейки (Формат> Ячейки). В диалоговом окне «Формат ячеек» щелкните вкладку «Шрифт».
Щелкните стрелку справа от поля со списком цветов и выберите белый цвет из списка.
Щелкните «ОК», чтобы закрыть диалоговое окно и применить новый цвет шрифта.
Распечатайте лист без отображения нежелательные клетки. После завершения печати снова выберите ячейки и установите черный цвет (или автоматически).


Связывание ячейки

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


Добавление ссылок

Вы можете скопировать диапазон ячеек и вставить изображение на рабочий лист. Затем изображение автоматически обновляется с любыми изменениями значений или форматирования. Удерживая нажатой клавишу Shift, выберите (Правка> Вставить ссылку на изображение).


Важно

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


© 2021 Better Solutions Limited. Все права защищены. © 2021 Better Solutions Limited TopNext

Excel 2013: Основные сведения о ячейках

/ ru / excel2013 / save-and-sharing-workbooks / content /

Введение

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


Необязательно: загрузите нашу рабочую тетрадь.

Что такое ячейки

Каждый рабочий лист состоит из тысяч прямоугольников, которые называются ячейками . Ячейка — это пересечение строки и столбца .Столбцы обозначаются буквами (A, B, C) , а строки обозначаются цифрами (1, 2, 3) .

Ячейка

Каждая ячейка имеет собственное имя — или адрес ячейки — в зависимости от столбца и строки. В этом примере выбранная ячейка пересекает столбец C и строку 5 , поэтому адрес ячейки — C5 . Адрес ячейки также появится в поле Имя . Обратите внимание, что заголовки столбца ячейки и заголовки строки выделяются цветом , когда ячейка выбрана.

Ячейка C5

Вы также можете выбрать несколько ячеек одновременно. Группа ячеек известна как диапазон ячеек . Вместо адреса одной ячейки вы будете ссылаться на диапазон ячеек, используя адреса ячеек первых и последних ячеек в диапазоне ячеек, разделенных двоеточием . Например, диапазон ячеек, включающий ячейки A1, A2, A3, A4 и A5, будет записан как A1: A5 .

На изображениях ниже выбраны два разных диапазона ячеек:

  • Диапазон ячеек A1: A8 Диапазон ячеек A1: A8
  • Диапазон ячеек A1: B8 Диапазон ячеек A1: B8

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

Для выбора ячейки:

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

  1. Щелкните ячейку , чтобы выделить ее.
  2. Вокруг выбранной ячейки появится граница , а заголовок столбца и заголовок строки будут выделены. Ячейка останется выбранной , пока вы не щелкнете другую ячейку на листе.Выбор одной ячейки

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

Чтобы выбрать диапазон ячеек:

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

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

Содержимое ячейки

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

  • Текст
    Ячейки могут содержать текст , например буквы, числа и даты.Текст ячейки
  • Атрибуты форматирования
    Ячейки могут содержать атрибутов форматирования , которые изменяют способ отображения букв, чисел и дат . Например, проценты могут отображаться как 0,15 или 15%. Вы даже можете изменить цвет фона ячейки . Форматирование ячейки
  • Формулы и функции
    Ячейки могут содержать формул и функций , которые вычисляют значения ячеек. В нашем примере СУММ (B2: B8) складывает значение каждой ячейки в диапазоне ячеек B2: B8 и отображает итог в ячейке B9.Формулы ячеек
Для вставки содержимого:
  1. Щелкните ячейку , чтобы выделить ее. Выбор ячейки A1
  2. Введите содержимое в выбранную ячейку, затем нажмите Введите на клавиатуре. Содержимое появится в ячейке и формуле bar . Вы также можете вводить и редактировать содержимое ячейки в строке формул. Вставка содержимого ячейки
Чтобы удалить содержимое ячейки:
  1. Выберите ячейку с содержимым, которое вы хотите удалить.Выбор ячейки
  2. Нажмите клавишу Delete или Backspace на клавиатуре. Содержимое ячейки будет удалено. Удаление содержимого ячейки

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

Для удаления ячеек:

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

  1. Выберите ячейки , которые вы хотите удалить. Выбор ячейки для удаления
  2. Выберите команду Удалить на вкладке Home на ленте . Нажав на команду «Удалить»
  3. Ячейки ниже будут shift up . Ячейки сдвинуты для замены удаленной ячейки
Чтобы скопировать и вставить содержимое ячейки:

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

  1. Выберите ячейки , которые вы хотите скопировать . Выбор ячейки для копирования
  2. Щелкните команду Копировать на вкладке Home или нажмите Ctrl + C на клавиатуре. Копировать команду
  3. Выберите ячейки , в которые вы хотите вставить содержимое . Скопированные ячейки теперь будут иметь пунктирную рамку вокруг них. Вставка ячеек
  4. Щелкните команду Вставить на вкладке Home или нажмите Ctrl + V на клавиатуре.При нажатии на команду «Вставить»
  5. В выбранные ячейки будет вставлено содержимое . Вставленное содержимое ячейки
Чтобы вырезать и вставить содержимое ячейки:

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

  1. Выберите ячеек , которые вы хотите вырезать . Выбор диапазона ячеек для вырезания
  2. Щелкните команду Cut на вкладке Home или нажмите Ctrl + X на клавиатуре.Щелчок по команде «Вырезать»
  3. Выберите ячейки, в которые вы хотите вставить содержимое. Вырезанные ячейки теперь будут иметь пунктирную рамку вокруг них. Вставка ячеек
  4. Щелкните команду Вставить на вкладке Home или нажмите Ctrl + V на клавиатуре. вырезанное содержимое будет удалено из исходных ячеек и вставлено в выбранные ячейки. Вырезанные и вставленные ячейки
Для доступа к дополнительным параметрам вставки:

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

  • Чтобы получить доступ к дополнительным параметрам вставки, щелкните стрелку раскрывающегося списка в команде Вставить .
Дополнительные параметры вставки

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

Щелкните правой кнопкой мыши, чтобы получить доступ к параметрам форматирования
Для перетаскивания ячеек:

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

  1. Выберите ячеек , которые вы хотите переместить .
  2. Наведите указатель мыши на границу выбранной ячейки (ячеек), пока курсор не изменится с белого креста на черный крест с четырьмя стрелками . Наведение на границу ячейки
  3. Щелкните, удерживайте и перетащите ячейки в нужное место .Перетаскивание выбранных ячеек
  4. Отпустите кнопку мыши, и ячейки будут сброшены на в выбранном месте. Отброшенные ячейки
Чтобы использовать маркер заполнения:

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

  1. Выберите ячеек , содержащих контент, который вы хотите использовать. Маркер заливки появится в виде небольшого квадрата в правом нижнем углу выбранных ячеек. Расположение маркера заливки
  2. Щелкните, удерживайте и перетащите маркер заливки до тех пор, пока все ячейки, которые вы хотите заполнить выбранных . Перетаскивание маркера заполнения
  3. Отпустите кнопку мыши, чтобы заполнить выбранных ячеек. Заполненные ячейки
Чтобы продолжить серию с помощью маркера заполнения:

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

Использование маркера заполнения для расширения серии

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

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

Для использования Flash Fill:

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

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

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

Нажатие кнопки Flash Fill

Найти и заменить

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

Для поиска содержания:

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

  1. На вкладке Home щелкните команду Find and Select , затем выберите Find… из раскрывающегося меню. Щелчок по команде «Найти»
  2. Появится диалоговое окно «Найти и заменить ». Введите контент , который хотите найти. В нашем примере мы введем имя сотрудника.
  3. Нажмите Найти далее . Если содержимое найдено, ячейка, содержащая это содержимое, будет выбрана . Щелчок «Найти далее»
  4. Щелкните «Найти далее» , чтобы найти другие экземпляры, или «Найти все», , чтобы просмотреть все экземпляры поискового запроса.Нажатие «Найти все»
  5. Когда вы закончите, нажмите Закрыть , чтобы выйти из диалогового окна «Найти и заменить». Закрытие диалогового окна «Найти и заменить»

Вы также можете получить доступ к команде «Найти», нажав Ctrl + F на клавиатуре .

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

Нажав Параметры
Для замены содержимого ячейки:

Иногда вы можете обнаружить, что неоднократно допускали ошибку в своей книге (например, неправильно написали чье-то имя) или что вам нужно заменить одно слово или фразу на другое.Вы можете использовать функцию Excel «Найти и заменить », чтобы быстро вносить изменения. В нашем примере мы воспользуемся функцией «Найти и заменить», чтобы исправить список адресов электронной почты.

  1. На вкладке Home щелкните команду Find and Select , затем выберите Replace … из раскрывающегося меню. При нажатии команды Replace
  2. появится диалоговое окно Find and Replace . Введите текст, который хотите найти, в поле Find what: .
  3. Введите текст, которым хотите заменить его, в поле Заменить на :, затем щелкните Найти далее .Нажатие «Найти далее»
  4. Если содержимое найдено, ячейка, содержащая это содержимое, будет выбрана .
  5. Просмотрите текст, чтобы убедиться, что вы хотите его заменить.
  6. Если вы хотите заменить его, выберите один из вариантов заменить :
    • Заменить заменит отдельные экземпляры.
    • Заменить все заменит все вхождения текста во всей книге. В нашем примере мы выберем эту опцию, чтобы сэкономить время.
    Замена выделенного текста
  7. Появится диалоговое окно, подтверждающее количество сделанных замен. Нажмите OK , чтобы продолжить. Нажмите OK
  8. . Выбранное содержимое ячейки будет заменено . Замененное содержимое
  9. Когда вы закончите, нажмите Закройте , чтобы выйти из диалогового окна «Найти и заменить». box

Вызов!

  1. Откройте существующую книгу Excel 2013. Если хотите, можете воспользоваться нашим учебным пособием.
  2. Выберите ячейку D3. Обратите внимание, как адрес ячейки отображается в поле Имя , а его содержимое отображается как в ячейке, так и в строке формулы .
  3. Выберите ячейку и попробуйте вставить текст и числа .
  4. Удалите ячейку и обратите внимание, как ячейки ниже сдвигаются на вверх, чтобы заполнить свое место.
  5. Вырежьте ячеек и вставьте их в другое место.Если вы используете этот пример, вырежьте ячейки D4: D6 и вставьте их в E4: E6 .
  6. Попробуйте перетащить и , перетащив некоторых ячеек в другие части рабочего листа.
  7. Используйте маркер заполнения для заполнения данными соседних ячеек как по вертикали, так и по горизонтали. Если вы используете этот пример, используйте дескриптор заполнения, чтобы продолжить серию дат в строке 3.
  8. Используйте функцию Найти для поиска содержимого в книге.Если вы используете этот пример, введите имя Lewis в поле Find what: .

/ ru / excel2013 / изменение-столбцов-строк-и-ячеек / содержимого /

Имена диапазонов в Excel: что нужно знать


Область применения

Обратите внимание на второй раздел ( Scope ). Все имена имеют область действия либо для определенного рабочего листа (также называемого уровнем локального рабочего листа), либо для всей книги (также называемого глобальным уровнем книги).Объем имени — это место, в котором имя распознается без уточнения.

Например, если вы определили имя диапазона как «Прибыль» с его областью действия как Лист1, а не Рабочая книга, то оно будет распознано как «Прибыль» только на Листе 1 (т. Е. Без уточнения).

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

= Лист1! Прибыль

Если вы определили имя, такое как «Денежный поток», и его область действия — рабочая книга, это имя распознается для всех рабочих листов в этой книге (но не для любой другой книги).Если бы областью был рабочий лист (скажем, Sheet1), тогда имя диапазона было бы «Sheet1! Cashflow». Таким образом, объем книги позволяет более четко определить имена диапазонов и избежать путаницы.

Имя всегда должно быть уникальным в пределах своей области действия. Excel не позволяет вам определять имя, которое не является уникальным в пределах его области действия. Однако вы можете использовать одно и то же имя с разными областями действия. Например, вы можете определить имя, такое как «Прибыль», с областью действия Sheet1, Sheet2 и Sheet3 в той же книге. Хотя каждое имя одинаково, каждое имя уникально в пределах своей области действия.Вы можете сделать это, чтобы гарантировать, что формула, которая использует, например, имя «GrossProfit», всегда ссылается на одни и те же ячейки на уровне локального рабочего листа.

Вы даже можете определить то же имя, например «Прибыль», для глобального уровня книги, но опять же, эта область уникальна. В этом случае может возникнуть конфликт имен. Чтобы разрешить этот конфликт, Excel использует имя, заданное для рабочего листа по умолчанию. Уровень локального листа имеет приоритет над уровнем глобальной книги. Это можно обойти, добавив к имени префикс, например, вместо этого переименуйте его в «WorkbookFile_Profit».

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

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

Уход с именами

Строка имени должна начинаться с буквы или символа подчеркивания.Остальные символы в имени могут быть буквами, цифрами, точками и символами подчеркивания. Пробелы не допускаются, но два слова можно соединить знаком подчеркивания (_) или точкой (.). Например, чтобы ввести имя «Денежный поток», вы должны ввести «Cash_Flow» или «Cash.Flow».

Вы не можете использовать имя, которое иначе можно было бы спутать с ссылкой на ячейку; например, «День 1», поскольку это уже ссылка на ячейку (многие пытались!).

Кажется, что нет ограничений на количество имен, которые вы можете определить, но имя может содержать не более 255 символов.Имена могут содержать прописные и строчные буквы, но Excel не различает прописные и строчные буквы в именах. Например, если вы создали глобальное имя «Прибыль», а затем создали другое глобальное имя «ПРОФИТ» в той же книге, второе имя будет отклонено, поскольку имена должны быть уникальными, независимо от использования заглавных букв.

Это не проблема синтаксиса, но я настоятельно рекомендую подумать о добавлении префиксов к именам диапазонов. Постоянные читатели заметят, что имена диапазонов моих списков всегда начинаются с «LU_», где «LU» означает «Искать вверх».Точно так же я использую «BC_» для «Базовая ячейка» при работе с функцией OFFSET .

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

Быстрое создание имен диапазонов

Существует изящный ярлык для создания имен диапазонов с использованием существующих имен.Рассмотрим следующий список:

Excel Диапазоны и ячейки VBA

Диапазоны и ячейки в VBA

Таблицы

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

Диапазон Excel относится к одной или нескольким ячейкам (например, A3: B4)

Адрес ячейки

A1 Обозначение

В нотации A1 ячейка обозначается буквой столбца (от A до XFD), за которой следует номер строки (от 1 до 1 048 576).

В VBA вы можете ссылаться на любую ячейку, используя Range Object .

‘См. Ячейку B4 на текущем активном листе

MsgBox Range («B4»)

‘ См. Ячейку B4 на листе с именем ‘Data’

MsgBox Worksheets («Data»). Диапазон (» B4 «)

‘См. Ячейку B4 на листе с именем« Данные »в другой ОТКРЫТОЙ книге

» с именем «Мои данные»

Книги MsgBox («Мои данные»).Рабочие листы («Данные»). Диапазон («В4»)

R1C1 Обозначение

В нотации R1C1 ячейка обозначается буквой R, за которой следует номер строки, затем буква «C», за которой следует номер столбца. например, B4 в нотации R1C1 будет обозначаться как R4C2. В VBA вы используете объект Cells Object для использования нотации R1C1:

‘См. Ячейку R [6] C [4], т.е. D6

Ячейки (6, 4) = «D6»

Диапазон ячеек

A1 Обозначение

Для ссылки на более чем одну ячейку используйте «:» между адресом начальной ячейки и адресом последней ячейки.Следующее будет относиться ко всем ячейкам от A1 до D10:

R1C1 Обозначение

Для ссылки на более чем одну ячейку используйте «,» между адресом начальной ячейки и адресом последней ячейки. Следующее будет относиться ко всем ячейкам от A1 до D10:

Диапазон (Ячейки (1, 1), Ячейки (10, 4))

Запись в ячейки

Чтобы записать значения в ячейку или непрерывную группу ячеек, просто обратитесь к диапазону, поставьте знак = и затем запишите значение, которое будет сохранено:

‘Сохранить F5 в ячейке с адресом F6

Диапазон («F6») = «F6»

‘ Сохранить E6 в ячейке с адресом R [6] C [5] i.e E6

Ячейки (6, 5) = «E6»

‘Сохранить A1: D10 в диапазоне A1: D10

Диапазон («A1: D10») = «A1: D10»

‘ или

Диапазон (Ячейки (1, 1), Ячейки (10, 4)) = «A1: D10»

Чтение из ячеек

Чтобы прочитать значения из ячеек, просто обратитесь к переменной для хранения значений, поставьте знак = и затем обратитесь к диапазону для чтения:

Dim val1

Dim val2

‘Считать из ячейки F6

val1 = Range («F6»)

‘ Прочитать из ячейки E6

val2 = Cells (6, 5)

MsgBox val1

Msgbox val2

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

Несмежные ячейки

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

‘Сохранить 10 в ячейках A1, A3 и A5

Диапазон («A1, A3, A5») = 10

‘ Сохранить 10 в ячейках A1: A3 и D1: D3)

Диапазон ( «A1: A3, D1: D3») = 10

Пересечение ячеек

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

‘Сохранить’ Col D ‘в D1: D10

‘, который является общим для A1: D10 и D1: F10

Диапазон («A1: D10 D1: G10») = «Col D»

Смещение от ячейки или диапазона

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

Синтаксис смещения

Диапазон.Смещение (число_строчек, число_столбцов)

Смещение от ячейки

‘СМЕЩЕНИЕ из ячейки A1

‘ Обратиться к самой ячейке

‘Переместить 0 строк и 0 столбцов

Диапазон («A1»). Смещение (0, 0) = «A1»

‘ Переместить 1 строк и 0 столбцов

Диапазон («A1»). Смещение (1, 0) = «A2»

‘Перемещение 0 строк и 1 столбца

Диапазон («A1»).Смещение (0, 1) = «B1»

‘Переместить 1 строку и 1 столбец

Диапазон («A1»). Смещение (1, 1) = «B2»

‘ Переместить 10 строк и 5 столбцы

Диапазон («A1»). Смещение (10, 5) = «F11»

Смещение из диапазона

‘Переместить ссылку в диапазон A1: D4 на 4 строки и 4 столбца

‘ Новая ссылка — E5: H8

Диапазон («A1: D4»). Смещение (4,4) = «E5: H8»

Установка ссылки на диапазон

Чтобы присвоить диапазон переменной диапазона: объявите переменную типа Range, затем используйте команду Set, чтобы установить ее в диапазон.Обратите внимание, что вы должны использовать команду SET, поскольку RANGE — это объект:

‘Объявить переменную Range

Dim myRange as Range

‘ Установить переменную в диапазон A1: D4

Установить myRange = Range («A1: D4»)

‘Распечатать $ A $ 1: $ D $ 4

MsgBox myRange.Address

Изменить размер диапазона

Метод изменения размера объекта Range изменяет размер эталонного диапазона:

Dim myRange As Range

‘Диапазон изменения размера

Set myRange = Range («A1: F4»)

‘ Печать $ 1: $ E $ 10

Отладка.Вывести myRange.Resize (10, 5) .Address

Левая верхняя ячейка диапазона измененного размера такая же, как левая верхняя ячейка исходного диапазона

Изменить размер синтаксиса

Range.Resize (number_of_rows, number_of_columns)

OFFSET против изменения размера

Offset не изменяет размеры диапазона, а перемещает его на указанное количество строк и столбцов. Изменение размера не изменяет положение исходного диапазона, но изменяет размеры на указанное количество строк и столбцов.

Все ячейки на листе

Объект Cells относится ко всем ячейкам на листе (1048576 строк и 16384 столбца).

‘Очистить все ячейки на листах

Ячейки Очистить

Б / У Свойство

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

Dim ws As Worksheet

Set ws = ActiveSheet

‘$ B $ 2: $ L $ 14, если L2 — первая ячейка с любым значением

‘, а L14 — последняя ячейка с любым значением на

‘ активный лист

Отладка.Распечатать ws.UsedRange.Address

Текущий регион

Свойство

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

Dim myRange As Range

Set myRange = Range («D4: F6»)

‘Печатает $ B $ 2: $ L $ 14

‘ Если есть заполненный путь от D4: F16 к B2 И L14

Отладка.Печать myRange.CurrentRegion.Address

‘Вы также можете ссылаться на одну начальную ячейку

Set myRange = Range («D4»)’ Печатает $ B $ 2: $ L $ 14

Диапазон Свойства

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

17

18

19

20

21

Dim myRange As Range

Set myRange = Range («A1: F10»)

‘Выводит $ A $ 1: $ F $ 10

Отладка.Распечатать myRange.Address

Set myRange = Range («F10»)

‘Отпечатает 10 для строки 10

Debug.Print myRange.Row

‘ Отпечатает 6 для столбца F

Debug.Print myRange.Column

Set myRange = Range («E1: F5»)

‘Печатает 5 для количества строк в диапазоне

Debug.Print myRange.Rows.Count

‘ Печатает 2 для количества столбцов в диапазоне

Debug.Print myRange.Столбцы.Счет

Последняя ячейка на листе

Вы можете использовать свойства Rows.Count и Columns.Count с объектом Cells , чтобы получить последнюю ячейку на листе:

‘Напечатать номер последней строки

‘ Печать 1048576

Debug.Print «Строки на листе:» & Rows.Count

‘Напечатать номер последнего столбца

‘ Печать 16384

Отладка.Печать «Столбцы на листе:» & Columns.Count

‘Распечатать адрес последней ячейки

‘ Печать $ XFD $ 1048576

Debug.Print «Адрес последней ячейки на листе:» & Ячейки (Строки .Count, Columns.Count)

Последний использованный номер строки в столбце

END переместит вас к последней ячейке в диапазоне, а End (xlUp) перенесет вас к первой использованной ячейке из этой ячейки.

Dim lastRow As Long

lastRow = Cells (Rows.Счетчик «A»). Конец (xlUp) .Row

Последний использованный номер столбца в строке

Dim lastCol As Long

lastCol = Cells (1, Columns.Count) .End (xlToLeft) .Column

END переместит вас к последней ячейке в диапазоне, а End (xlToLeft) переместит вас влево к первой использованной ячейке из этой ячейки.

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

Свойства ячейки

Общие свойства

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

17

18

19

20

21

22

Размер ячейки как диапазон

Установить ячейку = диапазон («A1»)

ячейка.Активировать

Debug.Print cell.Address

‘Print $ A $ 1

Debug.Print cell. Value

‘ Prints 456

‘Address

Debug.Print cell.Formula

SUM (C2: C3)

‘Комментарий

Debug.Print cell.Comment.Text

‘ Style

Debug.Print cell.Style

‘Cell Format

Debug.Print cell. DisplayFormat.NumberFormat

Шрифт ячейки

Ячейка.Объект Font содержит свойства шрифта ячейки:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

17

18

19

20

Тусклая ячейка как диапазон

Установить ячейку = диапазон («A1»)

‘Обычный, курсив, полужирный и полужирный курсив

ячейка.Font.FontStyle = «Bold Italic»

‘То же, что и ячейка

.Font.Bold = True

cell.Font.Italic = True

‘ Установить шрифт Courier

cell.Font.FontStyle = «Courier «

‘Установить цвет шрифта

ячейку.Font.Color = vbBlue

‘ или

ячейку.Font.Color = RGB (255, 0, 0)

‘Установить размер шрифта

ячейки. Размер шрифта = 20

Копирование и вставка

Вставить все

диапазонов / ячеек можно копировать и вставлять из одного места в другое.Следующий код копирует все свойства исходного диапазона в целевой диапазон (эквивалентный CTRL-C и CTRL-V)

‘Простое копирование

Диапазон («A1: D20»). Копирование

рабочих листов («Лист2»). Диапазон («B10»). Вставить

‘ или

‘Копировать с текущего листа на лист с именем «Sheet2»

Диапазон («A1: D20»). Место назначения для копирования: = Рабочие листы («Sheet2»). Диапазон («B10»)

Специальная паста

Выбранные свойства исходного диапазона могут быть скопированы в место назначения с помощью параметра PASTESPECIAL:

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

Диапазон («A1: D20»).Скопируйте рабочие листы

(«Sheet2»). Диапазон («B10»). PasteSpecial Paste: = xlPasteValues ​​

Вот возможные варианты для опции Вставить:

‘Paste Специальные типы

xlPasteAll xlPasteAllExceptBorders

xlPasteAllMergingConditionalFormats xlPasteAllUsingSourceTheme

xlPasteColumnWidths xlPasteComments

xlPasteFormats xlPasteFormulas

xlPasteFormulasAndNumberFormats xlPasteValidation

xlPasteValues ​​xlPasteValuesAndNumberFormats

AutoFit Содержимое

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

‘Измените размер строк с 1 по 5, чтобы они соответствовали содержимому.

Строки («1: 5»).AutoFit

‘Измените размер столбцов A на B, чтобы они соответствовали содержимому

столбцов («A: B»). AutoFit

Больше примеров диапазонов

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

За каждые

Проще перебрать диапазон, используя конструкцию For Each , как показано ниже:

Для каждой ячейки в диапазоне («A1: B100»)

‘Сделайте что-нибудь с ячейкой

Следующая ячейка

На каждой итерации цикла одна ячейка в диапазоне присваивается переменной c, и операторы цикла For выполняются для этой ячейки.Цикл завершается, когда все ячейки обработаны.

Сортировка

Sort — это метод объекта Range. Вы можете отсортировать диапазон, указав параметры сортировки в Range.Sort. Приведенный ниже код отсортирует столбцы A: C на основе ключа в ячейке C2. Порядок сортировки может быть xlAscending или xlDescending. Заголовок: = xlYes следует использовать, если первая строка является строкой заголовка.

Столбцы («A: C»). Ключ сортировки1: = Диапазон («C2»), _

order1: = xlAscending, Header: = xlYes

Найдите

Find также является методом Range Object.Он находит первую ячейку, содержимое которой соответствует критериям поиска, и возвращает ячейку как объект Range. Он возвращает Nothing , если совпадений нет.

Используйте метод FindNext (или FindPrevious), чтобы найти следующее (предыдущее) вхождение.

Следующий код изменит шрифт на «Arial Black» для всех ячеек в диапазоне, который начинается с «John»:

Для каждого c в диапазоне («A1: A100»)

Если c Как «John *», то

c.Font.Name = «Arial Black»

Конец Если

Далее c

Следующий код заменит все вхождения «To Test» на «Passed» в указанном диапазоне:

с диапазоном («a1: a500»)

Set c = .Find («To Test», LookIn: = xlValues)

If Not c Is Nothing Then

firstaddress = c.Address

Do

c .Value = «Passed»

Установить c =.FindNext (c)

Цикл, пока не c — ничто и c.Address <> firstaddress

End If

End With

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

Диапазон адресов

Используйте Range.Address, чтобы получить адрес в стиле A1

Диапазон MsgBox («A1: D10»). Адрес

‘или

Диапазон отладки.Печать («A1: D10»). Адрес

Используйте xlReferenceStyle (по умолчанию xlA1), чтобы получить адреса в стиле R1C1.

Диапазон MsgBox («A1: D10»). Адрес (ReferenceStyle: = xlR1C1)

‘или

Диапазон Debug.Print («A1: D10»).Адрес (ReferenceStyle: = xlR1C1)

Это полезно, когда вы имеете дело с диапазонами, хранящимися в переменных, и хотите обрабатывать только определенные адреса.

Диапазон до массива

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

Dim DirArray As Variant

‘Сохранение значений в диапазоне в массиве

DirArray = Range («a1: a5»).Value

‘Цикл для обработки значений

For Each c In DirArray

Debug.Print c

Next

от массива до диапазона

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

Используйте приведенный ниже код, чтобы записать массив в диапазон D1: D5:

Диапазон («D1: D5»).Значение = DirArray

Диапазон («D1: h2»). Значение = Application.Transpose (DirArray)

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

Диапазон сумм

SumOfRange = Application.WorksheetFunction.Sum (Range («A1: A10»))

Debug.Print SumOfRange

Вы можете использовать многие функции, доступные в Excel, в коде VBA, указав Application.WorkSheetFunction. перед именем функции, как в примере выше.

Диапазон счета

‘Count Число ячеек с числами в диапазоне

CountOfCells = Application.WorksheetFunction.Count (Range («A1: A10»))

Debug.Print CountOfCells

‘ Подсчет количества непустых ячеек в Диапазон

CountOfNonBlankCells = Application.WorksheetFunction.CountA (Range («A1: A10»))

Отладка.Печать CountOfNonBlankCells

Автор: Винамра Чандра

Как создать именованные диапазоны в Excel (пошаговое руководство)

Что в названии?

Если вы работаете с электронными таблицами Excel, это может означать значительную экономию времени и эффективность.

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

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


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

Верно?

Точно так же в Excel вы можете дать имя ячейке или диапазону ячеек.

Теперь вместо ссылки на ячейку (например, A1 или A1: A10) вы можете просто использовать присвоенное ей имя.

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

В этом наборе данных, если вам нужно обратиться к диапазону с датой, вам придется использовать в формулах A2: A11. Точно так же для торгового представителя и отдела продаж вам придется использовать B2: B11 и C2: C11.

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

Именованные диапазоны Excel упрощают обращение к наборам данных в Excel.

Вы можете создать именованный диапазон в Excel для каждой категории данных, а затем использовать это имя вместо ссылок на ячейки. Например, даты могут называться «Дата», данные торгового представителя — «SalesRep», а данные о продажах — «Продажи».

Вы также можете создать имя для отдельной ячейки. Например, если в ячейке указан процент комиссии с продаж, вы можете назвать эту ячейку «Комиссия».

Преимущества создания именованных диапазонов в Excel

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

Использовать имена вместо ссылок на ячейки

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

Например, вы можете использовать = СУММ (ПРОДАЖИ) вместо = СУММ (C2: C11) для указанного выше набора данных.

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

  • Количество продаж со значением более 500: = COUNTIF (Продажи, «> 500 ″)
  • Сумма всех продаж, выполненных Томом: = СУММЕСЛИ (SalesRep,» Том «, Продажи)
  • Комиссия, заработанная Джо (объем продаж Джо, умноженный на процент комиссионных):
    = СУММЕСЛИ (SalesRep, «Джо», продажи) * Комиссия

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

Нет необходимости возвращаться к набору данных для выбора ячеек

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

Вы можете просто ввести пару алфавитов этого именованного диапазона, и Excel покажет соответствующие именованные диапазоны (как показано ниже):

Именованные диапазоны Сделать формулы динамическими

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

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

Теперь, если ваша компания позже решит увеличить комиссию до 3%, вы можете просто обновить именованный диапазон, и все расчеты будут автоматически обновлены, чтобы отразить новую комиссию.

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

Вот три способа создания именованных диапазонов в Excel:

Метод № 1 — Использование Define Name

Вот шаги для создания именованных диапазонов в Excel с помощью Define Name:

  • Выберите диапазон, для которого вы хотите создать именованный диапазон в Excel.
  • Перейдите в Формулы -> Определить имя.
  • В диалоговом окне «Новое имя» введите имя, которое вы хотите присвоить выбранному диапазону данных. Вы можете указать область как всю книгу или конкретный рабочий лист. Если вы выберете конкретный лист, имя не будет доступно на других листах.
  • Нажмите ОК.

Это создаст SALESREP именованного диапазона.

Метод № 2: Использование поля имени

  • Выберите диапазон, для которого вы хотите создать имя (не выбирайте заголовки).
  • Перейдите в поле «Имя» слева от панели формул и введите имя, с которым вы хотите создать именованный диапазон.
  • Обратите внимание, что имя, созданное здесь, будет доступно для всей Рабочей книги. Если вы хотите ограничить его рабочим листом, используйте метод 1.

Метод № 3: использование параметра «Создать из выделенного»

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

Например, в наборе данных ниже, если вы хотите быстро создать три именованных диапазона (Date, Sales_Rep и Sales), вы можете использовать метод, показанный ниже.

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

  • Выберите весь набор данных (включая заголовки).
  • Перейдите в Формулы -> Создать из выделенного ( Сочетание клавиш — Control + Shift + F3 ). Откроется диалоговое окно «Создать имена из выделенного».
  • В диалоговом окне «Создать имена из выделенного» отметьте параметры, в которых у вас есть заголовки. В этом случае мы выбираем только верхнюю строку, поскольку заголовок находится в верхней строке.Если у вас есть заголовки и в верхнем ряду, и в левом столбце, вы можете выбрать оба. Точно так же, если ваши данные упорядочены, когда заголовки находятся только в левом столбце, вы выбираете только параметр «Левый столбец».

Это создаст три именованных диапазона — Date, Sales_Rep и Sales.

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

Соглашение об именах для именованных диапазонов в Excel

Существуют определенные правила именования, которые необходимо знать при создании именованных диапазонов в Excel:

  • Первым символом именованного диапазона должна быть буква и символ подчеркивания (_) или обратная косая черта (\).Если это что-то еще, отобразится ошибка. Остальные символы могут быть буквами, цифрами, специальными символами, точкой или подчеркиванием.
  • Нельзя использовать имена, которые также представляют ссылки на ячейки в Excel. Например, вы не можете использовать AB1, поскольку это также ссылка на ячейку.
  • Вы не можете использовать пробелы при создании именованных диапазонов. Например, у вас не может быть торгового представителя в качестве именованного диапазона. Если вы хотите объединить два слова и создать именованный диапазон, используйте для его создания символы подчеркивания, точки или прописные буквы.Например, у вас может быть Sales_Rep, SalesRep или SalesRep.
    • При создании именованных диапазонов Excel обрабатывает прописные и строчные буквы одинаково. Например, если вы создаете именованный диапазон SALES, вы не сможете создать другой именованный диапазон, такой как «sales» или «Sales».
  • Именованный диапазон может содержать до 255 символов.

Слишком много именованных диапазонов в Excel? Не волнуйтесь

Иногда в больших наборах данных и сложных моделях вы можете создать много именованных диапазонов в Excel.

Что делать, если вы не помните имя созданного вами именованного диапазона?

Не волнуйтесь — вот несколько полезных советов.

Получение имен всех именованных диапазонов

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

  • Перейдите на вкладку Формулы.
  • В группе «Определенные именованные» щелкните «Использовать в формуле».
  • Щелкните «Вставить имена».

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

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

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

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

  • Перейдите на вкладку «Формулы» и нажмите «Диспетчер имен».
  • В диалоговом окне «Диспетчер имен» будут перечислены все именованные диапазоны в этой книге. Дважды щелкните именованный диапазон, который вы хотите отредактировать.
  • В диалоговом окне «Редактировать имя» внесите изменения.
  • Нажмите ОК.
  • Закройте диалоговое окно «Диспетчер имен».

Полезные сочетания клавиш с именованными диапазонами (сила F3)

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

  • Чтобы получить список всех именованных диапазонов и вставить его в формуле: F3
  • Чтобы создать новое имя с помощью диалогового окна Диспетчера имен: Control + F3
  • Для создания именованных диапазонов из выделенного: Control + Shift + F3

Создание динамических именованных диапазонов в Excel

До сих пор в этом руководстве мы создали статические именованные диапазоны.

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

Например, если A1: A10 был назван «Продажи», он всегда будет относиться к A1: A10.

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

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

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

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

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

Давайте посмотрим, как создавать динамические именованные диапазоны в Excel.

Предположим, у нас есть данные о продажах в ячейке A2: A11.

Вот шаги для создания динамических именованных диапазонов в Excel:

    1. Перейдите на вкладку «Формула» и нажмите «Определить имя».
    2. В диалоговом окне «Новое имя» введите следующее:
      • Имя: Продажи
      • Область: Рабочая книга
      • Относится к: = $ A $ 2: ИНДЕКС ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”<>” & ””))
    3. Нажмите ОК.

Готово!

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

Как работают динамические именованные диапазоны?

Чтобы объяснить, как это работает, вам нужно немного больше узнать о функции ИНДЕКС в Excel.

Большинство людей используют ИНДЕКС для возврата значения из списка на основе номера строки и столбца.

Но у функции ИНДЕКС есть и другая сторона.

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

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

 = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "<> "&" ")) 

ИНДЕКС ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100,” <> ”&” ”) -> Ожидается, что эта часть формулы вернет значение ( что будет 10-м значением в списке, учитывая, что имеется десять элементов).

Однако, когда используется перед ссылкой (= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”<>” & ””))) он возвращает ссылку на ячейку вместо значения.

Следовательно, здесь он возвращает = $ A $ 2: $ A $ 11

Если мы добавим два дополнительных значения в столбец продаж, он вернет = $ A $ 2: $ A $ 13

При добавлении новых данных в список , Функция Excel СЧЁТЕСЛИ возвращает количество непустых ячеек в данных. Этот номер используется функцией ИНДЕКС для получения ссылки на ячейку последнего элемента в списке.

Примечание:

  • Это будет работать, только если в данных нет пустых ячеек.
  • В приведенном выше примере я назначил большое количество ячеек (A2: A100) для формулы именованного диапазона.Вы можете настроить это на основе вашего набора данных.

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

Вам также могут понравиться следующие ресурсы Excel:

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

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

Имена Excel — Введение

В Excel вы можете создавать имена, относящиеся к:

  • Ячейки на листе
  • Удельное значение
  • Формула

После тебя определить имена Excel, вы можете:

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

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

ПРИМЕЧАНИЕ : Чтобы создать быстрый список всех имен в книге, см. Инструкции «Быстрый список имен — без макросов».

Как назвать ячейки

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

Ячейки имени — Поле имени

Вы можете быстро присвоить имя выбранным ячейкам, введя имя в поле «Имя». ПРИМЕЧАНИЕ : существует несколько правил для имен Excel

  1. Выберите ячейки, которым следует присвоить имя
  2. Щелкните поле Имя слева от строки формул
  3. Введите допустимое имя из одного слова для списка, например.грамм. FruitList.
  4. Нажмите клавишу Enter.

Правила создания имен

На сайте Microsoft есть правила для имен Excel, которые кратко изложены ниже.

Подробно о том, какие символы можно использовать, см. В проекте Мартина Траммера на GitHub excel-names — в нем есть примеры и файл Excel для загрузки.

Следуйте этим правилам Microsoft при создании имени в Excel.

  • Первый символ имени должен быть одним из следующих символов:
    • письмо
    • подчеркивание (_)
    • обратная косая черта (\).
  • Остальные символы в имени могут быть
    • буквы
    • номера
    • периода
    • символа подчеркивания
  • Следующие запрещены :
    • Пробелы не могут быть частью имени.
    • Имена не могут выглядеть как адресов ячеек , например, 35 австралийских долларов или R2D2
    • C, c, R, r — не могут использоваться в качестве имен — Excel использует их как ярлыки выбора
  • Имена без учета регистра . Например, Север и СЕВЕР обрабатываются как одноименное.

См. Названия

ПРИМЕЧАНИЕ : Чтобы создать быстрый список всех имен в книге, см. Инструкции «Быстрый список имен — без макросов».

Чтобы увидеть некоторые из названных диапазонов на листе, используйте этот трюк:

  • В правом нижнем углу окна Excel щелкните настройку уровня масштабирования
  • В диалоговом окне Zoom выберите Custom
  • Введите 39 в поле процентов и нажмите ОК

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

  • Имена, созданные с помощью формулы, например YearList, не отображаются.
  • Некоторые диапазоны могут быть слишком маленькими, чтобы отображать их имена

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

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

Выполните следующие действия, чтобы изменить эталонный диапазон:

  1. На ленте щелкните вкладку Формулы
  2. Нажмите «Диспетчер имен»
  3. В списке щелкните имя, которое вы хотите изменить
  4. В поле «Относится к» измените ссылку на диапазон или перетащите лист, чтобы выбрать новый диапазон.
  5. Щелкните галочку, чтобы сохранить изменение
  6. Нажмите «Закрыть», чтобы закрыть диспетчер имен TOP

Создание имен из текста ячейки

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

Создание имен из текста ячейки

Быстрый способ создания имен — основывать их на тексте ячейки заголовка (рабочий лист этикетки).В примере, показанном ниже, ячейки в столбце E будут назван на основе меток в столбце D.

ПРИМЕЧАНИЕ : Если метки содержат пробелы, они заменяются на подчеркивание. Другие недопустимые символы, такие как & и #, будут быть удаленным или замененным символом подчеркивания.

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

  1. Выберите метки и ячейки, которым необходимо присвоить имя.Этикетки может быть сверху, снизу, слева или справа от именованных ячеек. В в этом примере метки находятся в столбце B слева от ячеек. который будет назван.
  2. На ленте щелкните вкладку Формулы, затем щелкните Создать из Выбор.
  3. В окне «Создать имена из выделенного» установите флажок для расположение меток, затем нажмите ОК.В этом примере метки находятся в левом столбце выбранных ячеек.
  4. Щелкните ячейку, чтобы увидеть ее имя. На снимке экрана ниже ячейка Выбран C4, и вы можете увидеть его имя в поле имени — Full_Name.
    ПРИМЕЧАНИЕ : Пробел был заменен на подчеркивание.

Создать имя для значения

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

Часто используемые значения

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

  • Имя: TaxRate
  • относится к: = 0,5

Затем используйте это имя в формулах вместо ввода значения

Особые значения

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

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

  • Имя: XL_Max
  • Обращается к: 9.99999999999999E + 307

Затем используйте имя XL_Max в формулах, как в этой формуле ПРОСМОТР, которая находит последнее число в столбце.

= ПРОСМОТР (9.99999999999999E + 307, данные о весе [вес])

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

После создания имен вы можете их использовать:

Использовать имена для быстрой навигации

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

ПРИМЕЧАНИЕ : Если имя не отображается в раскрывающемся списке, вы можете ввести имя вместо

Использование имен в формулах

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

= СУММ (количество)

Уловки с именами

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

Изменить размер поля имени

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

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

Выбрать ячейки

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

Показать столбцы

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

  • Скрыть столбцы от A до J
  • Щелкните в поле имени
  • Введите a1, e1, h2 в поле имени и нажмите Enter.
  • Затем используйте команду Показать, чтобы показать выбранные столбцы
    • Вкладка «Главная»> Формат> Скрыть и показать> Показать столбцы

Заполнить ячейки

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

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

Вот как создать список из 1000 номеров в столбце A:

  • Щелкните в поле имени
  • Введите a1: a1000 в поле имени и нажмите Enter
  • .
  • Выделив ячейки, введите число 1 и нажмите Ctrl + Enter
  • Затем выберите ячейку A1 и введите 1-е число в своей серии, e.грамм. 5
  • Выберите ячейку A2 и введите 2-е число в своей серии, например 10
  • Выберите ячейки A1 и A2 и дважды щелкните маркер заполнения, чтобы создать серию из 1000 чисел

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

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

Используйте именованную таблицу Excel

Используйте формулу

Используйте именованную таблицу Excel

Самый простой способ создать динамический именованный диапазон — начать с создания именованного Таблица Excel. Затем определите диапазон на основе одного или нескольких столбцов. в этой таблице.

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

Сначала создайте таблицу:
  1. Выберите ячейку в списке деталей
  2. На вкладке Вставка ленты щелкните Таблица
  3. Убедитесь, что выбран правильный диапазон, и добавьте проверку отметка для моей таблицы имеет заголовки
  4. Щелкните OK, чтобы создать таблицу.
  5. (необязательно) Измените имя таблицы по умолчанию (например,грамм. Table1) на значащее имя, например tblParts
Затем создайте динамический список идентификаторов деталей:
  1. Выберите ячейки A2: A9, которые содержат идентификаторы деталей (не заголовок)
  2. Щелкните в строке формул и введите имя диапазона из одного слова: PartIDList
  3. Нажмите клавишу Enter, чтобы ввести имя.

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

  • Щелкните вкладку Формулы ленты и щелкните Диспетчер имен.
  • В списке два названных элемента:
    1. Детали table с именем по умолчанию, Table1 (или именем, которое вы дали таблице)
    2. PartIDList, который на основе поля PartID в таблице 1.

Текст динамического диапазона

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

  • Добавить новый элемент в список идентификаторов деталей
  • В поле Имя выберите имя PartIDList
  • Выбран именованный диапазон, который включает новый идентификатор детали. ТОП

Динамический именованный диапазон — Формула

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

Письменные инструкции под видео.

Динамический именованный диапазон на основе формулы

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

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

  1. На ленте щелкните вкладку Формулы
  2. Нажмите «Определить имя»
  3. Введите имя диапазона, например Список имен
  4. Оставьте для параметра «Область действия» значение «Рабочая книга».
  5. В поле «Относится к» введите формулу смещения, которая определяет размер диапазона, основанный на количестве элементов в столбце, e.g .:
    = OFFSET (Sheet1! $ A $ 1,0,0, COUNTA (Sheet1! $ A: $ A), 1)
    В этом примере список находится на Sheet1, начиная с ячейки A1
    Аргументы в этой функции смещения используются:
  • Ссылочная ячейка: Sheet1! $ A $ 1
  • Строки для смещения: 0
  • Столбцы для смещения: 0
  • Количество строк: COUNTA (Sheet1! $ A: $ A)
  • Количество столбцов: 1
  • Примечание : для динамического количества столбцов замените 1 с:
    COUNTA (Sheet1! $ 1: $ 1)
  • Нажмите OK TOP
  • Получить файл образца

    Чтобы следовать инструкциям на этой странице, загрузите образец файла имен Excel.Заархивированный файл имеет формат xlsx и не содержит макросов. ТОП

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

    Использование имен в формулах

    Именованные таблицы Excel

    Excel имена макросов

    Создание динамических диапазонов с помощью макроса

    Диапазон

    в Excel — Easy Excel Tutorial

    Ячейка, строка, столбец | Примеры диапазонов | Заполнить диапазон | Переместить диапазон | Копировать / вставить диапазон | Вставить строку, столбец

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

    Ячейка, строка, столбец

    Начнем с выбора ячейки, строки и столбца.

    1. Чтобы выбрать ячейку C3, щелкните поле на пересечении столбца C и строки 3.

    2. Чтобы выбрать столбец C, щелкните заголовок столбца C.

    3. Чтобы выбрать строку 3, щелкните заголовок строки 3.

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

    Диапазон — это набор из двух или более ячеек.

    1. Чтобы выбрать диапазон B2: C4, щелкните ячейку B2 и перетащите ее в ячейку C4.

    2. Чтобы выбрать диапазон отдельных ячеек, удерживая нажатой клавишу CTRL, щелкните каждую ячейку, которую вы хотите включить в диапазон.

    Заполнить диапазон

    Чтобы заполнить диапазон, выполните следующие шаги.

    1а. Введите значение 2 в ячейку B2.

    1б. Выберите ячейку B2, щелкните в правом нижнем углу ячейки B2 и перетащите его в ячейку B8.

    результат:

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

    2а. Введите значение 2 в ячейку B2 и значение 4 в ячейку B3.

    2б. Выберите ячейку B2 и ячейку B3, щелкните в правом нижнем углу этого диапазона и перетащите его вниз.

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

    3а. Введите дату 13.06.2016 в ячейку B2 и дату 16.06.2016 в ячейку B3.

    3б.Выберите ячейку B2 и ячейку B3, щелкните в правом нижнем углу этого диапазона и перетащите его вниз.

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

    Переместить диапазон

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

    1. Выберите диапазон и щелкните границу диапазона.

    2. Перетащите диапазон на новое место.

    Копировать / вставить диапазон

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

    1. Выберите диапазон, щелкните правой кнопкой мыши и затем щелкните Копировать (или нажмите CTRL + c).

    2. Выберите ячейку, в которой должна появиться первая ячейка диапазона, щелкните правой кнопкой мыши и затем выберите Вставить в разделе «Параметры вставки:» (или нажмите CTRL + v).

    Вставить строку, столбец

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

    Оставить комментарий

    Добавить комментарий

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