Як у Excel зробити комірку обов'язковою для заповнення?

Коли ви надаєте книгу іншим користувачам для проведення опитування, для якого, наприклад, потрібна реєстрація цього імені, кожен досліджуваний користувач повинен ввести своє ім'я в B1. я уявляю VBA, щоб зробити певний осередок обов'язковим перед закриттям книги.

Зробіть комірку обов'язковою для введення за допомогою VBA

1. Увімкніть книгу, яка містить обов'язковий осередок, і натисніть Alt + F11 ключі для відкриття Microsoft Visual Basic для програм вікно.

2. в Проект панель, двічі клацніть Цей робочий зошитта виберіть Workbook і Дозакрити з правого списку розділів, а потім вставте в скрипт наведений нижче код.

VBA: зробити осередок обов'язковим

If Cells(1, 2).

3. Потім збережіть цей код і закрийте спливаюче вікно.

Функції: Ви можете змінити комірку B1 на інші комірки на свій розсуд.

Найкращі інструменти для офісної роботи

Поліпшіть свої навички роботи з Excel за допомогою Kutools for Excel та відчуйте ефективність, як ніколи раніше. Kutools for Excel пропонує більше 300 розширених функцій для підвищення продуктивності та економії часу Натисніть тут, щоб отримати функцію, яка вам потрібна найбільше.

Вкладка Office: інтерфейс із вкладками в Office та спрощення роботи

• Увімкнення редагування та читання з вкладками Word, Excel, PowerPoint , Видавець, доступ, Visio та проект.
• Відкривайте та створюйте кілька документів на нових вкладках одного вікна, а не у нових вікнах.
• Підвищує вашу продуктивність на 50% та скорочує кількість клацань мишею на сотні щодня!

Як у Excel зробити комірку обов'язковою для заповнення?

Як у Excel зробити комірку обов'язковою для заповнення? Щоб, наприклад, не можна було зберегти файл, якщо комірка не заповнена.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If Sheets(1).[A1] = "" Then MsgBox "Коробку A1 першого листа необхідно заповнити!" Cancel = True End If End Sub

Все про роботу з excel, word, access, powerpoint

Знахідка для тих, чиї дівчата та подружжя працюють у сфері послуг: манікюр, брови, вії тощо.
🤔 Ви ж, напевно, замислювалися, як допомогти своїй половинці заробляти більше? Але що робити, якщо у всіх цих маркетингах та процедурах не розумієшся від слова «зовсім»? Ми знайшли вихід. це сервіс VisitTime
Чат-бот для майстрів та спеціалістів, який спрощує ведення записів:
— Сам записує клієнтів та нагадує їм про візит
— Персоналізує знижки, чайові, кешбек та передоплати
— Збільшує дохідність та допомагає більше заробляти
А ще там перший місяць безкоштовнотому краще, що ви можете зробити зараз - встановити або показати його своїй принцесі Все інтуїтивно зрозуміло і просто, достатньо натиснути на цей текст і запустити чат-бота

Як в excel зробити осередок обов'язковим для заповнення?

Для таблиць, які використовують постійні та повторювані дані (наприклад, прізвища співробітників, номенклатура товару або відсоток знижки для клієнта), щоб не тримати в голові і не помилитися при наборі, існує можливість один раз створити стандартний список і при підстановці даних робити вибірку з нього. Дана стаття дозволить вам використовувати 4 різних способи як в екселі зробити список, що випадає.

Спосіб 1 - гарячі клавіші і список, що розкривається в excel

Даний спосіб використання списку, що випадає, по суті не є інструментом таблиці, який треба як або налаштовувати або заповнювати. Це вбудована функція (гарячі клавіші), яка працює завжди. При заповненні будь-якого стовпця, ви можете натиснути правою кнопкою миші на порожній комірці і у випадаючому списку вибрати пункт меню «Вибрати зі списку, що розкривається».

Цей пункт меню можна запустити поєднанням клавіш Alt+"Стрілка вниз" і програма автоматично запропонує у списку значення комірок, які ви раніше заповнювали даними. На зображенні нижче програма запропонувала 4 варіанти заповнення (дані Excel не показує). Єдина умова роботи даного інструменту - це між осередком, в який ви вводите дані зі списку і самим списком не повинно бути порожніх осередків.

Використання гарячих клавіш для розкриття списку даних, що випадає

При чому список для заповнення у такий спосіб працює як у комірці знизу, так і в комірці зверху.Для верхнього осередку програма візьме зміст списку з нижніх значень. І знову ж таки не повинно бути порожнього осередку між даними і осередком для введення.

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

