Получить список имен файлов из папки в excel (с vba и без)

Получение списка файлов в папке и подпапках средствами VBA

Функция VBA для получения списка файлов из папки, с учётом выбранной глубины поиска в подпапках

Пример в файле FilenamesCollection.xls выводит список файлов на чистый лист новой книги (формируя заголовки)

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

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

ПРИМЕЧАНИЕ: Если вы выводите на лист список имен файлов картинок (изображений), то при помощи этой надстройки вы сможете вставить сами картинки в ячейки соседнего столбца (или в примечания к этим ячейкам)

выполненной на основе функции FilenamesCollection, а также со способом добавления в таблицу значений ячеек из найденных файлов

‘ Пример использования функции в макросе:

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

Ещё один пример использования:

PS: Найти подходящие имена файлов в коллекции можно при помощи следующей функции:

234686 просмотров

Как в Excel вернуть предыдущую версию файла после сохранения?

Как восстановить измененный файл в Excel?

Если файл сохранен

1. Откройте файл, с которым вы работали.
2. Выберите Файл > Сведения .
3. В разделе Управление документом выберите файл с отметкой (при закрытии без сохранения) . …
4. На панели в верхней части файла выберите команду Восстановить , чтобы перезаписать сохраненные ранее версии.

Можно ли восстановить предыдущую версию файла?

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

Можно ли отменить сохранение в Excel?

Если файл сохранен

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

Как сделать откат файла Эксель?

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

Как вернуть замененный файл?

Как это работает на практике:

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

Где хранятся резервные копии файлов Excel?

Резервные копии хранятся в той же папке, что и основной документ, только с расширением «wbk». Например, «Копия. Статья. wbk».

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

Просмотр предыдущих версий файлов Office

1. Откройте файл, который вы хотите просмотреть.
2. Щелкните «Файл» > «Сведения > версии».
3. Выберите версию, чтобы открыть ее в отдельном окне.
4. Если вы хотите восстановить предыдущую открытую версию, выберите «Восстановить».

Как восстановить измененный файл в Word?

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

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

Как восстановить предыдущую версию файла TXT?

Шаг 1: Перейдите в папку, содержащую рассматриваемый файл. Шаг 2: Щелкните правой кнопкой мыши файл и выберите Восстановить предыдущую версию, Вы увидите список доступных предыдущих версий файла.

Как можно отменить сохранение документа?

Нажимайте клавиши CTRL+Z или кнопку ( Отменить) на ленте, пока не исправите ошибку. Некоторые действия вы не сможете отменить, например выбор команд на вкладке Файл или сохранение файла. Если вы не можете отменить действие, команда Отменить будет неактивна (затенена). .

Как вернуть данные в Excel?

Восстановление данных, когда книга открыта в Excel

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

Как отменить изменения в файле Excel после сохранения?

Чтобы отменить последнее действие, нажмите клавиши CTRL+Z. Вы также можете нажать кнопку Отменить на панели быстрого доступа. Если нужно отменить несколько действий, можно нажать кнопку «Отменить» (или клавиши CTRL+Z) несколько раз.

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

Просмотр журнала изменений

1. На вкладке Рецензирование нажмите кнопку Отслеживание изменений и выберите команду Выделить исправления. …
2. В группе Отслеживать исправления, внесенные установите флажок по времени и в списке время выберите вариант Все.
3. Снимите флажки пользователем и в диапазоне.

Упражнение

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

