Какие формулы работают с закрытыми файлами excel
Обновлено: 08.07.2024
Не судите строго, с экселем и тем более формулами я на "ВЫ".
Вобщем, запутался я с кавычками в трех соснах По форуму поискал, есть по этой функции много чего, но ответа не увидел.
А на счет того момента, что файл-донор должен быть открыт не ясный момент: я же ссылаюсь пытаюсь солсаться функцией ДВССЫЛ на ячейку в этой же книге. Должно работать.
Чувствую, истина где-то рядом. Подскажите, знающие люди, куда копать.
У меня не получается побороть такую задачу. Есть файл excel (Сводная.xls). В нем прописаны связи с другими книгами
Причем сводок много (50), путь всегда постоянен, меняться может только петя, вася ит.д.
Идея была в том, чтобы не меняя формулы применить функцию ДВССЫЛ. Например в ячейке A1 записать "петя" получится что-то вроде:
Не судите строго, с экселем и тем более формулами я на "ВЫ".
Вобщем, запутался я с кавычками в трех соснах По форуму поискал, есть по этой функции много чего, но ответа не увидел.
А на счет того момента, что файл-донор должен быть открыт не ясный момент: я же ссылаюсь пытаюсь солсаться функцией ДВССЫЛ на ячейку в этой же книге. Должно работать.
Чувствую, истина где-то рядом. Подскажите, знающие люди, куда копать.
Причем сводок много (50), путь всегда постоянен, меняться может только петя, вася ит.д.
Идея была в том, чтобы не меняя формулы применить функцию ДВССЫЛ. Например в ячейке A1 записать "петя" получится что-то вроде:
Не судите строго, с экселем и тем более формулами я на "ВЫ".
Вобщем, запутался я с кавычками в трех соснах По форуму поискал, есть по этой функции много чего, но ответа не увидел.
А на счет того момента, что файл-донор должен быть открыт не ясный момент: я же ссылаюсь пытаюсь солсаться функцией ДВССЫЛ на ячейку в этой же книге. Должно работать.
Чувствую, истина где-то рядом. Подскажите, знающие люди, куда копать.
Автор - Alex_M
Дата добавления - 28.01.2014 в 10:15
Справка Excel: "Если значение аргумента "ссылка_на_ячейку" является ссылкой на другую книгу (внешней ссылкой), другая книга должна быть открыта". Не ячейка, в которой написано "петя", а итоговое значение, которое получается тут ДВССЫЛ(ААА) на месте ААА.
а Ваша формула теоретически должна выглядеть примерно так
Но с закрытыми книгами все равно работать не будет
Справка Excel: "Если значение аргумента "ссылка_на_ячейку" является ссылкой на другую книгу (внешней ссылкой), другая книга должна быть открыта". Не ячейка, в которой написано "петя", а итоговое значение, которое получается тут ДВССЫЛ(ААА) на месте ААА.
а Ваша формула теоретически должна выглядеть примерно так
Но с закрытыми книгами все равно работать не будет _Boroda_
Но с закрытыми книгами все равно работать не будет Автор - _Boroda_
Дата добавления - 28.01.2014 в 10:29
Спасибо за ответ.
Но есть ли другие методы решения проблемы?
Неужели нельзя в пути переменные использовать?
И потом, я разбирал файлы использующие ДВССЫЛ (скачал на этом форуме но к стыду своему не могу сослаться на нужную тему)
В них используется данная функция и файл-донор закрыт, правда лежит в той же папке.
В чем колдовство не пойму
Спасибо за ответ.
Но есть ли другие методы решения проблемы?
Неужели нельзя в пути переменные использовать?
И потом, я разбирал файлы использующие ДВССЫЛ (скачал на этом форуме но к стыду своему не могу сослаться на нужную тему)
В них используется данная функция и файл-донор закрыт, правда лежит в той же папке.
В чем колдовство не пойму Alex_M
И потом, я разбирал файлы использующие ДВССЫЛ (скачал на этом форуме но к стыду своему не могу сослаться на нужную тему)
В них используется данная функция и файл-донор закрыт, правда лежит в той же папке.
В чем колдовство не пойму Автор - Alex_M
Дата добавления - 28.01.2014 в 11:01
Ура, нашла тему с тем же, что и у меня, вопросом. Правда, я с английским аналогом наименования формул не очень дружу, поэтому заранее извините)
У меня ситуация похожая - хотелось бы "научить" эксель самого брать нужные значения из нужных файлов - они постоянные, разве что год меняется да название месяца. Возможно ли, чтобы в строке января данные импортировались из файла за январь, в строке февраля - за февраль и так далее? Или всё ж таки ручками делать?
С функцией ДВССЫЛ тоже экспериметрировала, тоже ничего не получилось)
Какое-то время назад (года два, наверное) точно помню, что читала о подобном на каком-то форуме, там объяснялось, как это МОЖНО сделать, но тогда не было актуально.
Ура, нашла тему с тем же, что и у меня, вопросом. Правда, я с английским аналогом наименования формул не очень дружу, поэтому заранее извините)
У меня ситуация похожая - хотелось бы "научить" эксель самого брать нужные значения из нужных файлов - они постоянные, разве что год меняется да название месяца. Возможно ли, чтобы в строке января данные импортировались из файла за январь, в строке февраля - за февраль и так далее? Или всё ж таки ручками делать?
С функцией ДВССЫЛ тоже экспериметрировала, тоже ничего не получилось)
Какое-то время назад (года два, наверное) точно помню, что читала о подобном на каком-то форуме, там объяснялось, как это МОЖНО сделать, но тогда не было актуально. macovea
Чайник как он есть :)
Какое-то время назад (года два, наверное) точно помню, что читала о подобном на каком-то форуме, там объяснялось, как это МОЖНО сделать, но тогда не было актуально. Автор - macovea
Дата добавления - 03.02.2014 в 10:53
Дата добавления - 03.02.2014 в 12:39 С закрытыми книгами в свое время измучился. Решил запросами из MS Query на отдельный лист в книге. Так работает нормально. Хотя чую, что не совсем правильно решил задачу свою )) С закрытыми книгами в свое время измучился. Решил запросами из MS Query на отдельный лист в книге. Так работает нормально. Хотя чую, что не совсем правильно решил задачу свою )) Stormy
Такую задачу лучше решать через Гиперссылку(), она работает с закрытыми книгами. Но тоже проблема с синтаксисом.
еще в 97 экселе как то через гиперссылки формировал базу данных - выбор значений из разных книг, разложенных в каталогах (по году, месяцу, заказчику, профессии, ФИО)
тогда сделал легко.
в 2010м попробовал, почему-то глюк-на глюке, некорректно формула программно формируется, двойную кавычку как """ уже не получается в строку включить (редактор ругается), CHR(34) некорректно включается тоже (формула отображается в виде текста, не передает сначения)
Кто программно работал в 2010 с гиперссылкой, в чем закавыка?
Такую задачу лучше решать через Гиперссылку(), она работает с закрытыми книгами. Но тоже проблема с синтаксисом.
еще в 97 экселе как то через гиперссылки формировал базу данных - выбор значений из разных книг, разложенных в каталогах (по году, месяцу, заказчику, профессии, ФИО)
тогда сделал легко.
в 2010м попробовал, почему-то глюк-на глюке, некорректно формула программно формируется, двойную кавычку как """ уже не получается в строку включить (редактор ругается), CHR(34) некорректно включается тоже (формула отображается в виде текста, не передает сначения)
Кто программно работал в 2010 с гиперссылкой, в чем закавыка? fsl
еще в 97 экселе как то через гиперссылки формировал базу данных - выбор значений из разных книг, разложенных в каталогах (по году, месяцу, заказчику, профессии, ФИО)
тогда сделал легко.
в 2010м попробовал, почему-то глюк-на глюке, некорректно формула программно формируется, двойную кавычку как """ уже не получается в строку включить (редактор ругается), CHR(34) некорректно включается тоже (формула отображается в виде текста, не передает сначения)
Кто программно работал в 2010 с гиперссылкой, в чем закавыка? Автор - fsl
Дата добавления - 04.02.2014 в 12:26
Добрый вечер, коллеги!
С формулами на Ты, но в этой задачке затык.
Есть два файла:
1) Исходник, где содержатся данные - ИТОГО данных смещено всегда на 3 строки;
2) И Файл куда собираются ИТОГО.
Задача стоит сложить ИТОГО по названиям продуктов (условно), продукты повторяются в столбце.
Сложить не проблема, вариантов масса, все (на сколько сам знаю) попробовал.
Вот только есть нюанс - при закрытии Исходника выдает ЗНАЧ.
Везде читаю, что это связано с особенностью функции СМЕЩ, но с ИНДЕКС тоже выдает ЗНАЧ.
Варианты: открывать Исходник и макросы не рассматриваются.
Есть у кого-нибудь идеи? Буду признателен за советы.
Добрый вечер, коллеги!
С формулами на Ты, но в этой задачке затык.
Есть два файла:
1) Исходник, где содержатся данные - ИТОГО данных смещено всегда на 3 строки;
2) И Файл куда собираются ИТОГО.
Задача стоит сложить ИТОГО по названиям продуктов (условно), продукты повторяются в столбце.
Сложить не проблема, вариантов масса, все (на сколько сам знаю) попробовал.
Вот только есть нюанс - при закрытии Исходника выдает ЗНАЧ.
Везде читаю, что это связано с особенностью функции СМЕЩ, но с ИНДЕКС тоже выдает ЗНАЧ.
Варианты: открывать Исходник и макросы не рассматриваются.
Есть у кого-нибудь идеи? Буду признателен за советы. Anis625
Есть два файла:
1) Исходник, где содержатся данные - ИТОГО данных смещено всегда на 3 строки;
2) И Файл куда собираются ИТОГО.
Задача стоит сложить ИТОГО по названиям продуктов (условно), продукты повторяются в столбце.
Сложить не проблема, вариантов масса, все (на сколько сам знаю) попробовал.
Вот только есть нюанс - при закрытии Исходника выдает ЗНАЧ.
Везде читаю, что это связано с особенностью функции СМЕЩ, но с ИНДЕКС тоже выдает ЗНАЧ.
Варианты: открывать Исходник и макросы не рассматриваются.
Есть у кого-нибудь идеи? Буду признателен за советы. Автор - Anis625
Дата добавления - 07.06.2018 в 21:56
Microsoft Excel предоставляет пользователю несколько, условно выражаясь, уровней защиты - от простой защиты отдельных ячеек до шифрования всего файла шифрами крипто-алгоритмов семейства RC4. Разберем их последовательно.
Уровень 0. Защита от ввода некорректных данных в ячейку
Самый простой способ. Позволяет проверять что именно пользователь вводит в определенные ячейки и не разрешает вводить недопустимые данные (например, отрицательную цену или дробное количество человек или дату октябрьской революции вместо даты заключения договора и т.п.) Чтобы задать такую проверку ввода, необходимо выделить ячейки и выбрать на вкладке Данные (Data) кнопку Проверка данных (Data Validation) . В Excel 2003 и старше это можно было сделать с помощью меню Данные - Проверка (Data - Validation) . На вкладке Параметры из выпадающего списка можно выбрать тип разрешенных к вводу данных:
Уровень 1. Защита ячеек листа от изменений
Мы можем полностью или выборочно запретить пользователю менять содержимое ячеек любого заданного листа. Для установки подобной защиты следуйте простому алгоритму:
- Выделите ячейки, которые не надо защищать (если таковые есть), щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек(Format Cells) . На вкладке Защита(Protection) снимите флажок Защищаемая ячейка(Locked) . Все ячейки, для которых этот флажок останется установленным, будут защищены при включении защиты листа. Все ячейки, где вы этот флаг снимете, будут доступны для редактирования несмотря на защиту. Чтобы наглядно видеть, какие ячейки будут защищены, а какие - нет, можно воспользоваться этим макросом.
- Для включения защиты текущего листа в Excel 2003 и старше - выберите в меню Сервис - Защита - Защитить лист(Tools - Protection - Protect worksheet) , а в Excel 2007 и новее - нажмите кнопку Защитить лист (Protect Sheet) на вкладке Рецензирование (Reveiw) . В открывшемся диалоговом окне можно задать пароль (он будет нужен, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить, при желании, исключения:
Т.е., если мы хотим оставить пользователю возможность, например, форматировать защищенные и незащищенные ячейки, необходимо установить первых три флажка. Также можно разрешить пользователям использовать сортировку, автофильтр и другие удобные средства работы с таблицами.
Уровень 2. Выборочная защита диапазонов для разных пользователей
Если предполагается, что с файлом будут работать несколько пользователей, причем каждый из них должен иметь доступ в свою область листа, то можно установить защиту листа с разными паролями на разные диапазоны ячеек.
Чтобы сделать это выберите на вкладке Рецензирование (Review) кнопку Разрешить изменение диапазонов (Allow users edit ranges) . В версии Excel 2003 и старше для этого есть команда в меню Сервис - Защита - Разрешить изменение диапазонов (Tools - Protection - Allow users to change ranges) :
В появившемся окне необходимо нажать кнопку Создать (New) и ввести имя диапазона, адреса ячеек, входящих в этот диапазон и пароль для доступа к этому диапазону:
Повторите эти действия для каждого из диапазонов разных пользователей, пока все они не окажутся в списке. Теперь можно нажать кнопку Защитить лист (см. предыдущий пункт) и включить защиту всего листа.
Теперь при попытке доступа к любому из защищенных диапазонов из списка, Excel будет требовать пароль именно для этого диапазона, т.е. каждый пользователь будет работать "в своем огороде".
Уровень 3. Защита листов книги
Если необходимо защититься от:
- удаления, переименования, перемещения листов в книге
- изменения закрепленных областей ("шапки" и т.п.)
- нежелательных изменений структуры (сворачивание строк/столбцов при помощи кнопок группировки "плюс/минус")
- возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel
то вам необходима защита всех листов книги, с помощью кнопки Защитить книгу (Protect Workbook) на вкладке Рецензирование (Reveiw) или - в старых версиях Excel - через меню Сервис - Защита - Защитить книгу (Tools - Protection - Protect workbook) :
Уровень 4. Шифрование файла
При необходимости, Excel предоставляет возможность зашифровать весь файл книги, используя несколько различных алгоритмов шифрования семейства RC4. Такую защиту проще всего задать при сохранении книги, т.е. выбрать команды Файл - Сохранить как (File - Save As) , а затем в окне сохранения найти и развернуть выпадающий список Сервис - Общие параметры (Tools - General Options) . В появившемся окне мы можем ввести два различных пароля - на открытие файла (только чтение) и на изменение:
Обычно мы можем скопировать данные из открывающейся книги, а затем вставить в другую книгу. Однако иногда вам может потребоваться извлечь данные из закрытой книги, не открывая ее, или даже извлечь данные из других типов файлов, таких как текстовые файлы, как это сделать в Excel? В этой статье я покажу вам способ легко извлекать данные из других закрытых книг или текстовых файлов в Excel.
Легко объединяйте несколько листов / книг в один лист / книгуОбъединение десятков листов из разных книг в один может оказаться утомительным занятием. Но с Kutools for Excel's Объединить (рабочие листы и рабочие тетради) утилиту, вы можете сделать это всего за несколько кликов! Полнофункциональная бесплатная 30-дневная пробная версия!
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Извлечение данных из другой закрытой книги или текстовых файлов с помощью Kutools for Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Kutools for Excel - отличная надстройка Excel с десятками удобных утилит. Это Вставить файл в курсор Утилита может помочь вам извлечь данные из закрытой книги, текстовых файлов или любых других файлов и легко вставить в текущий рабочий лист.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
Шаг 1: Поместите курсор в ячейку, в которую вы будете вставлять извлеченные данные. В нашем примере мы выбираем ячейку D4. Затем нажмите Предприятие > Импорт / Экспорт > Вставить файл в курсор.
Шаг 2. В открывшемся диалоговом окне «Вставить файл в курсор» щелкните значок Приложения кнопку.
Внимание: Если вы хотите извлечь данные из других файлов без формул, проверьте Только значения (без формул) вариант; если вы хотите извлечь данные без форматирования, проверьте Не передавать форматирование опцию.
Шаг 3: Далее выберите файл для вставки в диалоговое окно положения курсора ячейки, откройте папку, в которой находится книга или другой файл, щелкните, чтобы выбрать файл, который вы извлечете, и щелкните значок Откройте кнопку.
Заметки:
1. Чтобы вставить данные из текстового файла, проверьте Текстовые файлы выбирается за Имя файла в диалоговом окне Выберите файл для вставки в положение курсора ячейки.
2. Если вы выберете книгу с несколькими листами, диалоговое окно Select a Worksheet будет выведено. Просто щелкните, чтобы выбрать лист, из которого вы будете извлекать данные, и щелкните значок Ok кнопку.
Теперь вы увидите, что все данные в указанном листе извлечены и вставлены в текущий лист.
Демонстрация: извлечение данных из одной закрытой книги или текстового файла в Excel
Извлечение данных из нескольких закрытых книг в Excel
С Kutools для Excel Комбинируйте рабочие листы и книги вы можете легко извлекать данные из нескольких листов и книг и помещать извлеченные данные на один лист. Пожалуйста, сделайте следующее:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
1. Нажмите Предприятие > Сочетать.
2. В открывшемся диалоговом окне «Объединить рабочие листы - шаг 1 из 3» установите флажок Объедините несколько листов из книг в один лист и нажмите Следующая кнопку.
3. В диалоговом окне «Объединить рабочие листы - шаг 2 из 3»:
(1) Нажмите Добавить > Файл / Папка для добавления книг, из которых вы будете извлекать данные;
(2) В Список рабочих книг раздел, пожалуйста, проверьте книги, из которых вы будете извлекать данные;
(3) В Список рабочих листов в разделе, пожалуйста, проверьте рабочие листы, из которых вы будете извлекать данные;
(4) В список рабочих листов раздел, нажмите кнопку обзора за именем каждого рабочего листа, чтобы указать диапазон, который вы извлечете из каждого рабочего листа.
(5) Щелкните значок Следующая кнопку.
4. В диалоговом окне «Комбинирование листов - шаг 3 из 3» укажите необходимое правило комбинирования и нажмите кнопку Завершить кнопку.
Теперь весь указанный диапазон из закрытых книг извлекается и помещается на один рабочий лист.
Демонстрация: извлечение данных из нескольких закрытых книг в Excel
Читайте также: