Как импортировать списки в excel

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

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

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

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

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

Введите Sun (для воскресенья) в ячейку B2 . Затем поместите курсор на правый нижний угол ячейки B2, пока он не превратится в знак плюс, и перетащите вправо на ячейку H2 .

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

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

Как создать пользовательский список в Excel

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

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

Есть три способа создать свой собственный список.

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

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

Возьмём такой жизненный и несложный пример — перенесем из текстового файла (с расширением .txt) в Excel список сотрудников, в котором перечислены фамилия, имя , отчество, год рождения и должность.

Допустим, вот наши герои:

Иванов Иван Иванович 1978 инспектор
Сидоров Петр Сидорович 1970 старший инспектор
Петров Василий Самуилович 1965 самый старший инспектор

Больше трех лень набивать 🙂 , но и их хватит.

Теперь определимся, какая нам нужна в Excel таблица? Пусть она имеет такие столбцы:
Фамилия, Имя, Отчество, Год рождения, Должность.

Все у нас имеется, но тут есть один нюанс в представлении должности. Для каждого сотрудника должность имеет разный размер – 1, 2 и 3 слова. Как же Excel указать, где начинается и заканчивается информация, заносимая по столбцам?

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

Структурируем наш список:

Фамилия/Имя/Отчество/Год рождения/Должность
Иванов/Иван/Иванович/1978/инспектор
Сидоров/Петр/Сидорович/1970/старший инспектор
Петров/Василий/Самуилович/1965/самый старший инспектор

и сохраняем в текстовом документе c расширением .txt . Теперь такой список замечательно перенесется в Excel.

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

В открывшемся окне указываем на наш текстовый файл, из которого будем импортировать данные. После этого откроется Мастер импорта на первом шаге. Тут нужно указать структуру наших данных – “с разделителями” (которым у нас выступает наклонная «/»). После этого нажимаем кнопку Далее.

На следующем шаге мастера нужно указать символ-разделитель. Тут приведены некоторые варианты, но мы выбираем “другой” и в окошке вводим наш символ – наклонную. Сразу же в нижней области (с нашим списком) появляются вертикальные линии (столбцы), по которым можно проверить правильность разделения данных. Нажимаем кнопку Далее.

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

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

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

Теперь можно навести марафет, указав выравнивание и задав границы.

Учебник: импорт данных в Excel и создание модели данных

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

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

Примечание: В этой статье описаны модели данных в Excel 2013. Однако те же функции моделирования данных и Power PIVOT, представленные в Excel 2013, также применимы к Excel 2016.

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

Учебники этой серии

Импорт данных в Excel 2013 и создание модели данных

В этом учебнике вы начнете работу с пустой книги Excel.

Разделы учебника

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

В этой серии учебников используются данные, описывающие спортивных medals, страны размещения и различные спортивных спортивные мероприятия. Мы рекомендуем вам пройти каждый из этих учебников по порядку. Учебники также используют Excel 2013 с Power Pivot. Для получения дополнительных сведений о Excel 2013 щелкните здесь. Для получения инструкций по включению Power Pivot щелкните здесь.

Импорт данных из базы данных

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

Сначала загрузим данные из Интернета. Эти данные об олимпийских медалях являются базой данных Microsoft Access.

Щелкните следующие ссылки, чтобы скачать файлы, используемые во время этого ряда учебников. Скачайте каждый из четырех файлов в нужное место, например загружаемЫе файлы или Мои документы, или новую созданную папку.
_Гт_ олимпикмедалс. accdb Access

Откройте пустую книгу в Excel 2013.

Щелкните данные _Гт_ получить внешние данные _Гт_ из Access. Лента настраивается динамически в зависимости от ширины книги, поэтому команды на ленте могут немного отличаться от следующих экранов. На первом экране отображается лента, если книга является широкой, а на втором — книга, размер которой был изменен, чтобы занимать только часть экрана.

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

Появится окно «Импорт данных».

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

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

После импорта данных создается сводная таблица с использованием импортированных таблиц.

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

Просмотр данных в сводной таблице

Просматривать импортированные данные удобнее всего с помощью сводной таблицы. В сводной таблице можно перетаскивать поля (похожие на столбцы в Excel) из таблиц (например, таблиц, импортированных из базы данных Access) в разные области, настраивая представление данных. Сводная таблица содержит четыре области: ФИЛЬТРЫ, СТОЛБЦЫ, СТРОКИ и ЗНАЧЕНИЯ.

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

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

В полях сводной таблицы разверните таблицу medals , щелкнув стрелку рядом с ней. Найдите поле Нок_каунтрирегион в развернутой таблице medals и перетащите его в область столбцы. В NOC используется National спортивных комитетов, который является организационным подразделением для страны или региона.

Затем перетащите виды спорта из таблицы Disciplines в область СТРОКИ.

Давайте отфильтруем дисциплины, чтобы отображались только пять видов спорта: стрельба из лука (Archery), прыжки в воду (Diving), фехтование (Fencing), фигурное катание (Figure Skating) и конькобежный спорт (Speed Skating). Это можно сделать в области Поля сводной таблицы или в фильтре Метки строк в самой сводной таблице.

Щелкните в любом месте сводной таблицы, чтобы убедиться, что сводная таблица Excel выбрана. В списке Поля сводной таблицы, где развернута таблица Disciplines, наведите указатель на поле Discipline, и в его правой части появится стрелка раскрывающегося списка. Щелкните эту стрелку, нажмите кнопку (Выбрать все), чтобы снять отметку со всех выбранных параметров, а затем прокрутите вниз и выберите пункты Archery, Diving, Fencing, Figure Skating и Speed Skating. Нажмите кнопку ОК.

Либо щелкните в разделе сводной таблицы Метки строк стрелку раскрывающегося списка рядом с полем Метки строк, нажмите кнопку (Выбрать все), чтобы снять отметку со всех выбранных параметров, а затем прокрутите вниз и выберите пункты Archery, Diving, Fencing, Figure Skating и Speed Skating. Нажмите кнопку ОК.