1. Создайте в Excel новую книгу.
2. Выберите команду ДанныеПолучить внешние данныеИз других источниковИз Microsoft Query.
3. Выберите в качестве источника данных MS Access. В списке баз данных окна Выбор источника данных это будет либо пункт База данных MS Access, либо пункт MS Access database (точное название зависит от вашего программного обеспечения).
4. В следующем окне откройте базу данных Борей.mdb, при стандартной установке расположенную по адресу C:\Program Files\Microsoft Office\Office\Samples\.
5. Включите в запрос таблицу Клиенты (поля КодКлиента, Название и Должность), таблицу Заказы (поля КодЗаказа и КодКлиента) и таблицу Сведения о заказах (все поля).
6. Задайте условие отбора данных — поле КодКлиента больше CACTU.
7. Выберите сортировку по полю КодЗаказа.
8. Установите переключатель Просмотр или изменение данных в MS Query и щелкните на кнопке Готово.
9. В открывшемся окне MS Query раскройте меню Вид и установите флажки возле команд Таблицы и Условия. В результате в окне MS Query вы должны увидеть область результата выполнения запроса, довольно большую таблицу с запрошенными полями

и данными в них, область таблиц (рис. 8.35) и область условий (рис. 8.36). Вы можете видеть, что три таблицы связаны между собой, причем линии связи показывают, какие поля связывают таблицы. В области условий отображено условие, которое мы задали с помощью мастера запросов. Полный текст запроса, согласно которому из базы данных были извлечены данные, можно увидеть, щелкнув на кнопке Режим SQL. Запрос получился довольно объемным. Последняя его строка (ORDER BY Заказы.КодЗаказа) указывает на то, что сортировка записей будет произведена по полю КодЗаказа из таблицы Заказы.

Рис. 8.35. Область таблиц

Рис. 8.36. Область условий

1. Как вы помните, из таблицы Заказы мы включили в запрос только два поля. В области просмотра таблиц очень легко добавить к запросу недостающие поля. Щелкните дважды на поле КодСотрудника прямо в области просмотра таблицы Заказы, и соответствующее поле будет добавлено как последний столбец к основной таблице вывода результата запроса.
2. Щелкните в области просмотра таблиц на таблице Сведения о Заказах. Откройте меню Таблица и выберите команду Удалить таблицу. Вы немедленно увидите результат в основном поле данных: все поля (столбцы) этой таблицы пропадут из таблицы данных.
3. Добавим в запрос еще одно условие. Для этого можно непосредственно вводить условия в таблицу или выбрать команду УсловияДобавить условие, которая выводит на экран окно задания условия. Выберем то же поле КодКлиента, зададим объединение условий оператором И, само условие (меньше) и значение (EASTC). После щелчка на кнопке Добавить количество строк в таблице данных резко уменьшится.
4. Теперь проверим, как работает команда Специальное копирование меню Правка. Для этого выделите все записи в таблице, начинающиеся с символа С. Раскройте меню Правка и выберите команду Специальное копирование. В открывшемся диалоговом окне установите флажки Включить заголовки столбцов и Включить номера строк, а затем щелкните на кнопке OK. Перейдите в книгу Excel, выделите ячейку С3 и нажмите сочетание клавиш Ctrl+V. Результатом будет помещение в таблицу не только скопированных данных, но и номеров строк и заголовков столбцов. Далее можно выделить этот фрагмент и задать ему параметры форматирования при помощи команды Автоформат. На рис. 8.37 вы можете видеть, как выглядит этот фрагмент данных после назначения ему встроенного формата Список 2.

Рис. 8.37. Фрагмент внешних данных в Excel

ПРИМеЧАНИе

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

Функция ПСТР в Excel для выделения фамилии из ФИО

Каким языком программирования мы воспользуемся? Формулы Excel вполне для этого подойдут, раз уж мы выбрали Эксель для нашего примера. В Excel есть функция ПСТР (сокращение от «подстрока»). В формуле ПСТР нужно:

• указать исходную строку текста (это ячейка B1 на рис. 1),
• затем ввести первое число (это 1), указывающего на номер начального символа подстроки,
• и ввести второе число (это 9), указывающего на номер последнего символа подстроки.

Таким образом мы пишем следующую расчетную формулу:

– вот так это выглядит в исполнении для компьютера, для Excel.

