Разное

Как сдвинуть таблицу в экселе вниз: Как в excel сдвинуть строку вниз Excelka.ru

Содержание

Как в excel сдвинуть строку вниз

Перемещение строк в Microsoft Excel

​Смотрите также​ мере изменения количества​: Теперь все работает.​Второй макрос -​ последнего заполненного значения,​Вопрос — как​Роман гранов​ подскажу как сделать​Так вот, в​ чтобы сравнить A,​ и строку.​ лично под себя,​ панели координат той​, которая локализуется на​ открывается небольшое окошко,​, находящуюся во вкладке​ пустого ряда с​Работая в Эксель, иногда​ строк в первой​ Спасибо за ответы.​ выдает ошибку «Run-time​ Выбрать последнюю заполненную​ для средней таблицы​: добавить ряд ячеек​

​ все намного проще,​ примере видно. Например​

​ B и E.​​Выделите столбец, который требуется​ но, безусловно, можно​

Смена положения строк

​ строки, которую желаем​ ленте во вкладке​ которое предлагает выбрать,​«Главная»​ добавлением в него​ можно столкнуться с​

Способ 1: процедура копирования

​ таблице, ненужные строки​Асааа​ error ‘1004’ Method​ ячейку и отобразить​ G:H произвести вставку​Сергей​ чтобы все данные​ если все заполнено,​ Точно таким же​ переместить, щелкнув по​ сказать, что самым​ переместить.

​«Главная»​ что нужно удалить.​

  1. ​на инструментальной ленте​ содержимого другого, с​ необходимостью поменять строки​ скрывать.​: Здравствуйте, ребят. Хотел​ ‘Range’ of object​ ее значение, Отображение​ «ячейки со сдвигом​​: вырезать / вставить​​ ВСЕХ комплектующих компьютеров​

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

  3. ​ местами. Для этого​Асааа​ бы спросить, а​ ‘_Global’ failed»​ ФИО последнего заполнившего​ вниз» — на​ ячейкой ниже​ стекались к тебе​​ кабинете информатики скажем​​ скрыть и строки.​​ нажмите команду Вырезать​​ строчки местами является​ границу этой строки,​
    ​«Буфер обмена»​
    ​ позицию напротив пункта​«Буфер обмена»​ Но, как мы​​ существует несколько проверенных​​: Юрий М, Cпасибо​

  4. ​ как сдвинуть таблицу​boa​ отчет Менеджера, копирования​ три строки вниз​ВасилисаЛукьянчикова​​ на твой компьютер.​ «обнаружится» еще что-либо.​​Выделите столбцы, которые необходимо​​ на вкладке Главная​​ вариант с перетягиванием​​ пока он не​. Она представлена пиктограммой​«Строку»​​. Вместо этого действия​​ позже установим, хотя​

  5. ​ способов. Одни из​ за ответ, но​ вниз? Т.е, есть​:​ и вставка данных​ (начиная с последней​: Добрый вечер.​Тофан м​ . Как сместить​ скрыть. Затем щелкните​​ или комбинацию клавиш​​ при помощи мышки.​

  6. ​ примет форму стрелки,​ в виде ножниц.​. Жмем на кнопку​ можно набрать комбинацию​ этот вариант сам​ них выполняют перемещение​ не помогло.​
    ​ 2 таблицы… Они​

Перемещение и копирование ячеек, строк и столбцов

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

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

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

  2. Выполните одно из указанных ниже действий.

    • Чтобы переместить строки или столбцы, на вкладке Главная в группе Буфер обмена нажмите кнопку Вырезать .

      Сочетание клавиш: CTRL+X.

    • Чтобы скопировать строки или столбцы, на вкладке Главная в группе Буфер обмена нажмите кнопку Копировать .

      Сочетание клавиш: CTRL+C.

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

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

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

      Примечание: Если вместо выбора команды в контекстном меню нажать кнопку Вставить на вкладке Главная в группе Буфер обмена (или нажать клавиши CTRL+V), содержимое конечных ячеек будет заменено.

Перемещение и копирование строк и столбцов с помощью мыши

  1. Выделите строки или столбцы, которые вы хотите переместить или скопировать.

  2. Выполните одно из указанных ниже действий.

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

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

      Важно: При перетаскивании удерживайте клавишу CTRL нажатой. Если отпустить клавишу CTRL раньше кнопки мыши, строки или столбцы будут перемещены, а не скопированы.

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

    Примечание: С помощью мыши невозможно переместить или скопировать несмежные строки или столбцы.

Перемещение и копирование ячеек

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

  2. На указателе на границу выбранной ячейки или диапазона.

  3. Когда указатель станет , сделайте следующее:

Задача

Необходимые действия

Перемещение ячеек

Перетащите ячейки в другое место.

Копирование ячеек

Удерживая на удержании option, перетащите ячейки в другое место.

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

Перемещение и копирование строк и столбцов

  1. Вы выберите строки или столбцы, которые нужно переместить или скопировать.

  2. На указателе на границу выбранной ячейки или диапазона.

  3. Когда указатель станет , сделайте следующее:

Задача

Необходимые действия

Перемещение строк и столбцов

Перетащите строки или столбцы в другое место.

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

Удерживая на удержании option, перетащите строки или столбцы в другое место.

Перемещение и копирование данных между существующими строками или столбцами

Удерживая на удержании shift, перетащите строку или столбец между существующими строками или столбцами. Excel выделяет место для новой строки или столбца.

Преобразование столбцов в строки, а строки в столбцы

  1. Скопируйте строки или столбцы, которые нужно транспонируйте.

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

  3. На вкладке «Главная» в области «Правка»щелкните стрелку рядом с кнопкой «Ввести» и выберите «Транспонировать».

    Примечание: Перекрытие столбцов и строк невозможно. Например, если вы выберете значения в столбце C и попробуете вклеить их в строку, которая перекрывает столбец C, в Excel отобразит сообщение об ошибке. Destination area of a pasted column or row must be outside the original values.

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

Вставка строк, столбцов или ячеек

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

Видео

Постановка задачи

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

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

Решение

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table):

 

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