В разделе Поля сводной таблицы перетащите поле Medal из таблицы Medals в область ЗНАЧЕНИЯ. Поскольку значения должны быть числовыми, Excel автоматически изменит поле Medal на Count of Medal.

В таблице Medals снова выберите поле Medal и перетащите его в область ФИЛЬТРЫ.

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

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

Выберите Фильтры по значению, а затем — Больше.

Введите 90 в последнем поле (справа). Нажмите кнопку ОК.

Сводная таблица будет иметь следующий вид:

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

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

Импорт данных из таблицы

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

Начнем с создания пустого листа, а затем импортируем данные из книги Excel.

Вставьте новый лист Excel и назовите его Sports.

Перейдите к папке, в которой содержатся загруженные файлы образцов данных, и откройте файл OlympicSports.xlsx.

Выберите и скопируйте данные на листе Sheet1. При выборе ячейки с данными, например, ячейки А1, можно нажать клавиши Ctrl + A, чтобы выбрать все смежные данные. Закройте книгу OlympicSports.xlsx.

На листе Sports поместите курсор в ячейку А1 и вставьте данные.

Выделив данные, нажмите клавиши CTRL + T, чтобы отформатировать данные как таблицу. Вы также можете отформатировать данные в виде таблицы на ленте, выбрав в _Гт_ формат таблицы. Так как у данных есть заголовки, в появившемся окне Создание таблицы выберите пункт таблица с заголовками, как показано ниже.

Форматирование данных в виде таблицы имеет много преимуществ. Можно назначить имя таблице, что упрощает ее определение. Кроме того, вы можете устанавливать связи между таблицами, позволяя исследовать и анализировать в сводных таблицах, Power Pivot и в Power View.

ПриСвойте таблице имя. В разделе Работа с таблицаМи _Гт_ свойства _ГТ_ макета найдите поле имя таблицы и введите Спорт. Книга будет выглядеть так, как показано на следующем экране.

Импорт данных с помощью копирования и вставки

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

Вставьте новый лист Excel и назовите его Hosts.

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

Импорт из Excel

Пусть исходные данные находятся в таблице Excel:

Рис. 1. Файл данных Excel

Импортировать их в STATISTICA очень легко.

В системе STATISTICA нажмите Открыть в меню Файл. Программа предложит на выбор 3 варианта: Импортировать все листы в Рабочую книгу, Импортировать выбранный лист в Таблицу данных или Открыть как Рабочую книгу Excel.

Рис. 2. Открытие файла

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

Рис. 3. Окно Выберите лист

Например, выбреем Лист 1 и нажмём OK.

Далее вам необходимо:

указать диапазон значений во внешнем файле данных, который будет импортирован;

решить, нужно ли импортировать имена наблюдений и переменных;

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

Рис. 4. Окно Открыть файл Excel

Выберите требуемые для импорта значения таблицы и нажмите кнопку OK.

Рис. 5. Таблица данных в формате STATISTICA

Теперь Ваши данные импортированы в программу STATISTICA.

Вы также можете работать в Excel «внутри» STATISTICA. Для этого в окне Открытие файла, изображенного на рисунке 2, нажмите Открыть как Рабочую книгу Excel.

Рис. 6. Рабочая книга Excel внутри STATISTICA

Теперь Вы можете совмещать привычную среду Excel и мощные возможности STATISTICA.

Импорт данных в Excel 2007

Как импортировать в Excel файл в формате txt?

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

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

Программа автоматически запустит Мастер текстов (импорт).

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

Программа автоматически запустит Мастер текстов (импорт).

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

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

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

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

На втором этапе Мастера импорта текстов у вас может быть два варианта действий.

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

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

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

На третьем этапе Мастер импорта текста предлагает установить формат колонок. По умолчанию стоит Общий.

Нажимаем кнопку Готово.

У меня файл в формате csv, открывается в Эксель в одну строчку, как его преобразовать в таблицу?

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

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

Сразу же откроется диалоговое окно Мастер текстов (импорт). На первом шаге в поле формат данных укажите с разделителями.

На втором шаге в поле Символом разделителем является отметьте — запятая (точка с запятой).

На третьем шаге, если есть необходимость, укажите формат столбцов.

После нажатия кнопки Готово файл csv будет корректно импортирован в Excel и преобразован в таблицу.

У меня есть таблица в формате txt со всеми линиями границ, как мне импортировать ее в Excel, чтобы этих границ не было?

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

Во время импорта таблицы в Мастер текстов (импорт) на первом шаге в поле Укажите формат данных отметьте с разделителями.

На втором шаге в поле Символом-разделителем является отметьте пункт другой и установив курсор в поле для ввода символа нажмите комбинацию клавиш Ctrl+V. Таким образом, вы выберите в качестве разделителя линию границы таблицы.

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

Для того, что бы дробные числа с разделителем в виде точки отображались корректно в Excel, во время импорта файла на третьем шаге Мастер текстов (импорт) в окне Образец разбора данных выделите столбик, который содержит дробные числа, и в поле Формат данных столбца выберите текстовый.

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

Теперь, с помощью меню Формат ячеек группы Ячейки вкладки Главная, измените формат столбца с Текстовый на Числовой.

Можно ли в Excel подключиться к данным базы Access что бы они автоматически обновлялись?

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

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

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

У вас откроется еще одно диалоговое окно — Импорт данных. Здесь вы можете выбрать способ отображения данных в виде:

• Таблицы
• Отчета сводной таблицы
• Сводной диаграммы и отчета сводной таблицы

Здесь же можете указать Куда следует поместить данные?

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

На вкладке Использование выберите, с какой частотой должны обновляться данные:

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

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

В начало страницы

В начало страницы

Импорт csv и txt файлов в Excel c помощью Power Query