Вводим указанную формулу в ячейку B4, и получаем результат: фамилию Завьялова в ячейке B4 (рис. 2):

Рис. 2. Результат применения формулы ПСТР в табличном редакторе Excel для извлечения Фамилии из ФИО.

Единичку слева мы приписали вручную – это наша первая (удачная!) попытка извлечь фамилию. Формулу справа мы записали лишь для наглядности нашего примера. В реальной рабочей программе дополнительное размещение формулы в соседней ячейке справа не нужно.

Итак, извлечь Фамилию из ФИО в Excel получилось! Мы имеем то, что хотели с помощью программы на компьютере. Мы смогли из ФИО извлечь первые 9 символов – это и есть искомая фамилия. Главное: нам удалось оцифровать задачу.

Мы сумели цифрами объяснить программе, компьютеру, процессору, какие данные он должен откуда взять и как их обработать. Главные цифры нашей небольшой успешной программы – это 1 и 9, начало и конец подстроки текста. Компьютер в цифрах знает толк, понимает их, делает то, что нам нужно. Как видим, ни о каких буквах (а ФИО – это буквы!) тут речь не идет. Вместо букв мы оперируем их порядковыми номерами в строке. Вот вам и оцифровка: по номерам позиций букв в строке!

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

Как получить список файлов в Excel с помощью VBA

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

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

Способ 1. Использование функции Dir

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

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

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

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

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

Если же необходимо отобрать только определенные типы файлов, например Excel файлы, то в нашем коде необходимо сделать дополнительную проверку:

Знак «*» означает любой набор символов. Таким образом, мы учли различные версии файлов Excel (xls, xlsx, xlsm).

Способ 2. Используем объект FileSystemObject

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

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

1. Открыть пункт меню Tools и выбрать пункт References.
2. Выбрать ссылку на библиотеку Microsoft Scripting Runtime.
3. Нажать Ок.

Теперь перейдем к коду. Он немного упростился:

Обратите внимание на переменные. Переменная FSO — это новый экземпляр объекта FileSystemObject

Тут мы его объявляем и сразу создаем. Директива New очень важна, многие тут допускают ошибку. Также создаем объекты MyFolder и iFile — это тоже объекты FileSystemObject

Далее делаем проверку на существование папки. В данном случае нам не нужна дополнительная функция , мы пользуемся методом FolderExists объекта (класса) FileSystemObject.

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

Результат работы программы следующий:

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

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

Итак перейдем к коду:

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

• Filter — перечисление списка файлов, которые мы хотим получить. Перечислять необходимо через запятую, например «doc, xls*». Вы можете использовать символ «*» чтобы включить сравнение по шаблону. По умолчанию фильтр отсутствует и возвращаются все типы файлов.
• Nesting — вложенность. Это максимальное число вложенных папок в которые «проваливается» алгоритм. По умолчанию равно 100.

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

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

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

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

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

Функции для работы с текстом в Excel

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

Примеры функции ТЕКСТ в Excel

Преобразует числа в текст. Синтаксис: значение (числовое или ссылка на ячейку с формулой, дающей в результате число); формат (для отображения числа в виде текста).

Самая полезная возможность функции ТЕКСТ – форматирование числовых данных для объединения с текстовыми данными. Без использования функции Excel «не понимает», как показывать числа, и преобразует их в базовый формат.

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

Использование амперсанда без функции ТЕКСТ дает «неадекватный» результат:

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

Формула «для даты» теперь выглядит так:

Второй аргумент функции – формат. Где брать строку формата? Щелкаем правой кнопкой мыши по ячейке со значением. Нажимаем «Формат ячеек». В открывшемся окне выбираем «все форматы». Копируем нужный в строке «Тип». Вставляем скопированное значение в формулу.

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

Если нужно вернуть прежние числовые значения (без нулей), то используем оператор «—»:

Обратите внимание, что значения теперь отображаются в числовом формате

Функция разделения текста в Excel

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

• ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков с начала ячейки;
• ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество знаков с конца ячейки;
• ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо

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

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

В первой строке есть только имя и фамилия, разделенные пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества знаков – используется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.

Формула для извлечения фамилии:

С помощью функции ПОИСК Excel определяет количество знаков для функции ПРАВСИМВ. Функция ДЛСТР «считает» общую длину текста. Затем отнимается количество знаков до первого пробела (найденное ПОИСКом).

Вторая строка содержит имя, отчество и фамилию. Для имени используем такую же формулу:

Формула для извлечения фамилии несколько иная: Это пять знаков справа. Вложенные функции ПОИСК ищут второй и третий пробелы в строке. ПОИСК(» «;A3;1) находит первый пробел слева (перед отчеством). К найденному результату добавляем единицу (+1). Получаем ту позицию, с которой будем искать второй пробел.

Часть формулы – ПОИСК(» «;A3;ПОИСК(» «;A3;1)+1) – находит второй пробел. Это будет конечная позиция отчества.

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

Формула «для отчества» строится по тем же принципам:

Функция объединения текста в Excel

Для объединения значений из нескольких ячеек в одну строку используется оператор амперсанд (&) или функция СЦЕПИТЬ.

Например, значения расположены в разных столбцах (ячейках):

Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Выбираем первую ячейку с текстом и нажимаем на клавиатуре &. Затем – знак пробела, заключенный в кавычки (“ “). Снова — &. И так последовательно соединяем ячейки с текстом и пробелы.

Получаем в одной ячейке объединенные значения:

Использование функции СЦЕПИТЬ:

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

Функция ПОИСК текста в Excel

Функция ПОИСК возвращает начальную позицию искомого текста (без учета регистра). Например:

Функция ПОИСК вернула позицию 10, т.к. слово «Захар» начинается с десятого символа в строке. Где это может пригодиться?

Функция ПОИСК определяет положение знака в текстовой строке. А функция ПСТР возвращает текстовые значения (см. пример выше). Либо можно заменить найденный текст посредством функции ЗАМЕНИТЬ.

Поиск ссылок на книги в диаграммах

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

Выберите свою диаграмму и перейдите на вкладку «Формат», которая появится после того, как вы это сделаете. В крайнем левом углу ленты щелкните раскрывающийся список «Элементы диаграммы» в разделе «Текущий выбор».

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

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

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

Импорт данных из Access в Excel

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

Предположим, мы имеем такую базу данных Access:

Чтобы загрузить данные, откроем пустой файл Excel, выберем в меню Данные — Получить внешние данные из Access.

В появившемся окне, выберем необходимый файл Access. Далее, появится следующее окно:

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

Теперь мы получили таблицу в Excel, которая связана с данными из файла Access . Но наша таблица не является простой, фактически она является запросом к базе данных. Это так называемая Умная таблица, которую можно обновить и получить «свежие» данные (щелкаем правой кнопкой мыши на таблицу и выбираем «Обновить»).

Как взять значение из другого листа в Excel?

Ссылка на лист, отделенная от ссылки на диапазон значений.

1. Щелкните ячейку, в которую нужно ввести формулу.
2. В строка формул введите = (знак равенства) и формулу, которую нужно использовать.
3. Щелкните ярлычок листа, на который нужно сослаться.
4. Выделите ячейку или диапазон ячеек, на которые нужно сослаться.

Как связать данные расположенные на разных листах Excel?

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

Как перенести данные из одного листа в другой Excel?

Копирование листа в одной книге

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

Как в Excel получить данные из другого файла?

Подключение к другой книге

1. На вкладке Данные щелкните Получить данные > Из файла > Из книги . …
2. Найдите книгу в окне Импорт данных .
3. В окне Навигатор выберите таблицу или лист, которые вы хотите импортировать, а затем нажмите кнопку Загрузить или Изменить .

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

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

