Авторизация в excel как сделать

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

Microsoft Excel — популярная программа для работы с электронными таблицами, входящая в состав офисного пакета Microsoft Office. Программа Excel широко используется отдельными лицами и на предприятиях для работы.

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

• Защита книги — защита структуры и окон.
• Защита листа — защита листа и содержимого ячеек (подробно настраивается под конкретные потребности).
• Доступ к книге — управление доступом к книге для пользователей компьютера.
• Защита документа паролем — установка пароля для открытия документа.

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

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

В статье мы рассмотрим шифрование файлов Excel с помощью пароля в программах: Microsoft Excel 2016, Microsoft Excel 2013, Microsoft Excel 2010, Microsoft Excel 2007, Microsoft Excel 2003.

Как поставить пароль на Excel файл в Microsoft Excel 2016

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

Выполните следующие действия:

1. В открытой книге Excel войдите в меню «Файл».
2. Во вкладке «Сведения» нажмите на кнопку «Защита книги». В выпадающем меню откроются следующие настройки для защиты документа:

• Пометить как окончательный — пометка книги как окончательной и нередактируемой
• Зашифровать с использованием пароля — установка пароля для открытия данной книги
• Защитить текущий лист — управление разрешениями для внесения изменений в текущий лист книги
• Защитить структуру книги — предотвращение изменений структуры книги
• Добавить цифровую подпись — добавление невидимой цифровой подписи

1. Нажмите на пункт «Зашифровать с использованием пароля».

1. В окне «Шифрование документа» введите пароль в соответствующее поле.

1. В окне «Подтверждение пароля» подтвердите введенный ранее пароль.

1. Закройте файл Excel, в окне с предупреждением нажмите на кнопку «Сохранить».

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

Как удалить пароль на открытие книги Microsoft Excel 2016

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

Снять пароль с Excel 2016 можно следующим образом:

1. Откройте книгу Excel.
2. Введите пароль для открытия документа.
3. Войдите в меню «Файл», во вкладке «Сведения» нажмите на кнопку «Защита книги».
4. Выберите «Зашифровать с использованием пароля».
5. В окне «Шифрование документа», в поле «Пароль» удалите ранее установленный пароль, а затем нажмите на кнопку «ОК».

1. Закройте книгу Excel, согласитесь с изменениями в файле.

Как запаролить файл Excel в Microsoft Excel 2013

Давайте посмотрим, как защитить паролем документ Excel в программе MS Excel 2013.

В Excel 2013 пройдите следующие шаги:

1. В открытой книге Excel войдите в меню «Файл».
2. Откройте вкладку «Сведения», а затем нажмите на кнопку «Защита книги».

1. В контекстном меню выберите «Зашифровать с использованием пароля».
2. В окне «Шифрование документа» введите пароль в поле «Пароль», нажмите на кнопку «ОК».
3. Подтвердите пароль в следующем окне.
4. Во время закрытия документа Excel, подтвердите согласие на изменение файла.

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

Как снять защиту Excel в Microsoft Excel 2013

Если больше не нужна защита книги Excel паролем, удалите пароль на открытие документа в MS Excel 2013.

Для снятия защиты паролем в Excel 2013, пройдите следующие шаги:

1. Кликните по файлу Excel для его открытия.
2. В открывшемся окне «Введите пароль» вставьте пароль для открытия файла.
3. Перейдите в меню «Файл», откройте вкладку «Сведения», щелкните по кнопке «Защита книги».
4. Нажмите на пункт «Зашифровать с использованием пароля».
5. В окне «Шифрование документа» удалите пароль, нажмите на кнопку «ОК».
6. Во время закрытия файла Excel, согласитесь на изменения.

Как поставить пароль на документ Excel в Microsoft Excel 2010

При работе в MS Excel 2010 можно защитить Excel паролем для ограничения доступа к файлу.

Проделайте следующее в Excel 2010:

1. В книге Excel войдите в меню «Файл», откройте раздел «Сведения».
2. Щелкните по кнопке «Защитить книгу».
3. В открывшемся контекстном меню выберите «Зашифровать паролем».