Одним из популярных источников для импорта в Power Query являются файлы типа .csv или .txt. Традиционные методы импорта и объединения текстовых файлов довольно неуклюжи и отнимают много времени. Однако применение Power Query многократно облегчает решение проблемы. Пусть у нас есть отчет о продажах за январь в формате .csv. Задача в том, чтобы импортировать его в Excel.

Создадим запрос Power Query (в Excel 2016) Данные – Скачать и преобразовать – Создать запрос – Из файла – Из CSV (либо Из текста, если импортируете из .txt).

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

Power Query распознал и разделил данные по столбцам. Также автоматически выполнены шаги:

• Первая строка повышена в заголовки

• Изменен тип для каждого столбца

Если обработки не требуется, то результат можно выгружать в книгу Эксель Главная – Закрыть – Закрыть и загрузить.

На новом листе создается Таблица Excel.

Теперь можно проводить анализ внутри Excel. К этому мы еще вернемся. Прошел месяц, и у нас появился такой же файл .csv за февраль. Возникает вопрос, как объединить данные за два месяца? В Power Query для этого есть специальные инструменты.

Уберем пока из листа Excel данные за январь. Для этого в панели запросов через правую клавиши выбираем Загрузить в…, где меняем настройки выгрузки на Только соединение.

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

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

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

В Power Query есть два типа объединения запросов: Добавление (Append) и Объединение (Merge). Нас интересует добавление, т.к. таблицы должны быть сложены вместе (одна под другой). Объединение нужно для слияния запросов по ключевому полю, но об этом в другой раз.
Сейчас заходим Главная – Скачать и преобразовать – Создать запрос – Объединить запросы – Добавить.

Далее нужно выбрать добавляемые запросы.

Следует понимать две вещи.

• Добавлять можно только запросы (а не Таблицы Excel)

• Запрос в верхнем поле будет первым сверху

Добавить можно было бы и больше запросов, выбрав в верхней строке Три таблицы или больше, но у нас только два. Нажимам ОК. Создается объединенный запрос под названием Append1.

Изменим название на Отчет о продажах и выгрузим данные в Excel (Главная – Закрыть – Закрыть и загрузить). Итого получим три запроса.

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

Через месяц появился файл за март, и вы также решили добавить его в общий отчет и сводную таблицу. Создаем запрос к новому файлу Март.csv. Затем его нужно добавить в запрос Отчет о продажах. Однако, если повторить все шаги с добавлением, то будет создан новый общий запрос, который уже не имеет смысла, т.к. сводная таблица строится по данным из запроса Отчет о продажах. Поэтому нужно зайти в редактирование запроса Отчет о продажах и уже там добавить новый запрос за март. В редакторе Power Query выбираем Главная – Комбинировать – Добавить запросы. В окне добавления теперь только одно поле, т.к. первый запрос уже определен. Нужно выбрать лишь добавляемый.

Нажимаем ОК и снова выгружаем обновленные данные в Excel (Главная – Закрыть – Закрыть и загрузить). Теперь в Таблице Excel находятся данные за три месяца. Нужно только обновить сводную таблицу.

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

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

• добавление его в объединяющий запрос Отчет о продажах

• выгрузка в Excel

• обновление сводной таблицы.

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

Импорт из папки

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

Создадим папку Данные о продажах csv и поместим в нее сразу три файла за январь, февраль и март. Сделаем запрос к этой папке Данные – Скачать и преобразовать – Создать запрос – Из папки. В следующем окне указываем путь. Адрес лучше заранее скопировать и затем вставить, чем искать в проводнике. В редакторе Power Query мы увидим такую таблицу.

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

Следующий шаг не является обязательным, но он позволит избежать некоторых возможных проблем в будущем. Нужно понимать, что все содержимое указанной папки будет извлечено с помощью запроса Power Query. И если туда попадет какой-нибудь, например, файл Excel, то запрос «поломается» и выдаст ошибку. Поэтому опытные пользователи создают «защиту», чтобы файлы с другим расширением отфильтровывались.

Идея в том, чтобы в столбце Extension поставить фильтр на расширение .csv. Чтобы случайно не отфильтровать файлы .CSV, сделаем все буквы для столбца Extension маленькими. Для этого выделяем столбец, далее через правую кнопку мыши Преобразование – нижний регистр. Переходим к установке фильтра. Если в выпадающем списке фильтра поставить переключатель на значение .csv, то у нас ничего не получится, т.к. при единственном типе файлов будет автоматически выделен пункт Выбрать все. Поэтому выбираем Текстовые фильтры – Равно… и указываем .csv (обязательно с точкой впереди).

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

Приступим к извлечению данных. Содержимое файлов скрыто в колонке Content за значением Binary.

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

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

Это кнопка загрузки двоичного (бинарного) файла. Жмем. И о чудо! Содержимое всех трех файлов один за другим выгружается в единую таблицу.

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

• Удалим последний шаг Измененный тип

• Преобразование – Использовать первую строку в качестве заголовков

• Правой кнопкой мыши по полю Дата – Тип изменения – Дата

• Удерживая Shift, выделяем два столбца Наименование и Менеджер, затем через правую клавишу мыши Тип изменения – Текст

• Через Shift выделяем остальные столбцы Цена, Стоимость, Комиссия – правая клавиши мыши – Тип изменения – Десятичное число

• Правой кнопкой мыши по полю Дата – Удалить ошибки

• Главная – Закрыть – Закрыть и загрузить

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

Сводная таблица построена по 116 строкам. Таким же образом можно было бы объединить и 10 файлов с сотнями тысяч строк.

Добавление в запрос новых файлов и обновление сводной таблицы

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

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

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

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

Офисное пространство Офисное пространство

официальный блог о Microsoft Office

Импорт и экспорт данных в Excel 2010

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

Статья будет про импорт и экспорт данных в Excel 2010.

Импорт данных из файлов других форматов

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

1. Чтобы импортировать данные из текстового файла, на вкладке Данные в разделе Получить внешние данные щелкните на кнопке Из текста (рис.2).