Спосіб 2 - найзручніший, найпростіший і найбільш гнучкий

Цей спосіб передбачає створення окремих даних для списку. При чому дані можуть бути як на аркуші з таблицею, так і на іншому аркуші файлу Excel.

1. Спочатку необхідно створити список даних, який буде джерелом даних для підстановки в список, що випадає в excel. Виділіть дані та натисніть правою кнопкою миші. У списку виберіть пункт «Присвоїти ім'я…». Створення набору даних для списку
2. У вікні «Створення імені» задайте ім'я для вашого списку (це ім'я буде використовуватися у формулі підстановки). Ім'я має бути без прогалин і починатися з літери. Введіть ім'я для набору даних
3. Виділіть осередки (можна відразу кілька осередків), в яких планується створити список, що випадає. У вкладці «ДАНІ» зверху документа натисніть «Перевірка даних». Створити список, що випадає, можна відразу для декількох осередків
4. У вікні перевірка введених значення як тип даних задайте «Список». У рядку «Джерело:» введіть знак і ім'я для раніше створеного списку. Ця формула дозволить запровадити значення лише зі списку, тобто. здійснить перевірку введеного значення та запропонує варіанти. Ці варіанти і будуть списком, що випадає.

Щоб створити перевірку введених значень, введіть ім'я раніше створеного списку

При спробі ввести значення, якого немає в цьому списку, ексель видасть помилку.

Крім списку, можна вводити дані вручну. Якщо введені дані не збігатимуться з одним із даних — програма видасть помилку

А при натисканні на кнопку випадаючого списку в осередку ви побачите перелік значень із створеного раніше.

Спосіб 3 — як у excel зробити список, що випадає з використанням ActiveX

Щоб скористатися цим способом, необхідно, щоб у вас була включена вкладка «Розробник». За промовчанням ця вкладка відсутня. Щоб її увімкнути:

1. Натисніть «Файл» у верхньому лівому куті програми.
2. Виберіть «Параметри» та натисніть на нього.
3. У вікні параметрів Excel у вкладці «Налаштувати стрічку» поставте галочку навпроти вкладки «Розробник».

Увімкнення вкладки «РОЗРОБНИК»

Тепер ви зможете скористатися інструментом "Поле зі списком (Елемент ActiveX)". У вкладці «Розробник» натисніть кнопку «Вставити» і знайдіть в елементах ActiveX кнопку «Поле зі списком (Елемент ActiveX)». Натисніть на неї.

Намалюйте даний об'єкт в excel список, що випадає в осередку, де вам необхідний список, що випадає.

Тепер потрібно налаштувати цей елемент. Щоб це зробити, необхідно увімкнути «Режим конструктора» та натиснути на кнопку «Властивості». У вас має відкритися вікно властивостей (Properties).

З відкритим вікном властивостей натисніть раніше створений елемент «Поле зі списком». У списку властивостей дуже багато параметрів для налаштування і ви зможете вивчивши їх, налаштувати дуже багато, починаючи від відображення списку до спеціальних властивостей даного об'єкта.

Але нас на етапі створення цікавлять лише три основні:

1. ListFillRange - Вказує діапазон осередків, з яких будуть братися значення для списку, що випадає. У моєму прикладі я вказав два стовпці (A2:B7 — далі покажу, як це використовувати). Якщо потрібно лише одне значення вказується A2:A7.
2. ListRows — кількість даних у списку, що випадає.Елемент ActiveX відрізняється від першого способу тим, що можна вказати велику кількість даних.
3. ColumnCount — вказує скільки стовпців даних вказувати у списку, що випадає.

У рядку ColumnCount я вказав значення 2 і тепер у списку випадають дані виглядають ось так:

Як бачите вийшов список, що випадає в excel з підстановкою даних з другого стовпця з даними «Постачальник».

Список, що випадає в Excel це, мабуть, один з найзручніших способів роботи з даними. Використовувати їх можна як при заповненні форм, так і створюючи дашборди та об'ємні таблиці. Списоки, що випадають, часто використовують у додатках на смартфонах, веб-сайтах. Вони інтуїтивно зрозумілі пересічному користувачеві.

Клацніть по кнопці нижче для завантаження файлу з прикладами списків, що випадають в Excel:

Відео-урок Як створити список, що випадає в Екселі на основі даних з переліку

Припустимо, що у нас є перелік фруктів:

Для створення списку, що випадає, нам потрібно зробити наступні кроки:

• Вибрати осередок, в якому ми хочемо створити список, що випадає;
• Перейти на вкладку “Дані” => розділ “Робота з даними” на панелі інструментів => вибираємо пункт “Перевірка даних“.