1. В окне «Шифрование содержимого этого файла» введите пароль.
2. В окне «Подтверждение пароля» снова введите пароль.
3. Закройте документ Excel, сделанные изменения.

Теперь, при открытии книги Excel потребуется вводить пароль.

Как снять защиту паролем в Excel в программе Microsoft Excel 2010

Снять пароль с Excel в Excel 2010 нужно следующим образом:

1. Откройте книгу Excel, введите пароль.
2. Перейдите в меню «Файл», далее откройте «Сведения».
3. Нажмите на кнопку «Защитить книгу», выберите «Зашифровать паролем».
4. В окне «Шифрование содержимого этого файла» удалите пароль.
5. Закройте документ Excel, подтвердите изменения.

Как поставить пароль на Excel в Microsoft Excel 20007

Пользователь может поставить пароль на файл Excel в программе MS Excel 2007.

В Excel 2007 сделайте следующее:

1. В открытой книге Excel нажмите на кнопку «Microsoft Office».
2. Выберите «Подготовить», а затем в открывшемся меню нажмите на пункт «Зашифровать документ».

1. Откроется окно «Шифрование документа», в котором необходимо ввести пароль.
2. Подтвердите пароль в следующем окне «Подтверждение пароля».
3. Закройте файл Excel, а в окне «Сохранить изменения в файле» нажмите на кнопку «Сохранить».

Как убрать пароль на открытие файла Excel в Microsoft Excel 2007

Для удаления пароля с файла Excel 2007 выполните последовательные действия:

1. Откройте книгу Excel на компьютере, введите пароль для открытия документа.
2. Нажмите на кнопку «Microsoft Office».
3. Сначала нажмите «Подготовить», а затем «Зашифровать документ».
4. В окне «Шифрование документа» удалите пароль, нажмите на кнопку «ОК».
5. Закройте книгу, примените изменения для файла Excel.

Как запаролить Excel в Microsoft Excel 2003

В программе MS Excel 2003 существует возможность для установки пароля на открытие документа Excel.

В программе Excel 2003 проделайте следующее:

1. В открытом документе Excel войдите в меню «Сервис», выберите «Параметры».
2. В окне «Параметры» откройте вкладку «Безопасность».
3. В поле «Пароль для открытия» введите пароль, а затем нажмите на кнопку «ОК».

1. В окне «Подтверждение пароля» введите пароль еще раз.
2. Закройте документ, подтвердите изменения в файле Excel.

Как снять пароль с файла Excel в Microsoft Excel 2003

Снятие пароля в Excel в программе Excel 2003 проходит следующим способом:

1. Откройте книгу Excel, введите пароль от документа.
2. Перейдите в меню «Сервис», откройте «Параметры».
3. В окне «Параметры» войдите во вкладку «Безопасность».
4. В поле «Пароль для открытия» удалите пароль, нажмите на кнопку «ОК».
5. Закройте книгу Excel, изменения в файле.

Выводы статьи

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

Источник: https://vellisa.ru/parol-na-fayl-excel

Как снять пароль в Excel? Три рабочих способа снятия пароля

Способ 1. (Используем программу) Ищем в поисковике и загружаем программу

Например, мы имеем много рабочих книг Excel, и мы хотим

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

Достаточно часто при заполнении ячейки текстом, возникает необходимость ввести текст

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

Предположим, что у нас есть такая таблица с перечнем соглашений,

В Excel есть одна интересная особенность, а именно возможность вводить

Многие сталкивались с файлами Ексель,  в которых создано огромное количество

Источник: https://excel.su/kak-snyat-parol-v-excel-tri-rabochih-sposoba-snyatiya-parolya/

Отслеживание входа пользователей в книгу Excel

65227 19.04.2014 Скачать пример

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

Одним совсем не прекрасным утром вы открываете этот файл и обнаруживаете, что внутри кто-то поиграл в аль-каиду: формулы поломаны, данные стерты, дизайн нарушен.

Поскольку вы опытный пользователь, то у вас, конечно же, была резервная копия этого важного документа (была же, правда?) и данные вы восстановите, но чисто из спортивного интереса хотелось бы все же узнать — КТО ЭТО СДЕЛАЛ?!

Давайте попробуем решить эту задачу. Итак, нам нужно:

• фиксировать на отдельном (скрытом) листе имя пользователя, открывшего файл;
• фиксировать там же дату-время открытия и закрытия файла;
• по возможности усложнить взлом или обход такой защиты.

Поехали…

Этап 1. Создаем «Лог»

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

Этап 2. Макросы фиксации входа-выхода

Теперь добавим макросы для записи на лист Лог даты-времени и имен пользователей при открытии и закрытии книги.

Для этого нужно открыть редактор Visual Basic с помощью сочетания Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) и найти в левом верхнем углу панель Project (если она не отображается, то включить ее можно сочетанием клавиш Ctrl+R):

Двойным щелчком откройте модуль ЭтаКнига (ThisWorkbook) и вставьте туда пару наших макросов для обработки событий открытия и закрытия книги:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ищем последнюю занятую строчку в логах
lastrow = Worksheets(«Лог»).Range(«A60000»).End(xlUp).Row
'заносим дату-время выхода из файла
If lastrow>1 Then Worksheets(«Лог»).Cells(lastrow, 3) = Now
'сохраняемся перед выходом
ActiveWorkbook.Save
End Sub

Private Sub Workbook_Open()
'ищем последнюю занятую строчку в логах
lastrow = Worksheets(«Лог»).Range(«A60000»).End(xlUp).Row
'заносим имя пользователя и дату-время входа в файл
Worksheets(«Лог»).Cells(lastrow + 1, 1) = Environ(«USERNAME»)
Worksheets(«Лог»).Cells(lastrow + 1, 2) = Now
End Sub

В первом приближении все уже должно работать. Попробуйте открыть-закрыть этот файл пару раз и убедитесь, что на лист Лог попадает ваше имя пользователя (логин входа в Windows) и дата-время:

Этап 3. Улучшаем надежность

Можно было бы скрыть лист Лог и на этом остановиться, но есть одно «но»: если у пользователя, который открывает нашу книгу, макросы разрешены по умолчанию либо он сам их разрешает, нажав в окне предупреждения на кнопку Включить содержимое, то все в порядке:

Но что если пользователь не разрешит выполнение макросов или они отключены у него по умолчанию? Тогда наши макросы отслеживания выполняться не будут и фиксации имени и даты не произойдет 🙁 Как же заставить пользователя разрешить использование макросов?

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

Суть в том, чтобы по умолчанию скрыть в книге все листы кроме этого, а рабочие листы с данными отображать с помощью специального макроса. Если пользователь не разрешил выполнение макросов, то он увидит в книге только один лист с предупреждением.

Если же макросы разрешены, то наш макрос обработки события открытия книги скроет лист с предупреждением и отобразит листы с данными.

Чтобы пользователь сам не отобразил их — используем суперскрытие вместо обычного скрытия листов (параметр xlSheetVeryHidden вместо обычного False).

Чтобы реализовать все описанное, слегка изменим наши процедуры в модуле ЭтаКнига (ThisWorkbook):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ищем последнюю занятую строчку в логах
lastrow = Worksheets(«Лог»).Range(«A60000»).End(xlUp).Row
'заносим дату-время выхода из файла
If lastrow > 1 Then Worksheets(«Лог»).Cells(lastrow, 3) = Now

'скрываем все листы, кроме листа ПРЕДУПРЕЖДЕНИЕ
Worksheets(«Предупреждение»).Visible = True
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = «Предупреждение» Then
sh.Visible = True
Else
sh.Visible = xlSheetVeryHidden
End If
Next sh

'сохраняемся перед выходом
ActiveWorkbook.Save
End Sub

Private Sub Workbook_Open()
'ищем последнюю занятую строчку в логах
lastrow = Worksheets(«Лог»).Range(«A60000»).End(xlUp).Row
'заносим имя пользователя и дату-время входа в файл
Worksheets(«Лог»).Cells(lastrow + 1, 1) = Environ(«USERNAME»)
Worksheets(«Лог»).Cells(lastrow + 1, 2) = Now

'отображаем все листы
For Each sh In ActiveWorkbook.Worksheets
sh.Visible = True
Next sh
'скрываем листы ПРЕДУПРЕЖДЕНИЕ и ЛОГ
Worksheets(«Предупреждение»).Visible = xlSheetVeryHidden
Worksheets(«Лог»).Visible = xlSheetVeryHidden

End Sub

Чтобы просмотреть скрытый Лог откройте редактор VisualBasic (Alt+F11), выделите лист на панели Project и измените его видимость на панели Properties, используя свойство Visible:

Если пользователи настолько продвинутые, что знают про суперскрытые листы и могут их отобразить через редактор Visual Basic или нарушить работу наших макросов, то можно дополнительно поставить пароль на просмотр и изменение макросов.

Для этого щелкните правой кнопкой мыши по имени файла в панели Project (строка VBAProject (blackbox.

xls)), выберите команду VBA Project Properties и включите флажок Lock project for viewing и задайте пароль на вкладке Protection:

Теперь точно никто не уйдет безнаказанным. Большой Брат следит за тобой! 😉

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/5/196/

Как при помощи простых функций Excel создать генератор паролей | Сводные таблицы Excel 2010

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

Сделать это в Excel можно при помощи пары простых функций:

• функция СЛУЧМЕЖДУ (RANDBETWEEN) – генерирует случайное число в заданном диапазоне;
• функция СИМВОЛ (CHAR) – выводит символ с заданным кодом.

По внутренней компьютерной кодировке английские прописные буквы имеют коды символов с 65 по 90, строчные – с 97 по 122. Поэтому, например, формула =СИМВОЛ(СЛУЧМЕЖДУ(65;90)) будет выводить случайную прописную букву английского алфавита. Соответственно, если нам нужна строка из таких символов, то необходимо использовать несколько таких функций, склеенных с помощью символа &.

Как при помощи простых функций Excel создать генератор паролей

В английском варианте это будет =CHAR(RANDBETWEEN(65;90)).

Если же необходимо создавать более сложные пароли (с символами разного регистра, цифрами и др.), то проще будет использовать специальную макрофункцию. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert → Module) и введите туда ее текст.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Public Function GeneratePassword(Optional Lenght As Integer = 8) For s = 1 To Lenght choice = Int(Rnd * 3) If choice = 0 Then nextsymbol = Int(Rnd * 9) End If If choice = 1 Then nextsymbol = Chr(Int((90 — 65 + 1) * Rnd + 65)) End If If choice = 2 Then nextsymbol = Chr(Int((122 — 97 + 1) * Rnd + 97)) End If PassTxt = PassTxt & nextsymbol Next s GeneratePassword = PassTxt End Function

Public Function GeneratePassword(Optional Lenght As Integer = 8) For s = 1 To Lenght choice = Int(Rnd * 3) If choice = 0 Then nextsymbol = Int(Rnd * 9) End If If choice = 1 Then nextsymbol = Chr(Int((90 — 65 + 1) * Rnd + 65)) End If If choice = 2 Then nextsymbol = Chr(Int((122 — 97 + 1) * Rnd + 97)) End If PassTxt = PassTxt & nextsymbol Next s GeneratePassword = PassTxt
End Function

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

Проще будет использовать специальную макрофункцию

Источник: http://pivot-table.ru/kak-pri-pomoshhi-prostyx-funkcij-excel-sozdat-generator-parolej.html

Авторизация в excel как сделать

Здесь можно получить ответы на вопросы по Microsoft Excel	58655	480092	13 Ноя 2018 21:31:53
44519	357825	29 Янв 2017 17:28:40
Лучшие избранные темы с основного форума	14	80	28 Июн 2018 15:25:11
Если вы — счастливый обладатель Mac ????	220	1067	13 Ноя 2018 16:52:22
Раздел для размещения платных вопросов, проектов и задач и поиска исполнителей для них.	2150	13762	13 Ноя 2018 21:03:59
Если Вы скачали или приобрели надстройку PLEX для Microsoft Excel и у Вас есть вопросы или пожелания — Вам сюда.	317	1623	13 Ноя 2018 14:37:21
824	11985	13 Ноя 2018 18:35:09
Обсуждение функционала, правил и т.д.	270	3481	30 Окт 2018 15:01:36

• Сейчас на форуме (гостей: 974, пользователей: 21, из них скрытых: 1) , , , , , , , , , , , , , , , , , , ,
• Сегодня отмечают день рождения (35), (32), (31)
• Всего зарегистрированных пользователей: 83991
• Приняло участие в обсуждении: 32205
• Всего тем: 106968

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

1. Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel:
2. Видео-урок Как создать выпадающий список в Экселе на основе данных из перечня
3. Представим, что у нас есть перечень фруктов:
4. Для создания выпадающего списка нам потребуется сделать следующие шаги:

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

• Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“:

• В поле “Источник” ввести диапазон названий фруктов =$A$2:$A$6 или просто поставить курсор мыши в поле ввода значений “Источник” и затем мышкой выбрать диапазон данных:

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

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

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

Например, представим что в выпадающем меню мы хотим отразить два слова “Да” и “Нет”. Для этого нам потребуется:

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

• Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“:

• В поле “Источник” ввести значение “Да; Нет”.
• Нажимаем “ОК“

После этого система создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в поле “Источник“, разделенные точкой с запятой будут отражены в разных строчках выпадающего меню.

• Если вы хотите одновременно создать выпадающий список в нескольких ячейках – выделите нужные ячейки и следуйте инструкциям выше.
• Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ
• Наряду со способами описанными выше, вы также можете использовать формулу СМЕЩ для создания выпадающих списков.
• Например, у нас есть список с перечнем фруктов:
• Для того чтобы сделать выпадающий список с помощью формулы СМЕЩ необходимо сделать следующее:

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

• Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“:

• В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;5)
• Нажать “ОК“

1. Система создаст выпадающий список с перечнем фруктов.
2. Как эта формула работает?
3. На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;;).

Эта функция содержит в себе пять аргументов. В аргументе “ссылка” (в примере $A$2) указывается с какой ячейки начинать смещение.

 В аргументах “смещ_по_строкам” и “смещ_по_столбцам” (в примере указано значение “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 выделит на листе все ячейки с выпадающим списком. Так вы сможете привести за раз все списки к общему формату, выделить границы и.т.д.

1. Как сделать зависимые выпадающие списки в Excel
2. Иногда нам требуется создать несколько выпадающих списков, причем, таким образом, чтобы, выбирая значения из первого списка, Excel определял какие данные отобразить во втором выпадающем списке.
3. Предположим, что у нас есть списки городов двух стран Россия и США:
4. Для создания зависимого выпадающего списка нам потребуется:

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

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

• Во всплывающем окне “Создание имен из выделенного диапазона” поставьте галочку в пункт “в строке выше“. Сделав это, Excel создаст два именованных диапазона “Россия” и “США” со списками городов:

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

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

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

• В разделе “Источник” укажите ссылку: =INDIRECT($D$2) или =ДВССЫЛ($D$2);

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

• Все, что вы хотели знать об онлайн версии Excel (Эксель) – возможности, отличия от десктопной программы, преимущества и недостатки.
• Еще несколько лет назад облачные сервисы не могли заменить полноценные десктопные программы, но сегодня онлайн версии софта стремительно набирают популярность и постепенно вытесняют стандартные приложения, которые требуют установки.
• Пакет MS Office Online – это набор средств для работы с офисными документами любых форматов.
• Редактор таблиц MS Excel тоже есть в перечне доступных сервисов.
•  С его помощью вы сможете открывать, редактировать и делиться таблицами в формате XLS, XLSX, CSV без необходимости в покупке и установке платной версии.
• Страница онлайн Экселя

Источник: http://word-office.ru/avtorizaciya-v-excel-kak-sdelat.html

Создание автоматически заполняемых списков в Excel — Центр обучения

Видеоверсия

Текстовая версия

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

Записи в перечне слева «сырые», т.е. они не сгруппированы, содержат «маркера (т.е. указатель номера списка)» и саму запись, в списках справа — распределены.

Количество в 3 списка взято в качестве примера, это количество может быть произвольным, равно как и название «списки».

Особым плюсом можно считать то, что, изменяя маркер в «сыром перечне», можно изменять место расположение записи в сгруппированных списках.

Реализуем такие, автоматически заполняемые списки, подробно объясняя этапы решения задачи.

Видеоверсия

Текстовая версия

Пользователи, которые уже поработали в Excel, очевидно заметят, что принцип действия таких динамических списков очень схож с таковым у функции ВПР, либо более продвинутого аналога данной функции – связки ИНДЕКС и ПОИСКПОЗ.

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

Соответственно, нам нужно сделать реализации чего-то на подобие множественного ВПР, когда значения будут выбираться все значения, а не только первое.

Основная идея реализации

Основу в реализации такого множественного выбора будет составлять чрезвычайно полезная функция ИНДЕКС, которая возвращает значение ячейки, которая находится на пересечении указанных строки и столбца. Столбце у нас известен из условия – это столбец со всеми значениями (правый в «сырой» таблице), а вот номер строки мы будем подставлять динамически.

Данную функции еще часто используют в связке с другой полезной функцией ПОИСКПОЗ, однако, сейчас нам нужна не связка, поскольку номер строки (а именно за поиск номера строки в функции ИНДЕКС отвечает функция ПОИСКПОЗ) мы найдем отдельно.

Видеоверсия

Текстовая версия

Для поиска номера строки воспользуемся довольно незамысловатой конструкцией:
=ЕСЛИ($A2=S$1;СТРОКА(A2))

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

Поиск номера строки

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

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

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

Видеоверсия

Текстовая версия

Мы получили номера строк, которые разбросаны по всему диапазону вычислений, нам нужно их собрать и для этого, во-первых, следует построить простой номерной ряд, банально пронумеровав один столбец, а, во-вторых, с помощью функции НАИМЕНЬШИЙ, собрать все значения по каждому столбцу, отсортировав по возрастанию.

Функция НАИМЕНЬШИЙ похожа на вычисление минимального, т.е. функцию МИН, за тем исключением, что позволяет найти не только минимальное, но и 2-е, 3-е и т.д. наименьше значение после минимального.

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

=НАИМЕНЬШИЙ(I:I;$L2)

Первым аргументом функции указана ссылка полностью на строку, если будете указывать на диапазон, не забудьте позаботиться о том, чтобы его зафиксировать (сделав абсолютную или смешанную ссылку). Второй аргумент – это смешанная ссылка на вспомогательный ряд, т.е., в первом случае, когда ссылка идет на цифру «1», формула вернет минимальное значение, потом — 2-е после минимального и т.д.

Общая картина вычислений выглядит так:

Упорядочивание чисел с помощью функции НАИМЕНЬШИЙ

Как и предполагалось, мы просто записали ранее разбросанные значения с вычислением номера строки.

Видеоверсия

Текстовая версия

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

Таким образом, пишем:

=ИНДЕКС($B:$B;M2;1)

В конечном итоге получаем вот такой результат:

Результат применения функции ИНДЕКС

Все хорошо, за исключением ошибки, которая находится в не заполненных ячейках. Ошибку легко скрыть, если «обернуть» конечную формулу в функцию ЕСЛИОШИБКА, указав, в качестве второго аргумента, пустую ячейку (просто двойные кавычки).

=ЕСЛИОШИБКА(ИНДЕКС($B:$B;M2;1);»»)

И вот такой результат:

Результат использования функции ЕСЛИОШИБКА для перехвата ошибок

Видеоверсия

Текстовая версия

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

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

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

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

Чтобы скачивать файлы, пожалуйста, войдите.

Формула
Применение в рамках данной задачи

ИНДЕКС (INDEX)	Основа формулы, используется для сопоставления заголовков таблицы и заголовков диапазонов исходного списка
НАИМЕНЬШИЙ (SMALL)	Используется для передачи первой ячейки плавающего диапазона в ВПР

Вспомогательные, т.е., без которых можно обойтись:

Формула
Применение в рамках данной задачи

ЕСЛИОШИБКА (IFERROR)	Используется для придания эстетического вида таблице, т.е. замене ошибок на пустые значения ячейки.

• Создание выпадающего списка в Excel
• Функции работы с данными в Excel
• Как сделать так, чтобы ячейки в Excel не заполнялись нолями
• Типы ссылок в MS Excel

Источник: https://msoffice-prowork.com/courses/excel/excelpr/excelpr-free-practice1/