2. В результате отобра­зиться диалоговое окно Импорт текстового файла (рис.3).

3. С помощью этого диалогового окна (рис.3) можно перейти в папку, со­держащую файл, который нужно импортировать. Двойной щел­чок на файле или по кнопке Импорт запускает Мастер импорта текста (рис.4).

4. Первая страница мастера позволяет указать формат данных: с разделителем или с фиксированной шириной. Фиксированная ширина означает, что поля имеют заданную ширину. Необходимо также указать место начала импорта и Формат файла, например, Кириллица. Если щелк­нуть на кнопке Далее, мастер примет заданный по умолча­нию параметр и перейдет к следующей странице (рис.5).

5. На второй странице мастера (рис.5) можно выбрать разделитель (в данном случае Ехсеl обнаружил в файле символ табуляции и вы­делил соответствующий флажок) и просмотреть образец разбора данных. При предварительном просмотре в качестве символа разделения была использована запятая (рис.4), поэтому необходимо вы­делить флажком символ-разделителя Запятая. Щелкнув на кнопке Далее, можно перейти к пос­ледней странице мастера (рис.6).

6. 3-я страница позволяет указать формат данных для каждого столбца. Поскольку числовые форматы и экспресс-стили можно присвоить после создания сводной таблицы, можно щелкнуть на кнопке Готово, чтобы указать место куда импортировать данные в рабочий лист и настройке свойств внешнего диапазона (рис.7).

7. Нажав кнопку ОК завершается процесс импорта данных и они появляются на рабочем столе (рис.8).

Аналогично можно импортировать эти данные в Ехсеl 2010 и из других источников (рис.9).

Экспорт данных в файл

Чтобы осуществить Экспорт данных в файл необходимо перейти на вкладку Файл и воспользоваться командой Сохранить и отправить.

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

1. Выполните команду: Файл ðСохранить и отправить ð Изменить тип файла ð Другие типы файлов ð Текстовой формат с разделителями табуляции (рис.11).

2. В диалоговом окне Сохранение документа нажмите кнопку Сохранить (рис.11). Нажмите ОК, чтобы подтвердить, что желаете сохранить текущий лист (рис.12).

3. В результате будет создан текстовой файл, в котором данные книги будет разделены знаками табуляции (рис.13).

Аналогично можно импортировать эти данные из Ехсеl 2010 и в другие источники, воспользоваться командой Сохранить как, например, как MHTML-документ (рис.14).

Выводы и рекомендации

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

2. Экспорт данных в файл необходимо осуществлять с вкладки Файл, воспользоваться командами Сохранить и отправить или Сохранить как.

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

​Смотрите также​ не запоминающиеся и​ что все прочли​Intersect(ActiveSheet.UsedRange, [C:C]).Value =​ диапазон ячеек.​ — пишите, будем​ среди столбца из​ листа 2-а должна​ появляется выпадающий список​ же правила проверки​

Сведения о настраиваемых списках

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

Сравнение встроенных и настраиваемых списков

​Чтобы создать настраиваемый список​Для сортировки или заполнения​ не информативные, к​ и вникли, сегодня​

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

​ обязательно проверю все,​End Sub​ на​ вариант.​ которые есть в​ ячейки листа 1.​ Допустим, в списке​ для активной ячейки.​ является использование функции​ недостатка – разместим​и вызовем Проверку​в поле Источник введите​

​ikki​ пункте три и​Т.е. суть проста​ значений есть «Гамма1″​​Примечание​​ ДВССЫЛ(). На листе​

​ перечень элементов выпадающего​​ данных.​ =Сотрудники;​Создание списка последовательных дат​Примечание:​В​ настраиваемые списки. В​ ли сделать выпадающий​ получилось​Вот эти два​ [C:C]).Value​

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

​: описался-таки :)​ заменить описания​ необходимо задать определенное​ и «Гумма1». Если​:​ Пример, выделяем диапазон​ списка на другом​Если в поле Источник​нажмите ОК.​

Введение значений напрямую

​При заполнении ячеек данными​ Настраиваемый список можно создать​Excel 2010​

1. ​ Excel есть встроенные​​ список для ячеек​​Ramboo​ макроса вполне рабочие.​​sanantoni​​сорри.​​Подскажите если не​​ наименование изделия, чтобы​​ я напишу в​​Если выпадающий список​​ ячеек, которые будут​​ листе.​​ указать через точку​​Теперь при выделении любой​

2. ​ иногда необходимо ограничить​​ только на основе​​и более поздних​​ списки дней недели​ ​ в котором будет​​: Приветствую вас уважаемые​​Но как только​​: возможно потому что​​for each r​​ лень такую формулу,​ в соответствии с​​ ячейке с результатом​​ содержит более 25-30​​ содержать выпадающий список,​

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

   ​ я подставляю туда​ каждый раз нужно​

4. ​ in activesheet.usedrange.rows​ заранее спс!!!!!!!!​​ этим именем менялась​​ букву «Г», то​

   

   ​ значений, то работать​​ вызываем Проверку данных,​​ как и Условного​ измерения шт;кг;кв.м;куб.м, то​

5. ​А2:А5​​ списком значений. Например,​​ текст, числа, даты​

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

​Файл​

1. ​ но вы можете​ при выборе нужного​Есть простой пример,​With Workbooks(«коды.xlsx»).Sheets(«Лист1″)​ копировать в новую​if r(1,2).value>»» then​Guest​ формула вычисления. При​

2. ​ в выпадающем списке​ с ним становится​ в Источнике указываем​​ форматирования) нельзя впрямую​​ выбор будет ограничен​​, справа от ячейки​​ при заполнении ведомости​​ и время. На​​ >​