Как в Формуле сделать ссылку на другой лист?

Создаем ссылку в Excel на другой лист

1. Найдите ячейку, на которую хотите сослаться, и запомните, где она находится. …
2. Перейдите к нужному листу. …
3. Выбранный лист откроется.
4. Найдите и выделите ячейку, в которой должно появиться значение. …
5. Введите знак равенства (=), название листа с восклицательным знаком(!)

Как ссылаться на данные с другого листа Гугл таблицы?

Выберите или создайте таблицу. Выделите ячейку. Введите =, затем название листа, восклицательный знак и номер копируемой ячейки, например =Sheet1! A1 или =’Sheet number two’!

Как связать между собой два документа Excel?

Как объединить данные из листов c одинаковым именем на одном сводном листе

1. Нажмите кнопку Объединить листы …
2. Отметьте флажком Таблицы с заголовками если это так. …
3. Выберите листы для объединения, установив соответствующие флажки в дереве данных. …
4. Нажмите кнопку Объединить

Как в Excel взять данные с другого листа?

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

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

Как сделать несколько копий одного листа в Excel?

Перейти на вкладку VBA-Excel на ленте. Запустить Диспетчер листов.…Запустить Диспетчер листов.

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

Как подтянуть данные из одной таблицы в другую Гугл таблицы?

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

1. Откройте файл в Таблицах.
2. В пустой ячейке введите =IMPORTRANGE.
3. Укажите в скобках следующие параметры через запятую, заключив их в кавычки: URL таблицы в Таблицах; название листа (необязательно) и диапазон импортируемых ячеек.
4. Нажмите Ввод.
5. Нажмите Открыть доступ, чтобы связать таблицы.

Как связать две таблицы в Excel?

В окне Управление связями нажмите кнопку Создать. В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи «один ко многим» эта таблица должна быть частью с несколькими элементами.

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

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

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

Попробуем воспользоваться командой СПЕЦИАЛЬНАЯ ВСТАВКА и скопировать все данные.

Сначала мы выделяем имеющуюся таблицу, правой кнопкой вызываем меню и жмем КОПИРОВАТЬ.

В свободной ячейке снова вызываем меню правой кнопкой и нажимаем СПЕЦИАЛЬНАЯ ВСТАВКА.

Если мы оставим все как есть по умолчанию и просто нажмем ОК, то таблица вставится полностью, со всеми ее параметрами.

Попробуем поэкспериментировать. В СПЕЦИАЛЬНОЙ ВСТАВКЕ выберем другой пункт, например, ФОРМУЛЫ. Получили уже неформатированную таблицу, но с работающими формулами.

Теперь вставим не формулы, а только ЗНАЧЕНИЯ результатов их вычислений.

Чтобы новая таблица со значениями получила внешний вид аналогичный образцу выделим ее и вставим ФОРМАТЫ с помощью специальной вставки.

Теперь попробуем выбрать пункт БЕЗ РАМКИ. Получили полную таблицу, но только без выделенных границ.

Полезный совет! Чтобы перенести формат вместе с размерами столбцов нужно перед копированием выделить не диапазон исходной таблицы, а столбцы целиком (в данном случаи это диапазон A:F).

Аналогично можно поэкспериментировать с каждым пунктом СПЕЦИАЛЬНОЙ ВСТАВКИ, чтобы наглядно увидеть, как она работает.

Программа DirLister для получения списка файлов

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

Для запуска программы, откройте папку с программой, а потом кликните по приложению DirLister.

В окне программы DirLister, в разделе «Directory To Scan», при помощи кнопки «Open», вам нужно будет выбрать папку для сканирования.

В разделе «Output File», при помощи кнопки «Save As…», вам необходимо будет выбрать место для сохранения списка файлов.