В результате после такого преобразования диапазона в «умную» Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

  1. Созданная Таблица получает имя Таблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design). Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
  2. Созданная один раз Таблица автоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:


     

  3. В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data)).
  4. При добавлении новых строк в них автоматически копируются все формулы.
  5. При создании нового столбца с формулой — она будет автоматически скопирована на весь столбец — не надо тянуть формулу черным крестом автозаполнения.
  6. При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т. е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):

  7. Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:


  8. К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):
  • =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
  • =Таблица1[#Данные] — ссылка только на данные (без строки заголовка)
  • =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
  • =Таблица1[#Итоги] — ссылка на строку итогов (если она включена)
  • =Таблица1[#Эта строка] — ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] — будет ссылаться на значение НДС из текущей строки таблицы.

    (В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).

P.S.

В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы — называлось Списком и создавалось через меню Данные — Список — Создать список (Data — List — Create list). Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.

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

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

Добавление ячеек

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

  • Выделите ячейки А1:К6 (диапазон, отвечающий области ввода).
  • Нажмите правую кнопку мыши и в контекстном меню активизируйте команду Добавить ячейки.
  • В открывшемся диалоговом окне Добавление ячеек (рис. 3.9) выберите переключатель ячейки, со сдвигом вниз и нажмите кнопку ОК.

Данное диалоговое окно также вызывается посредством команды Вставка/ Ячейки.

Вставка строк

Второй способ (менее трудоемкий) — это добавление шести строк сверху:

  • Выделите мышью строки 1-6.
  • Нажмите правую кнопку мыши и в контекстном меню выберите команду Добавить ячейки.

Перетягивание таблицы

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

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

Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.

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

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

Первый шаг.

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

Второй шаг.

Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»

Третий шаг.

Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.

Четвертый шаг.

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

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

Результат вставки связи

Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».

При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.

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

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

О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».

Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».

Смысл способа в следующем:

Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».

Из таблицы -Power Query

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

Источник данных для запроса Power Query

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

После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»

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

Обновление запроса в PowerQuery

2 способ
Копируем таблицу
на новом Листе щелкаем правой кнопкой — Специальная вставка — ширина столбцов — ОК
Если кроме ширины надо оставить еще и формулы, тогда не снимая выделения выбирам нужный пункт (после каждого пункта надо нажимать ОК )

3 способ
Если надо составить сразу n одинаковых таблиц (напр 12 месяцев) тогда щелкаем ПКМ по названию Листа — «Выбрать все Листы»
На всех Листах получится точная копия (ширина столбцов, шрифт, заливка, формулы . )

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

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

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

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

Так или иначе, здесь Вы найдёте три действующих способа перемещать столбцы в Excel, а именно:

Как в Excel перетаскивать столбцы мышью

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

Предположим, есть таблица с информацией о товарах Вашей компании, и Вы хотите быстренько поменять местами пару столбцов в этой таблице. Я возьму для примера прайс сервиса AbleBits. Необходимо поменять местами столбцы License type и Product ID, чтобы идентификатор Product ID следовал сразу после наименования продукта (Product name).

  1. Выделите столбец, который требуется передвинуть.
  2. Наведите указатель мыши на край выделения, при этом он должен превратиться из обычного креста в четырёхстороннюю стрелку. Лучше не делать это рядом с заголовком столбца, поскольку в этой области указатель может принимать слишком много различных форм, что может Вас запутать. Зато этот прием отлично работает на левом и правом краю выделенного столбца, как показано на скриншоте ниже.
  3. Нажмите и, удерживая клавишу Shift, перетащите столбец на новое место. Вы увидите серую вертикальную черту вдоль всего столбца и указатель с информацией о том, в какую область столбец будет перемещён.
  4. Готово! Отпустите кнопку мыши, отпустите клавишу Shift – Ваш столбец перемещён на новое место.

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

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

Метод перетаскивания работает в Microsoft Excel 2013, 2010 и 2007. Точно так же Вы можете перетаскивать строки. Возможно, придётся немного попрактиковаться, но, освоив этот навык однажды, Вы сэкономите уйму времени в дальнейшем. Думаю, команда разработчиков Microsoft Excel вряд ли получит приз в номинации “самый дружественный интерфейс” за реализацию этого метода

Перемещаем столбцы методом Вырезать > Вставить

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

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

  1. Выделите весь столбец, кликнув по его заголовку.
  2. Вырежьте выделенный столбец, нажав Ctrl+X, или кликните по нему правой кнопкой мыши и в контекстном меню выберите команду Cut (Вырезать). На самом деле, первый шаг Вы можете вообще пропустить и просто кликнуть правой кнопкой мыши по заголовку, а затем из контекстного меню выбрать команду Cut (Вырезать).
  3. Выделите столбец, перед которым необходимо вставить вырезанный, кликните правой кнопкой мыши по его заголовку и в контекстном меню выберите команду Insert Cut Cells (Вставить вырезанные ячейки).

Если Вы любите работать в Excel с клавиатурными комбинациями, тогда Вам больше понравится следующий способ:

  • Выделите любую ячейку в нужном столбце и нажмите Ctrl+Space (Пробел), чтобы выделить весь столбец.
  • Нажмите Ctrl+X, чтобы вырезать столбец.
  • Выберите столбец, перед которым необходимо вставить вырезанный.
  • Нажмите Ctrl и знак “+” на цифровой клавиатуре, чтобы вставить столбец.

Как переместить несколько столбцов в Excel

Метод Вырезать > Вставить, который отлично работает для одного столбца (либо смежных столбцов), не позволяет перемещать несколько несмежных столбцов одновременно. Если Вы попытаетесь сделать это, то получите сообщение об ошибке, предлагающее выбрать один смежный диапазон: The command you chose cannot be performed with multiple selections (Данная команда неприменима для несвязных диапазонов).

Если требуется поменять местами несколько столбцов на листе, то у Вас есть минимум 3 варианта на выбор:

Перемещаем несколько столбцов при помощи команд Копировать, Вставить и Удалить

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

  1. Выделите столбцы, которые хотите переместить (кликните по заголовку первого столбца, нажмите Shift и кликните по заголовку крайнего столбца). Альтернативный способ – выделить только заголовки столбцов, которые нужно передвинуть, и нажать Ctrl+Space (Пробел). Таким способом будут выделены только ячейки с данными, а не столбцы целиком, как показано на снимке экрана ниже.

Замечание: Если Вы передвигаете столбцы в обычном диапазоне, то подойдут оба способа. Если же нужно переместить столбцы в полноценной таблице Excel, тогда воспользуйтесь вторым способом (только ячейки с данными), иначе можно получить сообщение об ошибке: The operation is not allowed. The operation is attempting to shift cells in a table of your worksheet (Невозможно выполнить операцию. Предпринимается попытка изменить часть строки или столбца таблицы запрещённым способом).

  1. Скопируйте выделенные ячейки. Для этого нажмите Ctrl+C или кликните правой кнопкой мыши по выделенным столбцам и выберите команду Copy (Копировать).
  2. Выделите столбец, перед которым Вы хотите вставить скопированные столбцы. Кликните по нему правой кнопкой мыши и в контекстном меню выберите команду Insert copies cells (Вставить скопированные ячейки), либо нажмите одновременно Ctrl и знак “+” на цифровой клавиатуре.
  3. Удалите исходные, а также ненужные Вам столбцы.

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

Изменяем очерёдность столбцов в Excel при помощи макроса VBA

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

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

Глава 3. Работа с таблицами Excel

Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.). В этой главе мы обсудим различные методы работы с таблицами, включая создание таблиц, изменение их размера, вставку и удаление строк и столбцов, перемещение столбцов.

Предыдущая глава        Содержание    Следующая глава

Рис. 3.1. Диалоговое окно Создание таблицы

Скачать заметку в формате Word или pdf, примеры в формате Excel

Создание таблицы

Вы можете создать таблицу с данными или пустую таблицу. При создании таблицы Excel запрашивает расположение данных и наличие заголовков (рис. 3.1). Если строки заголовков нет, снимите флажок, и Excel создаст заголовки по умолчанию: Столбец1, Столбец2 и т.д.

Таблицу можно создать командами ленты. Пройдите по меню Главная –> Стили –> Форматировать как таблицу (рис. 3.2). Выберите один из предустановленных форматов таблицы. Excel откроет диалоговое окно Создание таблицы, как на рис. 3.1.

Рис. 3.2. Создание таблицы кнопками ленты

Также можно пройти по меню Вставка –> Таблица (рис. 3.3). Excel снова откроет диалоговое окно Создание таблицы.

Рис. 3.3. Вставка таблицы

Но, конечно же быстрее вставить таблицу с помощью клавиатурных сокращений. Нажмите Ctrl+T (английское) или Ctrl+L. Эти две команды эквивалентны, и обе открывают диалоговое окно Создание таблицы. С помощью клавиш также можно пройти по меню ленты. Alt+Я+Ь открывает стили таблицы, как на рис. 3.2. После чего стрелками можно выбрать нужный стиль и нажать Enter. Ну а Alt+С+1 эквивалентно вызову таблицы из меню Вставка (как на рис. 3.3).

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

Изменение размеров таблиц

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

Чтобы изменить размер таблицы вручную:

  • Используйте небольшой значок, расположенный в правом нижнем углу таблицы (рис. 3.4). Перетащив маркер, можно изменить число строк или число столбцов Таблицы. Но не одновременно. Чтобы изменить число строк и число столбцов, используйте маркер дважды.
  • Или активируйте вкладку Конструктор и кликните на кнопке Размер таблицы. Excel отобразит диалоговое окно Изменение размера таблицы (рис. 3.5). Введите диапазон с клавиатуры или выберите мышкой на листе и нажмите кнопку ОК. Можно выбрать любой диапазон, если выбрана хотя бы одна ячейка заголовка и одна ячейка тела. Новый диапазон должен перекрывать текущий диапазон таблицы.

Рис. 3.4. Маркер ручного изменения размера Таблицы

Рис. 3.5. Диалоговое окно Изменение размера таблицы

Автоматическое изменение размера таблицы

Когда вы вводите данные в первую пустую строку под таблицей, Excel автоматически увеличивает таблицу, чтобы включить эту новую строку данных. Новая строка инициализируется всеми форматами и формулами в вычисляемых столбцах. Можно указать Excel, следует ли автоматически включать новые строки и столбцы при вводе значений в ячейки, расположенные рядом с таблицей. Для этого пройдите по меню Файл –> Параметры. В окне Параметры Excel перейдите на вкладку Правописание, кликните на кнопку Параметры автозамены. В окне Автозамена перейдите на вкладку Автоформат при вводе. Установите или снимите флажок Включить в таблицу новые строки и столбцы. Эта опция, которая является опцией уровня приложения (а не книги), включена по умолчанию.

Рис. 3.6. Параметры Excel для управления поведением Таблиц по умолчанию; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

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

Сохранение пустых строк под таблицей

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

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

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

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

Перемещение столбцов

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

Вставка строк и столбцов

Не предусмотрена возможность вставлять строки или столбцы с вкладки ленты Конструктор. Это можно сделать пройдя по меню Главная –> Ячейки –> Вставить:

Рис. 3.8. Вставка столбца или строки с вкладки Главная

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

Рис. 3.9. Вставка столбца или строки из контекстного меню правой кнопкой мыши

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

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

Удаление строк и столбцов

На вкладке ленты Конструктор нет возможности удалить строки или столбцы. Для этого можно пройти по меню Главная –> Ячейки –> Удалить:

Рис. 3.10. Удаление столбца или строки с вкладки Главная

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

Рис. 3.11. Удаление столбца или строки из контекстного меню правой кнопкой мыши

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

Преобразование таблицы в диапазон

Некоторые ограничения препятствуют определенным действиям с таблицами, поэтому бывает полезно преобразовать таблицу в диапазон. При этом все структурированные ссылки преобразуются в стандартные (абсолютные) ссылки на ячейки. Например, формула со структурированными ссылками =[@Units]*[@Cost] преобразуется в =Лист1!$H5*Лист1!$I5.

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

Удаление повторяющихся строк

Пройдите по меню Конструктор –> Инструменты –> Удалить дубликаты. Excel запросит, какие столбцы использовать, чтобы определить, когда строка является дубликатом другой строки:

Рис. 3.12. Удаление дубликатов строк

Excel сохраняет первую уникальную строку, удаляет все последующие повторяющиеся строки и сдвигает вверх строки ниже удаления.

Параметры Таблиц

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

Рис. 3.13. Параметры таблиц

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

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

Экспорт данных таблицы

Существует два способа экспорта данных из таблицы: экспорт в SharePoint в виде списка или экспорт в Visio в виде сводной схемы. Для обоих этих вариантов необходимо иметь соответствующее приложение.

Экспорт в Visio

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

Рис. 3.14. Экспорт Таблицы в Visio

Рис. 3.15. Панель задач сводной схемы Visio после экспорта таблицы Excel

Дополнительную информацию можно получить на сайте Microsoft – Создание сводной схемы в Visio.

Экспорт в SharePoint

SharePoint – это служба общего доступа по умолчанию, используемая в Office 365 и Power BI. Чтобы совместно использовать таблицу с помощью SharePoint, выберите таблицу и пройдите по меню Конструктор –> Экспорт –> Экспорт таблицы в список SharePoint (см. рис. 3.14).

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

Рис. 3.16. Экспорт в список SharePoint, шаг 1

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

На втором шаге мастер экспорта отобразит типы данных столбцов:

Рис. 3.17. Типы данных экспортируемой таблицы

Внимательно прочитайте указания в окне мастера. Проверьте всё ли Ok. Нажмите Готово.

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

При экспорте таблицы Excel на сайт SharePoint каждому столбцу в списке SharePoint назначается один из следующих типов данных: Текст (однострочный), Текст (многострочный), Денежный, Дата и время, Числовой, Гиперссылка (URL-адрес). Другие типы данных недопустимы. Если столбец содержит ячейки с разными типами данных, применяется тип данных, который может использоваться для всех ячеек. Например, если столбец содержит числа и текст, в списке SharePoint будет использоваться текстовый тип.

Появится сообщение о том, что ваша таблица опубликована, а также URL-адрес списка (рис. 3.18). Чтобы перейти к списку, щелкните URL-адрес (рис. 3.19). Добавьте URL-адрес в избранное в своем браузере. Можно открыть список и другим способом. Перейдите на сайт SharePoint, в правом верхнем углу щелкните значок шестеренки и выберите элемент Контент сайта (рис. 3.20). Откроется окно доступного контента. Выберите ваш список.

Рис. 3.18. Таблица Excel успешно экспортирована в список SharePoint

Рис. 3.19. Список SharePoint в web-браузере

Рис. 3.20. Открытие списка SharePoint инструментами сайта

Это соединение является OLE DB соединением, которое можно просмотреть, пройдя по меню Конструктор –> Обновить –> Свойства подключения. В окне Свойства подключения перейдите на вкладку Определение. У меня Строка подключения выглядит так:

Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="";ApplicationName=Excel;Version=12.0.0.0

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

Перемещение столбцов в Excel, добавление, удаление, автозаполнение ширины столбца

Обожаю продукты!

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

Дэвид Джонстон

Это лучшие 100 долларов, которые мы когда-либо тратили!

Я посоветовал своему руководителю приобрести Ultimate Suite. Я часто имею дело с огромными таблицами, и сопоставление версий всегда было кошмаром.Он спросил меня о новых инструментах, и я сказал: «Это лучшие 100 долларов, которые мы когда-либо тратили!» Действительно сэкономил мне часы рабочего времени. Итак, одно большое «Спасибо!»

Майк Кавана

Невероятный продукт, еще лучшая техническая поддержка… AbleBits полностью отвечает вашим требованиям!

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

Дебора Брайант

Любой, кто работает с Excel, обязательно найдет облегчение своей работы с помощью Ablebits

Я использую Ultimate Suite почти год и считаю его бесценным для своих проектов. Мне нравится возможность объединять таблицы на основе одного или нескольких атрибутов, транспонировать столбцы и строки и преобразовывать данные с помощью утилиты Cell Cleaner. Кроме того, поддержка Ablebits надежно быстрая и вежливая.

Джеки Ли

Excel сейчас в лучшем виде

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

Annie C.

Благодарю вас, ребята, за решение проблемы, которую наш ИТ-отдел не понимает.

Наши ИТ-специалисты думали, что знают, как должен работать отдел продаж, и уничтожили некоторые файлы данных. С помощью AbleBits я смог регенерировать свою базу данных за несколько кликов. Это было УДИВИТЕЛЬНО. Благодарность вам, ребята, за решение проблемы, которую не понимает даже наш ИТ-отдел.

Erik Peterson

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

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

Nelda Fink

Ablebits — фантастический продукт, простой в использовании и очень эффективный.

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

Дебра Селмер

Мне понравилось все, и время я использую

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

Christian Onyekachi Nwosu

Мне нравится эта программа, и я не могу представить себе использование Excel без нее!

Спасибо за вашу приверженность Ultimate Suite. Мне нравится эта программа, и я не могу представить без нее Excel! Программа окупилась в первый же день ее использования.Ваша команда создала действительно отличный набор инструментов для использования в Excel. Спасибо Спасибо спасибо!

Роберт Мэдсен

Одним словом… ВАУ!

Одним словом… ВАУ! Я недавно купил пакет надстроек для Excel. Я использовал его ОДИН РАЗ, и он окупился. Это буквально сэкономило мне ЧАСЫ работы. КУДОС!

Дэйв Браун

Я не знаю, как отблагодарить вас за ваши надстройки Excel

Я не знаю, как отблагодарить вас достаточно. Я наткнулся на ваш сайт, пытаясь понять, как объединить 2 таблицы.Когда я увидел бесплатную пробную версию, я подумал, насколько хорошо это будет работать? Я скачал его и УДИВЛЕН. Не знаю, как я жил без этого. СПАСИБО за то, что облегчили мне жизнь.

Jennifer Morningstar

Любой, кто это испытал, обязательно полюбит!

Любой, кто это испытал, обязательно полюбит! Есть только 2 способа «использовать» потенциал Excel: стать компьютерщиком или принять Ultimate Suite. Зачем тратить время на рутины, которые можно выполнить одним щелчком мыши? Используйте возможности Excel, не теряя времени и энергии.

Kumar Nepa

Мне нравится ваш продукт

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

Брэд Гибсон

Лучшие деньги, потраченные на программное обеспечение, которые я когда-либо тратил!

Я просто должен сказать вам, что мне нравится ваша коллекция AbleBits. Я не могу сказать вам, сколько времени он сэкономил мне, особенно мастера слияния таблиц, средства удаления дубликатов, мастер слияния ячеек и разделение имен. Это были лучшие деньги, потраченные на программное обеспечение, которые я когда-либо тратил!

Патрик Ро

Отлично !!!

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

Шейла Бланшар

СПАСИБО, СПАСИБО, СПАСИБО !!

Я рад, что есть компания (Ablebits), где поддержка действительно может решить проблему клиента! Прекрасная работа!

Tibor

Это похоже на то, что у меня на плече эксперт, помогающий мне…

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

Линда Шекспир

Ваше программное обеспечение действительно помогает облегчить мою работу

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

Джинни К.

Создание зависимого раскрывающегося списка в Excel [Пошаговое руководство]

Посмотреть видео — Создание зависимого раскрывающегося списка в Excel

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

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

Ниже приведен пример раскрывающегося списка Excel:

В приведенном выше примере я использовал элементы в A2: A6 для создания раскрывающегося списка в C3.

  Прочтите:  Вот подробное руководство по созданию выпадающего списка Excel.

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

В Excel они называются зависимыми раскрывающимися списками.

Ниже приведен пример того, что я имею в виду под зависимым раскрывающимся списком в Excel:

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

Это называется условным или зависимым раскрывающимся списком в Excel.

Создание зависимого раскрывающегося списка в Excel

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

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

Загрузить файл примера

Как это работает? — Условный раскрывающийся список (в ячейке E3) относится к = КОСВЕННО (D3).Это означает, что когда вы выбираете «Фрукты» в ячейке D3, раскрывающийся список в E3 ссылается на именованный диапазон «Фрукты» (с помощью функции КОСВЕННО) и, следовательно, перечисляет все элементы в этой категории.

Важное примечание: Если основная категория состоит из более чем одного слова (например, «Сезонные фрукты» вместо «Фрукты»), тогда вам необходимо использовать формулу = КОСВЕННО (ПОДСТАВИТЬ (D3, ”“, ” _ ”)) Вместо простой функции ДВССЫЛ, показанной выше.

  • Причина этого в том, что Excel не допускает пробелов в именованных диапазонах.Поэтому, когда вы создаете именованный диапазон, используя более одного слова, Excel автоматически вставляет символ подчеркивания между словами. Например, когда вы создаете именованный диапазон с «Сезонными фруктами», он будет называться Season_Fruits в серверной части. Использование функции ПОДСТАВИТЬ в функции ДВССЫЛ позволяет убедиться, что пробелы преобразованы в символы подчеркивания .

Сброс / очистка содержимого зависимого раскрывающегося списка автоматически

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

Например, если вы выберете «Фрукты» в качестве категории, а затем выберете Apple в качестве элемента, а затем вернетесь и измените категорию на «Овощи», в зависимом раскрывающемся списке по-прежнему будет отображаться Apple в качестве элемента.

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

Вот код VBA для очистки содержимого зависимого раскрывающегося списка:

 Private Sub Worksheet_Change (ByVal Target As Range)
При ошибке Возобновить Далее
Если Target.Столбец = 4 Тогда
 Если Target.Validation.Type = 3, то
 Application.EnableEvents = False
 Target.Offset (0, 1) .ClearContents
 Конец, если
Конец, если
exitHandler:
 Application.EnableEvents = True
 Выход из подводной лодки
End Sub 

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

Вот как заставить этот код работать:

  • Скопируйте код VBA.
  • В книге Excel, где у вас есть зависимый раскрывающийся список, перейдите на вкладку «Разработчик» и в группе «Код» нажмите Visual Basic (вы также можете использовать сочетание клавиш — ALT + F11).
  • В окне редактора VB слева в проводнике проекта вы увидите все имена рабочих листов. Дважды щелкните тот, у которого есть раскрывающийся список.
  • Вставьте код в окно кода справа.
  • Закройте редактор VB.

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

Загрузите файл примера

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

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

  • Выберите ячейку, которая имеет зависимые раскрывающиеся списки.
  • Перейти на главную -> Условное форматирование -> Новое правило.
  • В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу для определения ячеек для форматирования».
  • В поле формулы введите следующую формулу: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
  • Установите формат.
  • Щелкните OK.

В формуле используется функция ВПР, чтобы проверить, является ли элемент в зависимом раскрывающемся списке элементом из основной категории. Если это не так, формула возвращает ошибку. Это используется функцией ISERROR для возврата TRUE, что указывает условному форматированию выделить ячейку.

Загрузите файл примера

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

Перемещение или копирование ячеек, строк и столбцов

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

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

  1. Выберите ячейку, строку или столбец, которые вы хотите переместить или скопировать.

  2. Выполните одно из следующих действий:

    • Чтобы переместить строки или столбцы, на вкладке Главная в группе Буфер обмена щелкните Вырезать .

      Сочетание клавиш: нажмите CTRL + X.

    • Чтобы скопировать строки или столбцы, на вкладке Главная в группе Буфер обмена щелкните Копировать .

      Сочетание клавиш: нажмите CTRL + C.

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

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

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

      Примечание: Если вы нажмете Вставить на вкладке Home в группе Clipboard (или нажмите CTRL + V) вместо того, чтобы щелкнуть команду в контекстном меню, вы замените существующее содержимое ячеек назначения.

Перемещение или копирование строк и столбцов с помощью мыши

  1. Выберите строку или столбец, который вы хотите переместить или скопировать.

  2. Выполните одно из следующих действий:

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

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

      Важно: Убедитесь, что вы удерживаете CTRL во время операции перетаскивания. Если вы отпустите CTRL до того, как отпустите кнопку мыши, вы переместите строки или столбцы вместо их копирования.

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

    Примечание. Нельзя перемещать или копировать несмежные строки и столбцы с помощью мыши.

Перемещение или копирование ячеек

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

  2. Наведите указатель мыши на границу выделенной ячейки или диапазона.

  3. Когда указатель становится , выполните одно из следующих действий:

С по

Сделай это

Переместить ячейки

Перетащите ячейки в другое место.

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

Удерживая OPTION, перетащите ячейки в другое место.

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

Перемещение или копирование строк или столбцов

  1. Выберите строки или столбцы, которые вы хотите переместить или скопировать.

  2. Наведите указатель мыши на границу выделенной ячейки или диапазона.

  3. Когда указатель становится , выполните одно из следующих действий:

С по

Сделай это

Перемещение строк или столбцов

Перетащите строки или столбцы в другое место.

Копировать строки или столбцы

Удерживая OPTION, перетащите строки или столбцы в другое место.

Перемещение или копирование данных между существующими строками или столбцами

Удерживая нажатой клавишу SHIFT, перетащите строку или столбец между существующими строками или столбцами. Excel освобождает место для новой строки или столбца.

Преобразование столбцов в строки или строк в столбцы

  1. Скопируйте строки или столбцы, которые нужно транспонировать.

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

  3. На вкладке Home в разделе Edit щелкните стрелку рядом с полем Paste , а затем нажмите Transpose .

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

См. Также

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

Создание раскрывающегося списка — служба поддержки Office

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

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

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

  2. Выберите ячейки, в которые нужно ограничить ввод данных.

  3. На вкладке Данные в разделе Инструменты щелкните Проверка данных или Проверить .

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

  4. Щелкните вкладку Параметры , а затем во всплывающем меню Разрешить щелкните Список .

  5. Щелкните поле Источник , а затем на листе выберите список допустимых записей.

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

  6. Нажмите RETURN или щелкните Expand кнопку, чтобы восстановить диалоговое окно, а затем нажмите ОК .

    Советы:

    • Вы также можете ввести значения непосредственно в поле Источник , разделив их запятыми.

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

    • Вы можете указать собственное сообщение об ошибке для ответа на ввод неверных данных. На вкладке Data щелкните Data Validation or Validate , а затем щелкните вкладку Error Alert .

См. Также

Применить проверку данных к ячейкам

    org/ItemList»>
  1. На новом листе введите записи, которые должны появиться в раскрывающемся списке.В идеале у вас будут элементы списка в таблице Excel.

  2. Выберите ячейку на листе, в которой требуется раскрывающийся список.

  3. Перейдите на вкладку Data на ленте, затем щелкните Data Validation .

  4. На вкладке Параметры в поле Разрешить щелкните Список .

  5. org/ListItem»>

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

    Фрукты, овощи, зерно, молочные продукты, закуски

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

  7. Установите флажок в раскрывающемся списке в ячейке .

  8. Щелкните вкладку Входное сообщение .

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

  9. Щелкните вкладку Предупреждение об ошибке .

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

  10. Нажмите ОК .

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

Создать раскрывающийся список в Excel

Создать раскрывающийся список | Разрешить другие записи | Добавить / удалить элементы | Динамический раскрывающийся список | Удалить раскрывающийся список | Зависимые раскрывающиеся списки | Стол Magic

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

Создать раскрывающийся список

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

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

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

2. На первом листе выберите ячейку B1.

3. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

4. В поле Разрешить щелкните Список.

5. Щелкните в поле «Источник» и выберите диапазон A1: A3 на листе Sheet2.

6. Щелкните OK.

Результат:

Примечание: чтобы скопировать / вставить раскрывающийся список, выберите ячейку с раскрывающимся списком и нажмите CTRL + c, выберите другую ячейку и нажмите CTRL + v.

7. Вы также можете вводить элементы непосредственно в поле «Источник» вместо использования ссылки на диапазон.

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

Разрешить другие записи

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

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

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

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

3. На вкладке «Предупреждение об ошибке» снимите флажок «Показывать предупреждение об ошибке после ввода неверных данных».

4. Щелкните OK.

5. Теперь вы можете ввести значение, которого нет в списке.

Добавить / удалить элементы

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

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

2. Щелкните правой кнопкой мыши и выберите Вставить.

3. Выберите «Сдвинуть ячейки вниз» и нажмите ОК.

Результат:

Примечание. Excel автоматически изменил ссылку на диапазон с Sheet2! $ A $ 1: $ A $ 3 на Sheet2! $ A $ 1: $ A $ 4. Вы можете проверить это, открыв диалоговое окно «Проверка данных».

4. Введите новый элемент.

Результат:

5. Чтобы удалить элемент из раскрывающегося списка, на шаге 2 нажмите «Удалить», выберите «Сдвинуть ячейки вверх» и нажмите «ОК».

Динамический раскрывающийся список

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

1. На первом листе выберите ячейку B1.

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

3. В поле Разрешить щелкните Список.

4. Щелкните поле «Источник» и введите формулу: = СМЕЩЕНИЕ (Sheet2! $ A $ 1,0,0, COUNTA (Sheet2! $ A: $ A), 1)

Объяснение: функция СМЕЩЕНИЕ принимает 5 аргументов.Ссылка: Sheet2! $ A $ 1, строки для смещения: 0, столбцы для смещения: 0, высота: COUNTA (Sheet2! $ A: $ A) и ширина: 1. COUNTA (Sheet2! $ A: $ A) подсчитывает число значений в столбце A на Листе 2, которые не являются пустыми. Когда вы добавляете элемент в список на листе Sheet2, COUNTA (Sheet2! $ A: $ A) увеличивается. В результате диапазон, возвращаемый функцией OFFSET, расширяется, и раскрывающийся список обновляется.

5. Щелкните OK.

6. На втором листе просто добавьте новый элемент в конец списка.

Результат:

Удалить раскрывающийся список

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

1. Выберите ячейку в раскрывающемся списке.

2. На вкладке «Данные» в группе «Работа с данными» щелкните «Проверка данных».

Откроется диалоговое окно «Проверка данных».

3. Щелкните Очистить все.

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

4. Щелкните OK.

Зависимые раскрывающиеся списки

Хотите узнать больше о раскрывающихся списках в Excel? Узнайте, как создавать зависимые раскрывающиеся списки.

1. Например, если пользователь выбирает Пиццу из первого раскрывающегося списка.

2. Второй раскрывающийся список содержит пункты «Пицца».

3. Но если пользователь выбирает китайский язык в первом раскрывающемся списке, второй раскрывающийся список содержит китайские блюда.

Стол Magic

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

1. На втором листе выберите элемент списка.

2. На вкладке Вставка в группе Таблицы щелкните Таблица.

3. Excel автоматически выбирает данные за вас. Щелкните ОК.

4. Если вы выберете список, Excel покажет структурированную ссылку.

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

Объяснение: функция ДВССЫЛ в Excel преобразует текстовую строку в действительную ссылку.

6.На втором листе просто добавьте новый элемент в конец списка.

Результат:

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

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

Примечание: эта функция динамического массива, введенная в ячейку F1, заполняет несколько ячеек. Вот Это Да! Такое поведение в Excel 365 называется разливом.

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

Объяснение: всегда используйте первую ячейку (F1) и символ решетки для обозначения диапазона разлива.

Результат:

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

Как быстро перемещаться по большим спискам

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

Так что было бы неплохо, если бы был способ просто прыгнуть в конец или в начало этого списка? Что ж, есть. Вам просто нужно выучить сочетание клавиш.

Давайте посмотрим.

Итак, мы находимся в Excel, и если я начну прокручивать, вы увидите, что это большой список.Фактически, этот список превышает 30 000 строк. Поэтому для меня просто непрактично прокручивать список вверх или вниз вручную.

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

Теперь также можно нажать кнопку управления вправо и стрелку и перейти к правому краю списка, а затем нажать стрелку влево и вернуться к левому краю.

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

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

.

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

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