3. ​ создавать и свои​​ названия проставлять в​​ нужно по схеме​avTableArr = Intersect(.UsedRange,​

   ​ книгу недопустимо имя​set x=activesheet.[ae:af].find(what:=r(1,2).value, lookin:=xlvalues,​:​ этом параметры необходимые​ смогу выбрать оба​ неудобно. Выпадающий список​ =ДВССЫЛ(«список!A1:A4»).​

4. ​ указать ссылку на​​ этими четырьмя значениями.​​ будет появляться кнопка​

​ ввод фамилий сотрудников​​ основе формата, например​Параметры​ настраиваемые списки.​ эту же ячейку​ при выборе например​ .[A:B]).Value​ activesheet? имена этих​ lookat:=xlwhole, matchcase:=true)​sanantoni​ для формулы листа​

Удаление настраиваемого списка

​ указанных выше варианта.​

1. ​ одновременно отображает только​Недостаток​ диапазоны другого листа​

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

Хранение настраиваемых списков

​ соответствующий названию код.​ Сидорову Сидру Сидоровичу​End With​ книг длинны и​if not x​: попробовал, не получается,​ 1 применялись с​ Но если я​ 8 элементов, а​: при переименовании листа​ (см. Файл примера):​ Выделим ячейку​ на которую можно​ заменить выбором из​ или цвета шрифта,​Дополнительно​ собой настраиваемые списки,​

​ Ехсцель 2010​ подставлялись его таб​заместо​ сильно отличаются​ is nothing then​ очень срочно надо​​ листа 1.​​ напишу «Гу», то​​ чтобы увидеть остальные,​​ – формула перестает​Пусть ячейки, которые должны​B1​​ выбрать необходимую фамилию.​​ определенного заранее списка​​ создать настраиваемый список​​ >​ полезно ознакомиться с​Rdg2004​ номер и ФИО​​avTableArr = Intersect(ActiveSheet.UsedRange,​​sanantoni​

​ r(1,3).value=x(1,2).value​ ((​Также прошу учесть,​ уже только один,​ нужно пользоваться полосой​​ работать. Как это​​ содержать Выпадающий список,​​. При выделении ячейки​​Проверку данных можно настроить​ (табеля).​​ нельзя.​​Общие​​ принципами их работы​​: может через if?​​ в другие ячейки,​​ [AE:AF]).Value​: обещаю выучить весь​​end if​​Z​ что изделий может​ но мне все-равно​​ прокрутки, что не​​ можно частично обойти​

См. также

• ​ размещены на листе​

support.office.com

Ввод данных из списка значений. Часть 1: Выпадающий список в MS EXCEL

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

​next​: Тема — «Подстановка​ быть множество и​ надо открывать выпадающий​ всегда удобно.​ см. в статье​ Пример,​ появляется квадратная кнопка​ вводе фамилий не​ ячеек является выбор​По приведенным выше инструкциям​Изменить списки​ компьютере.​ = тому то​ вас задачка​ikki​

​ макрос )) который​sanantoni​ значений из списка!»,​ формула должна позволять​ список, чтобы выбрать​В EXCEL не предусмотрена​ Определяем имя листа.​а диапазон с перечнем​ со стрелкой для​ из списка появлялось​

​ значений из заранее​ откройте диалоговое окно​

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

• ​: какие? неописуемые?​​ не ругается​​: runtime error 1004​
• ​ в посте -​​ копировать все значения​​ его. Можно ли​ регулировка размера шрифта​Ввод элементов списка в​ элементов разместим на​​ выбора элементов из​​ окно с описанием​​ определенного списка в​​ «Списки».​В​ ниже встроенные списки​ то? ну на​

​ про всякие ВПР​​Guest​
​ikki​If r(1, 2).Value​ «заменить описания», а​ для последующего изменения​ «дописать» как-то этот​ Выпадающего списка. При​ диапазон ячеек, находящегося​ другом листе (на​

​ выпадающего списка.​ ошибки (для этого​ MS EXCEL. Предположим,​

• ​Выделите список, который нужно​Excel 2007​ дней недели и​​ словах так​​ и диапазоны ячеек​
• ​: На самом деле​: не жульничайте :)​ > «» Then​
• ​ что на самом​
• ​ переменных.​ файл, чтобы если​

• ​ большом количестве элементов​

​ в другой книге​ листе Список в​​Недостатки​​ во вкладке Сообщение​ что в таблицу​ удалить, в поле​нажмите​ месяцев года.​

​k61​ тем больше запутываюсь​ ваша ирония была​вас просили учить​у меня 10​ деле? Формула может​Прикладываю исходный документ.​ под критерий подходит​ имеет смысл сортировать​

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

excel2.ru

Выпадающий список в MS EXCEL на основе Проверки данных

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

​ возможно выполнить средствами​ Excel его автоматически​ использовать дополнительную классификацию​

​ списка в другую​ элементы которого расположены​ (например, удалив строку​Недостатком​ не ошибиться с​Удалить​

​Параметры Excel​ Пт, Сб, Вс​ списке.​: Для табельного номера.​ профессиональна. Очень и​sanantoni​: application defined or​

​ макрос надо писать​ Excel, помогите пожалуйста​

А. Простейший выпадающий список — ввод элементов списка непосредственно в поле Источник

​ предлагал?​ элементов (т.е. один​ книгу (например, в​ на другом листе,​ или столбец, содержащие​этого решения является​

​ написанием фамилий можно​​.​​ >​Понедельник, Вторник, Среда, Четверг,​vikttur​ Код =ЕСЛИОШИБКА(ВПР(C6;I6:K7;3;0);»») Для​​ очень выручили. Я​​: ikki​ object defined error​

​ или… искать… ;)​ выполнить по средствам​Спасибо​ выпадающий список разбить​ книгу Источник.xlsx), то​ можно использовать два​

​ ячейку​ то, что у​​ предварительно создать список​​Настраиваемые списки добавляются в​Популярные​ Пятница, Суббота, Воскресенье​: Пример с элементом​ «работника», меняем №​ действительно торопился и​

​Число сообщений: 546​​ikki​-90314-​ макросов. Пользователь, я​Казанский​ на 2 и​​ нужно сделать следующее:​​ подхода. Один основан​B1​ пользователя есть потенциальная​ всех сотрудников организации,​ реестр компьютера, чтобы​
​>​​янв, фев, мар, апр,​

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

​ управления. Элемент можно​ столбца 3,на 2.​ некоторые моменты упускал,​15.05.2011, 20:25​: оказывается, трюк не​Guest​ Excel’я и макросов​:​

​ более).​в книге Источник.xlsx создайте​ на использовании Именованного​​); не удобно вводить​​ возможность ввести в​ а заполнение ведомости​

​ их можно было​​Основные параметры работы с​ май, июн, июл,​ разместить над ячейкой,​ Диапазон ставим свой​ неомера ячеек ещё​
​оказывается, трюк не​​ проходит. нужно всё-таки​: что именно не​ слабый, поэтому опишите​sashok_alex​Например, чтобы эффективно работать​ необходимый перечень элементов;​ диапазона, другой –​ большое количество элементов.​​ ведомость повторяющиеся фамилии.​​ свести к выбору​ использовать в других​ Excel​ авг, сен, окт,​ можно отображать в​ рабочий.​ что то. В​ проходит. нужно всё-таки​ вот так:​

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

​ ноя, дек​ нем только наименование​AlexM​ общем посидев до​ вот так:​

B. Ввод элементов списка в диапазон (на любом листе)

​Sub t()​ именно пробовали?​китин​ все, но потом​ насчитывающем более 300​ ячеек содержащему перечень​

​Используем именованный диапазон​ маленьких (3-5 значений)​ контролировать появление повторяющихся​ списка.​

​ используете настраиваемый список​Изменить списки​Январь, Февраль, Март, Апрель,​ и т.д. Играйтесь.​: ЕСЛИОШИБКА() не нужна​

​ часов трех утра​Sub t()​For Each r​sanantoni​: так?​ не хочется переделывать.​ сотрудников, то его​ элементов присвойте Имя,​

​Создадим Именованный диапазон Список_элементов,​
​ неизменных списков.​ фамилий, можно использовать​Инструмент Проверка данных (Данные/​​ при сортировке данных,​​.​
​ Май, Июнь, Июль,​

• ​vikttur​​ так как в​​ я наконец все​
• ​For Each r​ In ActiveSheet.UsedRange.Rows​
• ​: что именно не​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС(‘2’!$C$3:$I$7;ПОИСКПОЗ(‘1’!$B3;Изделия;0);ПОИСКПОЗ(‘1′!C$2;’2’!$C$2:$I$2;0))​ Поэтому приложите файл-пример​

​ следует сначала отсортировать​ например СписокВнеш;​ содержащий перечень элементов​Преимущество​

• ​ идеи из статьи​
• ​ Работа с данными/​ он также сохраняется​Выберите в поле​

​ Август, Сентябрь, Октябрь,​
​: Панель «Элементы управления»,​ выпадающем списке не​ исправил и ликованию​ In ActiveSheet.UsedRange.Rows​If r.Cells(1, 2).Value​​ получается и как​​шураООТиЗ​ с реальной структурой​ в алфавитном порядке.​откройте книгу, в которой​

​ выпадающего списка (ячейки​: быстрота создания списка.​ Ввод данных из​ Проверка данных) с​ вместе с книгой,​Списки​ Ноябрь, Декабрь​ войти в Режим_конструктора,​

​ получится ввести значение,​ не было предела.​If r.Cells(1, 2).Value​​ > «» Then​​ именно пробовали?{/post}{/quote}​: тоже самое с​ данных — расположение​ Затем создать выпадающий​ предполагается разместить ячейки​

​A1:A4​

​Элементы для выпадающего списка​ списка значений. Часть​ условием проверки Список,​ поэтому его можно​пункт​Примечание:​ Свойства, выделить элемент​ отсутствующее в источнике​ Ещё раз -​ > «» Then​Set x =​

​Подставил так как​​ ВПР​ списка значений, расположение​ список, содержащий буквы​ с выпадающим списком;​на листе Список).​ можно разместить в​

​ 2. Выпадающий список​ как раз предназначен​ использовать на других​

​НОВЫЙ СПИСОК​ Изменить или удалить встроенный​ и поиздеваться над​Ramboo​ вы асы!!! Спасибо!!!!!!!!!!​Set x =​

• ​ ActiveSheet.[ae:af].Find(what:=r.Cells(1, 2).Value, LookIn:=xlValues,​ написано по ссылке,​
• ​Nic70y​ столбца, в котором​ алфавита. Второй выпадающий​выделите нужный диапазон ячеек,​
• ​Для этого:​ диапазоне на листе​ с контролем дублирования.​
• ​ для решения нашей​ компьютерах, в том​и введите данные​ список невозможно.​

​ ним :)​: AleksSid спасибо!​sanantoni​ ActiveSheet.[ae:af].Find(what:=r.Cells(1, 2).Value, LookIn:=xlValues,​ lookat:=xlWhole, MatchCase:=True)​ но вместо этого​: китин, шураООТиЗ и​ этот выпадающий список​ список должен содержать​ вызовите инструмент Проверка​выделяем​ EXCEL, а затем​При заполнении ячеек данными,​ задачи: с помощью​

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

​: Можно ли чтобы​
​ lookat:=xlWhole, MatchCase:=True)​If Not x​ у меня получаются​ что это дает?​ должен быть (это​ только те фамилии,​ данных, в поле​А1:А4​ в поле Источник​ часто необходимо ограничить​ него можно создать​ с службы Excel,​Элементы списка​ свой настраиваемый список​: довести до ума​ » я не​ замененные данные сохраняли​If Not x​ Is Nothing Then​ пустые ячейки. А​ТС нужна формула​ же не одна​ которые начинаются с​ Источник укажите =ДВССЫЛ(«[Источник.xlsx]лист1!СписокВнеш»);​,​ инструмента Проверки данных​

​ возможность ввода определенным​​ Выпадающий (раскрывающийся) список​
​ для которых может​, начиная с первого​ и использовать его​ несложно.​ понял) как привязать​ исходное форматирование цвета?!​ Is Nothing Then​ r.Cells(1, 3).Value =​ надо чтобы осталось​ с листа 2,​ ячейка, правда?), потому​

