Изолиране на данните в отделен лист и използването им като опростена база данни

Безплатни 20 урока
регистрирай се и научи

Ако сте вече потребител - Регистриран съм
Видео

Тест за преминаване към следващия урок

 
Как можем да направим падащо меню с данни, които са взети от отделен лист в старите версии на Excel?
Създаваме наименован диапазон и го слагаме в полето Source при Data Validation
Създаваме референция от типа =Данни!A2:A9 и я слагаме в полето Source при Data Validation
В старите версии е невъзможно да се направи списък с данни от друг лист. Excel дава грешка.

Въпроси и отговори

  • Как се прави падащо меню с данни от друг файл?

    Диляна Арнаудова:

    Казахте, че може да се направи такова меню с данни от друг файл. Как става това?

    Цанко Дилов:

    Диляна, става по същия начин както между различни листа от един файл: с примерно два отворени файла. Пробвах го с наименован диапазон, независимо от версията на Excel.

    Диляна Арнаудова:

    Какъв точно синтаксис използваш за Sours? Пробвах по най-различни начини. Не ми позволява да отворя другият файл и да избера клетките, а когато имам наименован диапазон ми казва че не го разпознава.

    Иван Цукев:

    Става по следният начин.

    Отиваш в Formulas -> Name Manager с new дефинираш диапазон. Диапазонът ти сочи към другият файл. Прости избираш клетките, но може и да напишеш Name: DiapazonDrugFile Refers to: =[Book1.xlsx]Sheet1!$I$4:$I$6

    в Data Validation избираш List и в source задаваш =DiapazonDrugFile

    Диляна Арнаудова:

    Изписва ми че не мога да правя референции към друг файл "You may not use references to other workbooks for Data Validation criteria"

    Весела Костадинова:

    И на мен така ми изписва.

    Иван Гумнеров:

    Получава се по следния начин: Първо отваряш Name Manager, следва NEW, след това бутонът Refers to, след като натиснеш този бутон и остане New Name-Refers to вече можеш да посочиш другия файл и да зададеш диапазон следва Enter и трябва да зададеш име на диапазона, след това копираш /Ctrl+C/ адреса на новия диапазон от  Refers to и с ОК и Close затваряш Name Manager. Отваряш Data Validation и в полето Source пействаш /Ctrl+V/ адреса на новия диапазон и даваш ОК

    Иван Гумнеров:

    Обаче при мен падащото меню с данни от друг файл работи само когато и другият файл е отворен. Иначе е неактивно.

    Ангелина Асенова Панчева:

    Къде да намеря Name Manager в стара версия на Excel във Windos XP?

    Марина Радева:

    опитах се да направя препратка към друг отворен екселски файл по по-горе изброените начини, но нещо не ми се получава


    Марина Радева:

    или това ми се получава

    Иван Цукев:

    ако не се лъжа, това е проблем на по-старата версия, която имаш

    Марина Радева:

    Благодаря!

    Iva Nikolova:

    И при мен не се получи с използване на диапазон от друг файл, но пак ще пробвам. С 2013 съм.

    инж.Красимира Пеновска:

    По начина на Иван Гумнеров се получава, наистина само когато са отворени и двата файла.Благодаря за разяснението, много ми помогна.

    Цветелин Стоянов:

    На мен не ми се получава и по двата начина. излизат ми същите предупреждения, като при Марина Радева. Радева, ти дали си реши проблема ?:):):)

    Лилия Павлова:

    Здравейте! Информацията е много полезна, но и аз не успях да направя препратка към базата данни от друг файл, където данните са в таблица със зададено име. Пробвах и по двата начина, но не се получава. Може ли да ми помогнете?

    Лилия Павлова:

    С версия 2010 съм

    КРАСИМИРА КРЪСТЕВА:

    Нещо интересно. При избора на диапазон избрах цялата колона , така при добавяне на нов ред в списъка автоматично се появява и не е необходимо да изпълняваме допълнителни функции за добавяне на нови редове.В диапазона за данни се изписва  =Данни!$B:$B 

    Теменужка Тасева:

    Здравейте и при мен не се получи  с препратка към база данни от друг файл.

    Видя ми се много сложно, по точно по-дълго да променям диапазона  при прибавяне на нови имена и затова пробвах с вмъкване на нови редове и новите имена автоматично се включиха в списъка ,но не зная дали това е правилен начин.Работя с версия 2010

    Боряна Романо:

    Много благодаря на КРАСИМИРА КРЪСТЕВА! Това е най-лесният начин. Работи и при версия 2007.


    Everflow:
    Microsoft Office Professional Plus 2013

    Предложеният от г-н Цукев вариант за ползване на диапазон от друг Workbook е синтактично логичен/правилен, но за съжаление не работи :( 

    И при мен излиза предупреждение, че този тип референция не може да се ползва при Data Validation.
    инж. Милена Дамянова:

    Към Красимира Кръстева - аз също постъпих като вас и зададох на диапазона цялата колона и при добавянето на ново име то се появи автоматично в списъка. Обаче открих следния проблем - когато в полето напиша име, което не е в списъка, Excel го приема и го записва в клетката вместо да ме предупреди, че  това е невалидна стойност и да не ми позволи да я запиша. Тъй като този начин на избор на диапазон е наистина по-удобен бих се радвала, ако споделите решение на проблема ми.

    Mila Georgieva-Petrova:

    Супер сте! Най-накрая се получи! Вер 2007

    Diana Siteva:

    Не е необходимо да се избира колоната за диапазон, за да влизат новите данни автоматично. Необходимо е да се вмъкне ред между редовете на диапазона и той се разширява автоматично.

    Вера Стоянова Райчева:

    И при мен нищо не се получава. Изобщо не иска да ми маркира диапазон,нито от друг файл, нито от друг Sheet. И съм с версия от 2003г. Моля, помогнете!

    Камелия Славова:

    И при мен при опит да използвам диапозон от друг фал се появява грешката: This type of reference cannot be used in a Data Validation formulas. Метода, посочен от Иван Гумнеров, също не работи при мен. Някой намери ли решение?

    Никола Петров:

    Може да се използва и функцията Consolidate Тя обобщава(консолидира) данни от различни, но отворени файлове. Най - важна е отметката (стрелка9), която позволява при промяна на данните, те автоматично да се променят и в консолидираната таблица. Дано е станало ясно! Лек ден и успехи!

    Зоя Желязкова :

    Хареса ми въпроса и всички коментари по него.Освен урока научих много неща около него.Благодаря на всички.

  • Краси Кръстев:

    Здравей, защото просто базата данни, не е на друг лист ( а не както ти си направила в отделен файл ) както е показано и в урока за това падащо меню, ще си спестиш проблема със задължителното отваряне и на втория файл.

    Бригита:

    Благодаря за отговора, просто исках да пробвам и този вариант, когато базата данни са на друг файл.

    Tania:

    При мен не е на друг файл, а на друг шийт, но пак не се получава. Падащото меню не е активно след като затворя и отново отворя файла. Все едно след всяко отваряне наново да избирам DATA, Data Validatoinq, и т. н. Явно нещо пропускам, но какво е ?

    Maria:

    Здравейте! Тази функция според мен е приложима само и единствено между листа в един работен файл и не работи между отделни файлове.

  • Как да използваме входдните данни от таблица (форматиран като таблица) вместо от диапазон?

    Ивайло Миланов:

    Въпросът ми е следният. Не можем ли, вместо да избираме диапазон, да форматираме входдните данни като таблица и да използваме някоя от колоните в таблицата. в Цел: Така диапазона автоматично ще се разширява, но и ще стои в интервала на таблицата. На снимка показвам форматиране на таблицата ,,Входни данни'' На снимка 2 се опитвам да задам формулата , но явно нещо бъркам.

    Kocho:

    Аз лично не разбирам въпроса. Какво се опитваш да направиш?

    Димитринка Вълкова:

    Ивайло, Съобщението за грешка се получава, защото формулата не е пълна за Data Validation --> List (Списък) Ето какво трябва да се въведе: =INDIRECT("ВходниДанни[Име]") Функция Indirect има един аргумент, който трябва да бъде име, т.е. текст (затова името на таблицата и колоната са заградени в кавички). Резултатът, който връща е списък със стойностите, включени в диапазона със зададеното име. С една дума резултатът се връща индиректно за разлика от повечето функции, в които връщат директно резултата (референциите, които са включени в аргументите им). Смятам, че това ще реши проблема, но ако има още нещо, пишете.

    Ивайло Миланов:

    Стана перфектно. Благодаря много :).

  • А защо трябва да се избира диапазон вместо цяла колона?

    Драгомир Кулов:

    Ако изберем колона можем да вписваме нови данни без да се налага ново дефиниране на диапазона.Изпробвано и работи!!

    Kocho:

    Абе че работи - работи. Но аз по лошия начин се научих, че така не се прави. Едно е VLOOKUP или SUMIF да търси в 15 хиляди клетки - друго е в 1 милион.

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

    Но щом ти харесва - дерзай :)