• У спливаючому вікні “Перевірка значень, що вводяться” на вкладці “Параметри” у типі даних вибрати “Список“:

• У полі “Джерело” ввести діапазон назв фруктів =$A$2:$A$6 або просто поставити курсор миші у поле введення значень “Джерело” і потім мишкою вибрати діапазон даних:

Якщо ви хочете створити списки, що випадають, у кількох осередках за раз, виберіть всі осередки, в яких ви хочете їх створити, а потім виконайте вказані вище дії. Важливо переконатися, що посилання на комірки є абсолютними (наприклад, $A$2), а не відносними (наприклад, A2 або A$2 або $A2).

Як зробити список, що випадає в Excel використовуючи ручне введення даних

На прикладі вище, ми вводили список даних для випадаючого списку шляхом виділення діапазону осередків.

Наприклад, уявимо, що у випадаючому меню ми хочемо відобразити два слова “Так” і “Ні”.

• Вибрати осередок, в якому ми хочемо створити список, що випадає;
• Перейти на вкладку “Дані” => розділ “Робота з даними” на панелі інструментів => вибрати пункт “Перевірка даних“:

• У спливаючому вікні “Перевірка значень, що вводяться” на вкладці “Параметри” у типі даних вибрати “Список“:

• У полі “Джерело” ввести значення “Так;
• Натискаємо “ОК“

Після цього система створить список, що розкривається, у вибраній комірці.Джерело“, розділені точкою з комою будуть відображені в різних рядках меню.

Якщо ви хочете одночасно створити список, що випадає, в декількох осередках - виділіть потрібні осередки і дотримуйтесь інструкцій вище.

Як створити список, що розкривається, в Ексель за допомогою функції ЗМІЩ

Поряд зі способами описаними вище, ви також можете використовувати формулу ЗМІЩ для створення списків, що випадають.

Наприклад, у нас є список із переліком фруктів:

Для того щоб зробити список, що випадає, за допомогою формули ЗМІЩ необхідно зробити наступне:

• Вибрати осередок, в якому ми хочемо створити список, що випадає;
• Перейти на вкладку “Дані” => розділ “Робота з даними” на панелі інструментів => вибрати пункт “Перевірка даних“:

• У спливаючому вікні “Перевірка значень, що вводяться” на вкладці “Параметри” у типі даних вибрати “Список“:

• У полі “Джерело” ввести формулу: =ЗМІЩ(A$2$;0;0;5)
• Натиснути “ОК“

Система створить список, що випадає, з переліком фруктів.

Як ця формула працює?

На прикладі вище ми використали формулу =ЗМІЩ(посилання;сміщ_по_рядків;сміщ_по_стовпцям;;).

Ця функція містить у собі п'ять аргументів. /стовпчиків потрібно зміщуватися для відображення даних. висоту діапазону осередків. Аргумент “” ми не вказуємо, тому що в прикладі діапазон складається з однієї колонки.

Використовуючи цю формулу, система повертає вам як дані для списку, що випадає, діапазон осередків, що починається з осередку $A$2, що складається з 5 осередків.

Як зробити список в Excel з підстановкою даних (з використанням функції ЗМІЩ)

Якщо ви використовуєте для створення списку формулу СМЕЩ на прикладі вище, то ви створюєте список даних, зафіксований у певному діапазоні осередків. список, в який будуть автоматично завантажуватися нові дані для відображення.

Для створення списку потрібно:

• Вибрати осередок, в якому ми хочемо створити список, що випадає;
• Перейти на вкладку “Дані” => розділ “Робота з даними” на панелі інструментів => вибрати пункт “Перевірка даних“;
• У спливаючому вікні “Перевірка значень, що вводяться” на вкладці “Параметри” у типі даних вибрати “Список“;
• У полі “Джерело” ввести формулу: =ЗМІЩ(A$2$;0;0;РАХУНКИ($A$2:$A$100;””))
• Натиснути “ОК“

У цій формулі, в аргументі “” ми вказуємо як аргумент, що означає висоту списку з даними – формулу РАХУНКИ, яка розраховує в заданому діапазоні A2:A100 кількість не порожніх осередків.

Примітка: Для коректної роботи формули, важливо, щоб у списку даних для відображення у меню, що випадає, не було порожніх рядків.

Як створити список, що випадає в Excel з автоматичною підстановкою даних

Для того щоб у створений вами список, що випадає, автоматично підвантажувалися нові дані, потрібно виконати такі дії:

• Створюємо список даних для відображення у списку, що випадає. У нашому випадку це список кольорів. Виділяємо список лівою кнопкою миші:

• На панелі інструментів натискаємо пункт “Форматувати як таблицю“:

• Натиснувши клавішу “ОК” у спливаючому вікні, підтверджуємо обраний діапазон осередків:

• Потім, виділимо діапазон даних таблиці для списку, що випадає, і привласним йому ім'я в лівому полі над стовпцем "А":

Таблиця з даними готова, тепер можемо створювати список, що випадає. Для цього необхідно:

• Вибрати осередок, в якому ми хочемо створити список;
• Перейти на вкладку “Дані” => розділ “Робота з даними” на панелі інструментів => вибрати пункт “Перевірка даних“:

• У спливаючому вікні “Перевірка значень, що вводяться” на вкладці “Параметри” у типі даних вибрати “Список“:

• У полі джерело вказуємо = "назва вашої таблиці". У нашому випадку ми її назвали “Список“:

• Готово! Створений випадаючий список, у ньому відображаються всі дані із зазначеної таблиці:

• Для того щоб додати нове значення в список, що випадає - просто додайте в наступну після таблиці з даними комірку інформацію:

• Таблиця автоматично розширить діапазон даних.Список, що випадає, відповідно поповниться новим значенням з таблиці:

Як скопіювати список, що випадає в Excel

В Excel є можливість копіювати створені списки, що випадають. Наприклад, в осередку А1 у нас є список, що випадає, який ми хочемо скопіювати в діапазон осередків А2: А6.

Для того щоб скопіювати список з поточним форматуванням:

• натисніть лівою клавішею миші на комірку зі списком, що випадає, яку ви хочете скопіювати;
• натисніть клавіші на клавіатурі CTRL+C;
• виділіть комірки в діапазоні А2: А6, в які ви хочете вставити список, що випадає;
• натисніть клавіші на клавіатурі CTRL+V.

Так, ви скопіюєте список, що випадає, зберігши вихідний формат списку (колір, шрифт і.т.д). Якщо ви хочете скопіювати/вставити список, що випадає без збереження формату, то:

• натисніть лівою клавішею миші на комірку зі списком, що випадає, який ви хочете скопіювати;
• натисніть клавіші на клавіатурі CTRL+C;
• виберіть комірку, в яку ви хочете вставити список, що випадає;
• натисніть праву кнопку миші => викличте меню, що випадає, і натисніть “Спеціальна вставка“;

• У вікні в розділі “Вставити” виберіть пункт “умови на значення“:

• Натисніть “ОК“

Після цього, Ексель скопіює лише дані списку, не зберігаючи форматування вихідного осередку.

Як виділити всі осередки, що містять список, що випадає в Екселі

Іноді, складно зрозуміти, скільки комірок у файлі Excel містять випадають списки. Є простий спосіб відобразити їх. Для цього:

• Натисніть на вкладку “Головна” на панелі інструментів;
• Натисніть “Знайти та виділити” і виберіть “Виділити групу осередків“:

• У діалоговому вікні виберіть “Перевірка даних“. У цьому полі є можливість вибрати пунктиУсіх” та “Цих же“. “Усіх” дозволить виділити всі списки, що випадають на аркуші. Пункт “цих же” покаже списки, що випадають, схожі за змістом даних у випадаючому меню. У нашому випадку ми обираємо “всіх“:

• Натисніть “ОК“

Натиснувши “ОК“, Excel виділить на аркуші всі осередки з списком, що випадає. Так ви зможете привести за все списки до загального формату, виділити межі і.т.д.

Як зробити залежні випадають списки в Excel

Іноді нам потрібно створити кілька списків, що випадають, причому, таким чином, щоб, вибираючи значення з першого списку, Excel визначав які дані відобразити в другому списку.

Припустимо, що у нас є списки міст двох країн: Росія та США:

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

• Створити два іменовані діапазони для осередків “A2:A5” з ім'ям “Росія” та для осередків “B2:B5” з назвою “США”. Для цього нам потрібно виділити весь діапазон даних для списків, що випадають:

• Перейти на вкладку “Формули” => клікнути у розділі “Певні імена” на пункт “Створити із виділеного“:

• У спливаючому вікні “Створення імен із виділеного діапазону” поставте галочку в пункт “у рядку вище“. Зробивши це, Excel створить два іменовані діапазони "Росія" та "США" зі списками міст:

• Натисніть “ОК“
• У осередку “D2” створіть список, що випадає, для вибору країн “Росія” або “США”. Так, ми створимо перший список, що випадає, в якому користувач зможе вибрати одну з двох країн.

Тепер, для створення залежного списку, що випадає:

• Виділіть комірку E2 (або будь-який інший осередок, в якому ви хочете зробити залежний список, що випадає);
• Клацніть по вкладці “Дані” => “Перевірка даних”;
• У спливаючому вікні “Перевірка значень, що вводяться” на вкладці “Параметри” у типі даних виберіть “Список“:

• У розділі "Джерело" вкажіть посилання: =INDIRECT($D$2) або =ДВССИЛ($D$2);

• Натисніть “ОК“

Тепер, якщо ви оберете в першому випадаючому списку країну "Росія", то в другому списку з'являться тільки ті міста, які відносяться до цієї країни.

У програмі Excel існує багато прийомів для швидкого та ефективного заповнення осередків даними. Всім відомо, що ліньки – це двигун прогресу.

На заповнення даних доводиться витрачати більшу частину часу на нудну та рутинну роботу. Наприклад, заповнення табеля обліку робочого часу або витратної накладної тощо.

Розглянемо прийоми автоматичного та напівавтоматичного заповнення в Excel. Якими інструментами володіють електронні таблиці для полегшення праці користувача.

Як у Excel заповнити осередки однаковими значеннями?

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

Це невелика табличка тільки на прикладі і її можна було заповнити вручну. Але в практиці іноді доводиться заповнювати по 30 тисяч рядків.

1. Перейдіть на будь-яку порожню комірку вихідної таблиці.
2. Виберіть інструмент: «Головна»-«Знайти та виділити»-«Перейти» (або натисніть клавіші CTRL+G).
3. У вікні, клацніть на кнопку «Виділити».
4. У вікні виберіть опцію «порожні комірки» і натисніть ОК Усі незаповнені комірки виділені.
5. Тепер введіть формулу = A1 і натисніть комбінацію клавіш CTRL + Enter. Так виконується заповнення порожніх осередків у Excel попереднім значенням – автоматично.
6. Виділіть колонки A:B та скопіюйте їх вміст.
7. Виберіть інструмент: "Головна"-"Вставити"-"Спеціальна вставка" (або натисніть CTRL+ALT+V).
8. У вікні виберіть опцію «значення» і натисніть Ок. Тепер вихідна таблиця заповнена непросто формулами, а природними значеннями осередків.

При заповненні 30 тисяч рядків неможливо не допустити помилки. Вище наведений спосіб як економить сили та час, а й виключає виникнення помилок викликаних людським чинником.

Увага! У 5-му пункті таблиця красиво заповнилася без помилок, так як наш активний осередок був за адресою A2, після виконання 4-го пункту. При використанні даного методу будьте уважні і слідкуйте за тим, де знаходиться активний осередок після виділення. Важливо, звідки вона братиме свої значення.

Напівавтоматичне заповнення осередків в Excel з списку

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

У новій вихідній таблиці автоматично заповніть колонки C і D відповідними даними.

1. Заповніть заголовки колонок C1 – «Дата» та D1 – «Тип платежу».
2. В комірку C2 введіть дату 18.07.2015
3. У осередках С2:С4 дати повторюються. Тому виділяємо діапазон С2:С4 та натискаємо комбінацію клавіш CTRL+D, щоб автоматично заповнити комірки попередніми значеннями.
4. Введіть поточну дату в комірку C5. Для цього натисніть клавішу CTRL+; (крапка з комою на англійській розкладці клавіатури).Заповніть поточними датами стовпчик C до кінця таблиці.
5. Діапазон комірок D2:D4 заповніть так, як показано нижче на малюнку.
6. У комірці D5 введіть першу буку "п", а далі слово заповнять не треба. Достатньо натиснути клавішу Enter.
7. У клітинці D6 після введення першої літери «н» не відображається частина слова для автозаповнення. Тому натисніть комбінацію ALT+(стріла вниз), щоб з'явився список, що випадає. Виберіть стрілками клавіатури або вказівником мишки значення «готівкою в касі» та натисніть Enter.

Такий напівавтоматичний спосіб введення даних дозволяє у кілька разів прискорити та полегшити процес роботи з таблицями.

Увага! Якщо значення складається з декількох рядків, то при натисканні на комбінацію ALT+(стріла вниз) воно не відображатиметься у списку значень.

Розбити значення на рядки можна за допомогою комбінації клавіш ALT+Enter. Таким чином, текст ділиться на рядки в рамках одного осередку.

Примітка. Зверніть увагу на те, як ми вводили поточну дату в пункті 4 за допомогою гарячих клавіш (CTRL+;). Це дуже зручно! При натисканні CTRL+SHIFT+; ми отримуємо поточний час.