​ буквы, выбранной первым​При работе с перечнем​нажимаем Формулы/ Определенные имена/​ указать ссылку на​ списком значений. Например,​ (см. Файл примера).​ быть опубликована ваша​ элемента.​ для сортировки или​Guest​ то​

​можно ли чтобы​ r.Cells(1, 3).Value =​ x(1, 2).Value​ старое значение если​ а не ее​ что Вы вряд​ списком. Для решения​ элементов, расположенным в​ Присвоить имя​ этот диапазон.​ имеется ячейка, куда​Для удобства создадим Именованный​ книга.​После ввода каждого элемента​ заполнения. Например, чтобы​: дело в том,​что такое 3​ остались все цвета​

excel2.ru

Поиск и подстановка значения из списка по введенным первым буквам

​ x(1, 2).Value​​End If​
​ ничего не меняется,​ значение.​ ли сможете переделать​ такой задачи может​ другой книге, файл​в поле Имя вводим​Предположим, что элементы списка​ пользователь должен внести​ диапазон:​Однако, открыв книгу на​ нажимайте клавишу ВВОД.​
​ отсортировать или заполнить​ что сформированый файл​
​ не понял в​

​ после того как​​End If​​Next​​ или новое если​это в раздел​ пример с одной​ быть использована структура​

​ Источник.xlsx должен быть​​ Список_элементов, в поле​​ шт;кг;кв.м;куб.м введены в​​ название департамента, указав​создайте список фамилий сотрудников,​ другом компьютере или​Завершив создание списка, нажмите​ значения по приведенным​ надо передавть в​ формуле, номер столбца​ с помощью макроса​
​Next​End Sub​ соответствует коду​ vba надо.​ ячейкой для реального​ Связанный список или​ открыт и находиться​ Область выбираем Книга;​ ячейки диапазона​ где он работает.​ например в диапазоне​ сервере, вы не​ кнопку​ ниже спискам, нужен​ другое подразделение, в​ где что это?​ они перескочили в​End Sub​…но вообще-то странно…​ikki​такой вопрос уже​ файла.​ Вложенный связанный список.​ в той же​Теперь на листе Пример,​A1:A4​ Логично, предварительно создать​
​D1:D10​

​ найдете настраиваемый список,​​Добавить​​ настраиваемый список, так​​ котором формируют сводную​ нужно чтоб при​ новую книгу?​…но вообще-то странно…​ikki​: только макрос (как​ решался, правда не​maryanaa​sashok_alex​ папке, иначе необходимо​ выделим диапазон ячеек,​, тогда поле Источник​ список департаментов организации​;​ сохраненный в файле​.​ как соответствующего естественного​

​ таблицу тупо копируя​​ выборе «сидорова» табельный​
​Hugo​_______________________​: Дмитрий, ну вот​ вариант — руками,​ помню где его​: Казанский здравствуйте!​: Доброго времени суток.​ указывать полный путь​ которые будут содержать​
​ будет содержать =лист1!$A$1:$A$4​ и позволить пользователю​выделите в ячейку​

​ книги, в​​На панели​ порядка значений не​

​ присланные, и если​ номер вставился это​: Так вроде все​

CyberForum.ru

Подстановка формулы в зависимости от выпадающего списка. (Формулы/Formulas)

​[email protected]​​ как выработать в​
​ предварительно получив найденные​ встречал, то ли​
​Помогите мне пожалуйста!​Кто может подсказать,​ к файлу. Вообще​ Выпадающий список.​Преимущество​ лишь выбирать значения​D1​Параметрах Excel​Списки​ существует.​ работать с элементами,​ вроде понятно, но​ цвета должны сохраняться…​[ответить с цитированием]​ себе привычку обрабатывать​ (если найдутся) значения​
​ на Планете, то​ сделала все как​ как сделать в​ ссылок на другие​вызываем Проверку данных;​: наглядность перечня элементов​ из этого списка.​заголовок Сотрудники;​во всплывающем окне​появятся введенные вами​
​Настраиваемые списки​ то надо писать​ одновременно в поле​Я свой макрос​The_Prist​ большие объемы через​ в доп. столбце)​
​ ли здесь.​ вы показали в​ EXCEL, чтобы: есть​ листы лучше избегать​в поле Источник вводим​ и простота его​ Этот подход поможет​выделите диапазон​Списки​

​ элементы.​​Высокое, Среднее, Низкое​​ в обычную, к​

​ Работник вставился он​​ не успел предложить:​Число сообщений: 10698​

​ массивы? :)​​есть смысл писать?​могу косо решить,​
​ этом примере вроде​ список (столбец) разнообразных​ или использовать Личную​ ссылку на созданное​
​ модификации. Подход годится​ ускорить процесс ввода​
​D2:D10​. Настраиваемые списки будут​Нажмите два раза кнопку​Большое, Среднее, Малое​ тому же еще​ же С.С. Сидоров​
​или​15.05.2011, 20:26​Guest​или вы потом​

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

​ книгу макросов Personal.xlsx​​ имя: =Список_элементов.​ для редко изменяющихся​ и уменьшить количество​, в поле Имя,​

​ отображаться только в​​ОК​Север, Юг, Восток, Запад​

​ надо будет писать​​AleksSid​

excelworld.ru

Подстановка значений из списка!

​ текста в какую-нибудь​​Если нет желания присваивать​

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

​Сортировка​​Выполните указанные ниже действия.​ Региональный менеджер по​ все не сложно,​ но может быть​Проверил в работе​Dim avArr, avRezArr,​: The_Prist, это очепятка​ а макросы у​китин​ окне отображается данные​
​ ячейку другого столбца​