Вы можете уточнить критерии поиска файлов по маске «*.*», выбрав нужное имя или тип файла («TXT», «Excel», «HTML» и т.д.). После выбора необходимых настроек, нажмите на кнопку «Make List».

После завершения сканирования в левом нижнем углу программы будет выведена информация об успешном завершении создания списка файлов: «All file processed correctly».

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

Примеры использования функции ДВССЫЛ в Excel

Пример 1. Используем ссылку на ячейку для получения значения

Функция ДВССЫЛ получает ссылку на ячейку как исходные данные и возвращает значение ячейки по этой ссылке (как показано в примере ниже):

Формула в ячейке С1:

Функция получает ссылку на ячейку (в двойных кавычках) и возвращает значение этой ячейки, которая равна “123”.

Вы можете спросить — почему бы нам просто не использовать «=A1» вместо использования функции INDIRECT (ДВССЫЛ)?

И вот почему…

Если в данном случае вы введете в ячейку С1 формулу “=A1” или “=$A$1”, то она выдаст вам тот же результат, что находится в ячейке А1. Но если вы вставите в таблице строку выше, вы можете заметить, что ссылка на ячейку будет автоматически изменена.

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

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

На примере выше, ячейка «А1» содержит в себе число “123”.

Ячейка «С1» ссылается на ячейку «А1».

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

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

Пример 3. Используем комбинацию текстового и числового значений в функции INDIRECT (ДВССЫЛ)

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

Например, если в ячейке С1 указано число “2”, то используя формулу =INDIRECT(“A”&C1) или =ДВССЫЛ(«A»&C1) вы получите ссылку на значение ячейки «А2».

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

Больше лайфхаков в нашем Telegram Подписаться

Пример 4. Ссылаемся на диапазон ячеек с помощью функции INDIRECT (ДВССЫЛ)

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

Например, =INDIRECT(“A1:A5”) или =ДВССЫЛ(«A1:A5») будет ссылаться на данные из диапазона ячеек «A1:A5».

Используя функцию SUM (СУММ) и INDIRECT (ДВССЫЛ) вместе, вы можете рассчитать сумму, а также максимальные и минимальные значения диапазона.

Если вы создали именованный диапазон в Excel, вы можете обратиться к нему с помощью функции INDIRECT (ДВССЫЛ).

Например, представим что у вас есть оценки по 5 студентам по трем предметам как показано ниже:

Зададим для следующих ячеек названия:

• B2:B6: Математика
• C2:C6: Физика
• D2:D6: Химия

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

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

Например, если вы хотите узнать средний балл среди студентов по математике — используйте следующую формулу:

Если имя диапазона указано в ячейке («F2» в приведенном ниже примере указан как “Матем”), вы можете использовать ссылку на ячейку прямо в формуле. В следующем примере показано, как вычислять среднее значение с использованием именных диапазонов.

Пример 6. Создаем зависимый выпадающий список с помощью INDIRECT (ДВССЫЛ)

C помощью этой функции вы можете создавать зависимый выпадающий список.

Например, предположим, что у вас есть две колонки с названиями «Россия» и «США», в строках указаны города этих стран, как указано на примере ниже:

Для того, чтобы создать зависимый выпадающий список вам нужно создать два именованных диапазона для ячеек «A2:A5» с именем “Россия” и для ячеек «B2:B5» с названием “США”.

Теперь, в ячейке «D2» создайте выпадающий список для «России» и «США». Так мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.

Теперь, для создания зависимого выпадающего списка:

• Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
• Кликните по вкладке “Data” -> “Data Validation”;
• На вкладке “Настройки” в разделе “Allow” выберите List;
• В разделе “Source” укажите ссылку: =INDIRECT($D$2) или =ДВССЫЛ($D$2);
• Нажмите ОК

Теперь, если вы выберите в первом выпадающем списке, например, страну «Россия», то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Такая же ситуация, если вы выберите страну «США» из первого выпадающего списка.

Больше лайфхаков в нашем ВК