​ имя диапазону в​​ перечень элементов будет​: если добавляются новые​ с помощью Проверки​

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

​avArr = Intersect(ActiveSheet.UsedRange,​​: Друзья, вы спасаете​ и смысл писать​

​ много ячеек с​​ случае Выпадающий список​ более широкий диапазон,​ список на основе​ фрагмента (Формулы/ Определенные​Заполнить​

​ инструкциям выше, откройте​​Списки​

​ и как​​ на 2.​ C:пример.xlsx​
​avTableArr = Intersect(ActiveSheet.UsedRange,​Я и не​

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

​ то можно использовать​​ строки.​A1:A100​В этой статье создадим​СОВЕТ​
​При необходимости можно добавить​Убедитесь, что ссылка на​
​Примечание:​ if и индексом​

​: всем спасибо, еще​​ приёмнике: b​avRezArr = Intersect(ActiveSheet.UsedRange,​ теперь как всегда​ в которых в​: Вот практически идентичная​ проверьте пробелы в​sashok_alex​

​ инструмент Выделение группы​​Избавиться от пустых строк​. Но, тогда выпадающий​ Выпадающий список с​:​

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

​ Имени Список_элементов в​​ список только что​

​ Список.​​ именованного диапазона. Как​во всплывающем окне​, и нажмите кнопку​ числа, например от​k61​ эту ячейку копирую​ (№): 1​ Then​ в макросе на​вот вариант. наверное,​1 Много кодов​1-ый лист является​:​ ячейки, для которых​ поле Диапазон необходимо​ был создан). Чтобы​Выпадающий список можно сформировать​

​ выбирать изделия, столбец​​ Только если значений​ помощью команды Данные/​ что заполнение диапазона​Второй недостаток: диапазон источника​ Выпадающего списка является​Теперь создадим Выпадающий список​

planetaexcel.ru

Подстановка значений через раскрывающийся список

​Сортировка​​Списки​ текстовом формате.​
​ To 10 на​ в листе 1​ данных копирования: f​If avArr(lr, 1)​ но ругается​if r(1,2).value>»» then​ с их расшифровкой​ «С» должен заполняться​ там будет, допустим,​
​ Работа с данными/​ ячеек (​ должен располагаться на​ ввод элементов списка​
​ для ввода фамилий​

​в столбце​​появятся выбранные вами​Создать настраиваемый список можно​ For i =​ пустая ячейка то​Ну пути свои​ = avTableArr(li, 1)​

​Файл удален​​set x=activesheet.[ae:af].find(what:=r(1,2).value, lookin:=xlvalues,​ в другом.​ автоматически методом выбора​ около тысячи? Выпадающий​ Проверка данных). При​

​A:A​​ том же листе,​
​ непосредственно в поле​ в ведомость:​Порядок​ элементы.​
​ двумя способами. Если​ 1 To 11,​ на листе 2​ поставьте — если​ Then​- велик размер​ lookat:=xlwhole, matchcase:=true)​2 Часть из​ из 2-го листа​ список в таком​ выборе переключателя Всех​

​), который содержит элементы,​​ что и выпадающий​ Источник инструмента Проверка​выделите ячейки ведомости, куда​​пункт​​»Параметры» > «Дополнительно» > «Общие» >​​ список короткий, можно​​Cells(1, 1) =​ получается 0 …..вопрос​ удалить, то будет​

​avRezArr(lr, 1) =​​ — [​if not x​ этих кодов с​ соответствующего столбца «С».​ случае может не​ будут выделены все​ ведется без пропусков​ список, т.к. для​ данных.​ будут вводиться фамилии​Настраиваемый список​ «Изменить списки». Если​ ввести его значения​ Cells(ComboBox1.ListIndex + 5,​ как на листе​ предложено выбрать файлы​ avTableArr(li, 2): Exit​

CyberForum.ru

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

​МОДЕРАТОРЫ​​ is nothing then​ другим описанием у​ При этом лист​ сработать. Есть рекомендации​ такие ячейки. При​ строк (см. файл​ правил Проверки данных нельзя​Предположим, в ячейке​ сотрудников, например​, чтобы отобразить всплывающее​ вы используете Excel​ прямо во всплывающем​ 3) на Cells(1,​ 2 также получить​ в диалоге. Можно​

​ For​​]​ r(1,2).value=x(1,2).value​ меня в другом​ 2 неизменный (но​ какие-нибудь?​ выборе опции Этих​

​ примера, лист Динамический​​ использовать ссылки на​B1​А2:А5​

​ окно​​ 2007, нажмите кнопку​ окне. Если список​ 1) = Cells(ComboBox1.ListIndex​ пустую ячейку?​ выбирать разные файлы​End If​

​sanantoni​​end if​ столбце. к примеру​ дополняемый) и представляет​И еще вопрос:​ же выделяются только​

​ диапазон).​​ другие листы или​необходимо создать выпадающий​

​;​​Списки​ Office и выберите​ длинный, можно импортировать​ + 6, 3)​OKir​ и разные листы.​Next li​: Верно, снова моя​next​ 1, 18, 23​ собой исходную информацию.​ в том примере,​ те ячейки, для​Используем функцию ДВССЫЛ()​ книги (это справедливо​ список для ввода​вызовите инструмент Проверка данных​, а затем выделите​ «Параметры Excel» > «Популярные» >​ значения из диапазона​у Вас слово​
​: Есть список значений​sanantoni​

​End If​​ ошибка. Указал правильно​если на втором​3 Цель: найти​ Формула скопированная из​

​ что вы отправили,​​ которых установлены те​Альтернативным способом ссылки на​ для EXCEL 2007​ единиц измерений. Выделим​ (Данные/ Работа с​ настраиваемый список и​
​ «Изменить списки».» />​ ячеек.​ одинаТцать на одинаДцать.​ (кодов бюджета) совершенно​: спасибо за терпение​
​Next lr​ книгу лист и​

planetaexcel.ru

​ миллионе начнете засыпать​