Сообщество - MS, Libreoffice & Google docs
MS, Libreoffice & Google docs
54 поста 5 787 подписчиков
602

Защита данных в Excel

Приветствую всех! В этот раз мы рассмотрим способы защиты данных в Excel, от примитивных приёмов, до более серьёзных с паролированием.


Спрятать содержимое ячеек


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

Нажимаем Ctrl+1 или выбираем «Формат ячеек», в открывшемся меню «Число»-«Все форматы»-«Тип» вводим подряд 3 точки с запятой без пробелов.

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

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

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


Защита ячеек листа от изменений


В случаях, когда необходимо защитить не весь лист, а только некоторые его части, оставив пользователям возможность вводить информацию в определенные ячейки, нужно выделить ячейки, которые не надо защищать (если такие есть), нажимаем Ctrl+1 «Формат ячеек»-«Защита» и снимаем флажок «Защищаемая ячейка».

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

По умолчанию этот флажок всегда включён для всех ячеек.

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

Этот приём необходим т.к. в Excel команда «защитить диапазон», отсутствует, а есть только «защитить лист» и «защитить книгу», поэтому таким образом мы решаем эту проблему.


Далее для включения защиты текущего листа нажимаем кнопку «Защитить лист» на вкладке «Рецензирование». В открывшемся окне можно установить пароль (он необходим, чтобы кто попало не мог снять защиту) и при помощи списка флажков настроить исключения:

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Например, необходимо оставить пользователям:

- возможность помечать ячейки маркером (но не менять их содержимое) –включаем флажок «Форматирование».

- использование фильтрации и/или сортировки (через Автофильтр) – включаем флажки «Использование автофильтра» и/или «Сортировка». Только перед включением защиты сперва создайте сам фильтр на вкладке «Данные».


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


Выборочная защита диапазонов листа для разных пользователей


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

Чтобы это сделать, выбираем на вкладке «Рецензирование» кнопку «Разрешить изменение диапазонов».

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

В появившемся окне нажимаем кнопку «Создать» и вводим имя диапазона, адреса ячеек и пароль к нему:

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

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

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


Защита листов книги


С помощью кнопки «Защитить книгу» на вкладке «Рецензирование» защищаем документ от удаления, переименования, перемещения листов в книге, изменения закрепленных областей (зафиксированной «шапки» таблиц и т.п.), возможности сворачивать/перемещать/изменять размеры окна книги внутри окна Excel:

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Флажок «Структуру» отмечен по умолчанию, т.к. он подразумевает стандартный набор функций защиты листов от удаления, переименования, копирования.

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


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

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Шифрование книги


В Excel имеется возможность зашифровать весь файл книги, используя алгоритм шифрования AES 128-bit. Взломать его напрямую невозможно, а автоматический подбор пароля осложнён низкой скоростью перебора вариантов, поэтому не забывайте свои пароли.


Данную защиту можно задать при сохранении книги, выбрав «Файл» – «Сохранить как» или клавишу F12, в окне сохранения нажать «Сервис» – «Общие параметры».

В появившемся окне можно ввести два разных пароля – на открытие файла (только чтение) и на изменение:

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Если задать первый пароль (для открытия), то пользователь не сможет даже открыть файл для просмотра.

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

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

Кстати, в Microsoft Word этот способ тоже работает.


Суперскрытый лист


Иногда некоторые листы (например, с личными заметками) приходится скрывать от глаз пользователей. Простым способом является скрытие листа нажав правой кнопкой мыши по ярлыку листа «Скрыть», но это малоэффективно.

Чтобы улучшить защиту у нас должно быть открыто минимум два листа (т.к. один лист мы скроем), затем открываем редактор Visual Basic, на вкладке «Разработчик» или нажатием Alt+F11, выбираем «Visual Basic».


Нажатием Project Explorer или Ctrl+R откроется меню

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

В верхней части выделяем наш Лист1, а в нижней части находим свойство Visible и делаем его xlSheetVeryHidden.

Защита данных в Excel Excel, Защита, Защита информации, Полезное, На заметку, Длиннопост

Можно переименовать Лист2 на какое-нибудь слово, чтобы не палиться о наличии Листа1 и теперь узнать о его существовании можно только в редакторе Visual Basic, в других местах он отображаться не будет.


Берегите свои данные, чаще сохраняйтесь Shift+F12 и вы никогда ничего не потеряете.

Показать полностью 9
140

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel

Многие из тех, кто часто работает в MS Excel, собрали коллекцию макросов на VBA, которые облегчают их ежедневный труд. У кого-то эти макросы хранятся в отдельной книге, кто-то собрал их в "личной книге макросов" (personal.xlsb), доступной на уровне всего приложения, и вручную добавил кнопки вызова нужных скриптов на панель инструментов. В первом случае коллекцией удобно делиться с коллегами - достаточно переслать файл, но чтобы ей воспользоваться, необходимо каждый раз открывать эту книгу. Во втором случае доступ к функциональности есть сразу при запуске Excel, но могут возникнуть проблемы с передачей наработок другим пользователям.

В данном посте описан способ создания VBA-настройки с пользовательской панелью инструментов на ленте Excel (Ribbon), которая позволяет воспользоваться преимуществами обоих подходов. Это файл в формате xlam, который можно передавать как обычную Excel-книгу. При её открытии пользователь видит появившуюся панель инструментов, которая даёт доступ к функциональности надстройки:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

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

Используемые инструменты: MS Excel, 7zip, Visual Sudio Code. Последние два необязательны, подойдёт любой архиватор с поддержкой zip-формата и любой текстовый редактор, умеющий в UTF-8 (если Вы хотите использовать кириллицу).

Также желательно обладать базовым представлением о формате XML.


Код и результат в виде книги xlsm можно найти на гитхабе:

https://github.com/navferty/SampleExcelXlamAddin

Итак, для начала откроем новую книгу, и добавим модуль с образцом кода:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Сохраним книгу в формате xlsm (книга с поддержкой макросов). Современные форматы документов MS Office (xlsx, xlsm, docx, docm, pptx и т.д.) основаны на стандарте OpenXML и представляют из себя обычный zip-архив, который состоит из компонентов (например, XML-файлы, соответствующие листам, изображения, бинарный контент и прочее), а также файлов отношений (.rels), которые задают структуру всего документа, путём ссылок на компоненты.

Чтобы получить доступ к внутреннему содержимому книги, можно переименовать файл Sample.xlsm в Sample.zip, после чего открыть его любым архиватором. Некоторые архиваторы, например 7zip, умеют открывать файлы напрямую, без изменения имени файла:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Чтобы добавить ribbon-панель, нам нужно будет создать новый компонент customUI/customUI.xml, а также добавить ссылку на него в корневой файл отношений _rels/.rels

Разархивируем весь документ в отдельную папку ("Распаковать в "Sample\""), и создадим в ней новую папку customUI:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Теперь добавим собственно компонент:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Ниже приводится текстовая версия содержимого customUI.xml


<?xml version="1.0" encoding="utf-8"?>

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

<ribbon>

<tabs>

<tab id="customTab" label="Sample Add-in" keytip="XT">

<group id="Tools" label="Group 1">

<button id="HelloWorldBtn" imageMso="BlackAndWhiteAutomatic" keytip="H" label="Привет, Мир!"

onAction="SayHelloWorld" screentip="Поприветствовать мир" size="large" />

<button id="DuplicatesBtn" imageMso="SmartArtChangeColorsGallery" keytip="D"

label="Выделение цветом дублей" onAction="DuplicateColors"

screentip="Выделение парными цветами повторов в выделенном диапазоне" size="large" />

</group>

</tab>

</tabs>

</ribbon>

</customUI>

Коротко расскажу о важных элементах и атрибутах.

Значения идентификаторов (атрибут id) должны быть уникальными. Для некоторых элементов (например, tab - в случае, если Вы объявляете новую вкладку, а не существующую, путем указания idMso) также обязательно указать label - иначе Excel не отобразит этот элемент.

С помощью атрибута imageMso можно определить иконку элемента из числа имеющихся в MS Office. Список доступных значений весьма объемный, поэтому здесь приводить не буду - он легко гуглится.

Элемент button - обычная кнопка. В зависимости от атрибута size может быть маленького размера или большой, во всю высоту вкладки.

Атрибут onAction задаёт название процедуры, которая будет выполнена при основном взаимодействии с элементом (например, при нажатии на кнопку button). Процедура должна иметь определённую сигнатуру (количество и тип аргументов), их можно найти в спецификации. Например, метод для onAction у элемента button принимает один аргумент типа IRibbonControl.

Название этого метода также не должно совпадать с названием модуля (либо необходимо явно указать имя модуля перед именем самого метода: "Module1.SayHelloWorld").

Атрибут keytip поможет в навигации с помощью клавиатуры - с помощью клавиши Alt можно осуществлять навигацию по вкладкам MS Excel.


Вы можете скопировать образец вёрстки на официальном сайте с документацией от Microsoft:

Образец customUI на docs.microsoft.com


Для тех, кто хочет подробно изучить стандарт, есть спецификация, также на docs.microsoft.com:

[MS-CUSTOMUI]: Custom UI XML Markup Specification

Обратите внимание, что при наличии не-ASCII символов (например, кириллицы) файл следует сохранить в кодировке UTF-8, иначе Вы можете столкнуться с тем, что Excel не будет отображать новую вкладку.


Чтобы MS Excel "увидел" и "понял", что за компонент мы добавили, нужно указать ссылку на него в корневом файле отношений, который лежит в папке _rels, и называется .rels

Он также имеет xml-формат, и для удобства можно отформатировать его (в VS Code для этого есть команда: Ctrl-Shift-P -> Format Document).

Добавляем элемент Relashionship, указав путь к customUI.xml, тип компонента, а также идентификатор (не имеет значения, какой именно. Главное, чтобы он был уникальным среди других элементов Relashionship):

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Добавляю текст отношения для удобства копирования (чтобы парсер не "съел" URL, добавил пробел перед .com - не забудьте его убрать при копировании)

<Relationship Id="rID4" Target="customUI/customUI.xml" Type="http://schemas.microsoft .com/office/2006/relationships/ui/extensibility" />

Теперь заархивируйте обратно все файлы, и верните исходное имя файла Sample.xlsm

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Если всё было сделано правильно, то при открытии книги Вы увидите новую вкладку на ленте:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Однако при попытке запустить макрос HelloWorld Вы можете столкнуться с таким предупреждением:

Wrong number of arguments or invalid property assignment

Это связано с тем, что функция-коллбэк должна иметь определенную сигнатуру. Так, обработчик нажатия на кнопку button должен выглядеть следующим образом:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Добавьте аргумент "rc As IRibbonControl", и Вы увидите долгожданное приветствие:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Список сигнатур можно найти на сайте документации Microsoft


Теперь осталось лишь сохранить книгу с макросами как надстройку. В редакторе VBE выберите объект ThisWorkbook и установите свойство IsAddin в значение True.

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Теперь нужно сохранить книгу в новом формате (Excel предупредит об этом, если Вы попытаетесь сохранить книгу нажатием Ctrl-S)

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Теперь надстройку можно открыть, как обычную книгу Excel. Она не будет отображаться в отдельном окне, как книга, но её можно увидеть в Project Explorer'е редактора VBE:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Для того, чтобы надстройка была доступна сразу при запуске Excel, установите её в меню Excel Add-ins на вкладке разработчика...

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

...или через настройки Excel, в разделе Надстройки (Add-ins)

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Готово! Теперь Вы можете использовать надстройку при работе с любым документом, а также легко делиться своими наработками с коллегами, просто пересылая файл надстройки.

P.S. В качестве бонуса приведу примеры использования различных элементов в Custom UI

toggleButton - кнопка, которая может быть в двух состояниях

splitButton - составной компонент из button или toggleButton и выпадающего меню

dropDown - выпадающее меню с заранее определённым набором элементов

comboBox - интерактивное поле ввода, которое может содержать заранее определённый набор элементов

dynamicMenu - выпадающий список, элементы которого определяются динамически в методе getContent

checkBox - чекбокс, который может быть в двух состояниях

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Excel, Vba, Туториал, Вкладки, Длиннопост

Разметка и код VBA модуля ниже, также их можно найти на гитхабе


customUI.xml


<?xml version="1.0" encoding="utf-8"?>

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

<ribbon>

<tabs>

<tab id="customTab" label="Sample Add-in" keytip="XT">

<group id="SampleControls" label="Group 2">

<splitButton id="splitButton" size="large" >

<button id="SplitButton" imageMso="HappyFace" label="Split Button" />

<menu id="menu">

<button id="button1" label="Button 1" onAction="OnSplitButton1Click" />

<button id="button2" label="Button 2" onAction="OnSplitButton2Click" />

</menu>

</splitButton>

<toggleButton id="ToggleButton" label="Toggle Button" onAction="OnToggleButtonClick" />

<dropDown id="DropDown" label="DropDown" onAction="OnDropDownSelected" >

<item id="DropDownItem1" label="Item 1" />

<item id="DropDownItem2" label="Item 2" />

<item id="DropDownItem3" label="Item 3" />

<button id="button" label="Button..." />

</dropDown>

<comboBox id="ComboBox" label="Combo Box" onChange="OnComboBoxSelected" >

<item id="ComboBoxItem1" label="Item 1" />

<item id="ComboBoxItem2" label="Item 2" />

<item id="ComboBoxItem3" label="Item 3" />

</comboBox>

<separator id="separator" />

<dynamicMenu id="DynamicMenu" label="Dynamic Menu" getContent="GetMenuContent" />

<checkBox id="CheckBox" label="Check Box" onAction="OnCheckBoxToggled" />

</group>

</tab>

</tabs>

</ribbon>

</customUI>

VBA модуль SampleControls

Option Explicit


Public Sub OnSplitButton1Click(rc As IRibbonControl)

MsgBox "Split button 1 was clicked"

End Sub


Public Sub OnSplitButton2Click(rc As IRibbonControl)

MsgBox "Split button 2 was clicked"

End Sub


Public Sub OnToggleButtonClick(rc As IRibbonControl, isButtonPressed As Boolean)

MsgBox "Toggle button was toggled, button now is " & IIf(isButtonPressed, "pressed", "not pressed")

End Sub


Public Sub OnDropDownSelected(rc As IRibbonControl, selectedItemId As String, selectedItemIndex As Integer)

MsgBox "DropDown was changed, selected item id is " & selectedItemId

End Sub


Public Sub OnComboBoxSelected(rc As IRibbonControl, comboBoxValue As String)

MsgBox "Combo box was changed, value is " & comboBoxValue

End Sub


Public Sub GetMenuContent(rc As IRibbonControl, ByRef returnedVal)

Dim xml As String


xml = "<menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">" & _

"<button id=""but1"" imageMso=""Help"" label=""Help"" onAction=""OnHelpPressed""/>" & _

"<button id=""but2"" imageMso=""FindDialog"" label=""Find"" onAction=""OnFindPressed""/>" & _

"</menu>"


returnedVal = xml

End Sub


Public Sub OnCheckBoxToggled(rc As IRibbonControl, isButtonChecked As Boolean)

MsgBox "Check box was toggled, value is " & IIf(isButtonChecked, "checked", "not checked")

End Sub


Public Sub OnHelpPressed(rc As IRibbonControl)

MsgBox "Help button pressed"

End Sub


Public Sub OnFindPressed(rc As IRibbonControl)

MsgBox "Find button pressed"

End Sub

Более подробная информация о различных элементах Custom UI есть в документации:

Документация по элементам Custom UI от Microsoft

P.P.S. Дополнительный бонус для тех кто дочитал до конца - горячие клавиши редактора VBE

Навигация по редактору

Ctrl-R - перейти в окно проектов (Project Explorer)

F4 - перейти к свойствам

Ctrl-G - перейти к Immediate window

F7 - перейти в окно редактора кода

Ctrl-Tab (Ctrl-Shift-Tab) - переключение между открытыми окнами модулей

Ctrl-F4 -закрыть текущий модуль

Alt-F11 - открыть редактор кода VBE


Редактирование и навигация по коду

Ctrl-Space - автодополнение

Ctrl-J - показать доступные варианты (IntelliSense)

Ctrl-I - показать информацию о методе (аргументах)

Shift-F2 - перейти к определению переменной/метода


Отладка

F5 - запустить макрос/продолжить исполнение

F8 - шаг вперёд (с заходом во вложенные функции)

Shift-F8 - шаг вперёд (без захода вглубь)

F9 - установить/снять точку останова (breakpoint)

Shift-F9 - добавить наблюдателя (quick watch)

Ctrl-L - показать стек вызовов (кликабельный)

Показать полностью 14
1001

Лайфхак  как открыть дважды  тот же лист Excel

опять опишу очевидное ( но невероятное).

Иногда бывает, что нужно сделать так, чтобы  один и тот же лист документа был виден  на экране дважды ( допустим, начало  длиннючего документа и  его окончание). ( я работаю с длинными контрактными сметами, где частенько овер 3000 строк и постоянно надо метаться по всему листу). Тогда  в экселе можно сделать так:  Вид-  Новое окно

Лайфхак  как открыть дважды  тот же лист Excel Excel, Лайфхак, Длиннопост

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

Лайфхак  как открыть дважды  тот же лист Excel Excel, Лайфхак, Длиннопост

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


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


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

Лайфхак  как открыть дважды  тот же лист Excel Excel, Лайфхак, Длиннопост

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


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

Показать полностью 1
244

Стили в Microsoft Word

В Microsoft Word есть очень мощный и полезный инструмент, который называется "Стили". Когда я только начинал плотно работать в word'е - казалось, что стили - это очень сложно и непонятно, поэтому старался их не использовать. Но разобравшись, я понял, что там всё очень просто и хочу вам сейчас это показать.


Для чего вообще нужны стили?

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

Стили в Microsoft Word Microsoft Word, Microsoft, Видео, Длиннопост

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

Когда вас устраивает, как выглядят заголовки, можно приступать к созданию стилей, а тут всё очень просто)

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

1. переходим в расширенный список стилей

2. нажимаем "Создать стиль"

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

4. нажимаем "Ок"

Стили в Microsoft Word Microsoft Word, Microsoft, Видео, Длиннопост

в работе для удобства и скорости я обычно стили заголовков называю "1", "1.1", "1.1.1" - так быстрее и наглядней)

Теперь самое интересное - как нужный текст сделать заголовком из созданных стилей? Просто поставить курсор в нужное место и выбрать из галереи стилей нужный вам ранее созданный стиль.

Вот так вот все просто)



Еще один существенный бонус использования стилей для заголовков - потом очень просто собрать оглавление. Для этого ставим курсор в место, куда хотим вставить оглавление, переходим во вкладку "Ссылки", нажимаем "Оглавление", затем выбираем пункт "Настраиваемое оглавление"

Стили в Microsoft Word Microsoft Word, Microsoft, Видео, Длиннопост

Во всплывающем окне сразу переходим в параметры, там вписываем уровни заголовков цифрами (1,2,3), нажимаем 2 раза "ок"

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

Стили в Microsoft Word Microsoft Word, Microsoft, Видео, Длиннопост

Получится такое вот стандартное оглавление

Стили в Microsoft Word Microsoft Word, Microsoft, Видео, Длиннопост

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

Спасибо за внимание!

Надеюсь, кому-нибудь пригодится)

Показать полностью 4 2
607

Отключение защищенного просмотра в MS Office

Для тех, кого раздражает при постоянной работе с документами нажимать "Разрешить редактирование". Это можно отключить. Для этого идем по пути: Файд - Параметры - Центр управления безопасностью - Параметры центра управления безопасностью - Защищенный просмотр и снимаем все галки. Для наглядности прикрепил пошаговые скрины. Причем проделать это нужно в каждом приложении MS Office.


P.S.Для тех, кто беспокоится о безопасности, делать этого не рекомендую. Но на работе используем только внутреннюю интранет-сеть, поэтому опасаться нечего.

Отключение защищенного просмотра в MS Office Ms Office, Microsoft Word, Excel, Miscrosoft Excel, Длиннопост
Отключение защищенного просмотра в MS Office Ms Office, Microsoft Word, Excel, Miscrosoft Excel, Длиннопост
Отключение защищенного просмотра в MS Office Ms Office, Microsoft Word, Excel, Miscrosoft Excel, Длиннопост
Отключение защищенного просмотра в MS Office Ms Office, Microsoft Word, Excel, Miscrosoft Excel, Длиннопост
Отключение защищенного просмотра в MS Office Ms Office, Microsoft Word, Excel, Miscrosoft Excel, Длиннопост
Показать полностью 3
316

Word, excel - добавление часто используемых функций в панель быстрого доступа

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

Выглядит это вот так:

Word, excel - добавление часто используемых функций в панель быстрого доступа Microsoft, Microsoft Word, Miscrosoft Excel, Powerpoint

Чтобы это сделать, наведите курсор на нужную вам функцию и нажмите правую кнопку мыши. Во всплывающем окне выберите первую команду.

Word, excel - добавление часто используемых функций в панель быстрого доступа Microsoft, Microsoft Word, Miscrosoft Excel, Powerpoint

Также, в эту панель можно добавлять и макросы. Для этого нужно перейти в параметры Word (Excel, PowerPoint) и выбрать там пункт "Панель быстрого доступа", либо нажать крайнюю правую кнопку на панели и там выбрать пункт "Другие команды" :

Word, excel - добавление часто используемых функций в панель быстрого доступа Microsoft, Microsoft Word, Miscrosoft Excel, Powerpoint

Появится окно параметров. Тут нужно будет сделать последовательность действий:

1. выбрать из всплывающего списка пункт "Макросы"

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

3. нажать "Добавить"

4. нажать "Ок"

Word, excel - добавление часто используемых функций в панель быстрого доступа Microsoft, Microsoft Word, Miscrosoft Excel, Powerpoint

Может быть, кому-то будет полезно)

Показать полностью 1
41

Калькулятор суточной нормы калорий и БЖУ в эксель

Калькулятор суточной нормы калорий и БЖУ(белки, жиры, углеводы) в эксель поможет понять, какая норма необходима для поддержания формы, набора веса или похудения. Укажите ваши параметры, выберите образ жизни и цель. Система сделает расчет автоматически!

Скачать с яндекс диска заменена на  https://yadi.sk/i/VoQ4bOr_Pv2Jag

992

Наша победа! Сообществу быть!

Уважаемые подписчики, спешу вас обрадовать @SupportCommunity разрешил создать сообщество посвящённое Office. Спасибо вам за поддержку))


Сообщество будет посвящено MS Office, Libreoffice и Google docs.


Я хочу чтобы сообщество приносило пользу многим, дабы облегчить работу офисному брату))


Тех кто владеет Libreoffice и Google docs призываю вас быть активней, сообществу понадобятся модераторы, чтобы следить за порядком и публиковать полезные посты.


Ссылка на сообщество MS, Libreoffice & Google docs

85

Приёмы отладки VBA-кода (на примере MS Excel)

Многие из тех, кто часто работает в MS Excel, используют книги или надстройки с VBA-макросами. Зачастую требуется доработать существующее решение, добавив новую функциональность или исправив найденный баг. В поисках места, где скрывается баг или производится нужное вычисление, можно провести немало времени, особенно если проект содержит десятки тысяч строк кода (увы, такие макросы иногда встречаются). Можно прибегнуть к текстовому поиску (например по тексту сообщения, вшитому в код), но иногда удобнее запустить макрос и прибегнуть к пошаговой отладке.

В этом посте описаны несколько приёмов, которые могут быть полезны при отладке макросов на VBA в приложениях MS Office на примере MS Excel.


1. Стандартные точки останова (breakpoint). Step-into, step-over

2. Окна Immediate ("REPL-терминал"), Locals (локальные переменные), Watches (наблюдатели)

3. Условные точки останова на базе watch expression

4. Обработка ошибок

1. Самый очевидный способ отладки - поставить точку останова в начале макроса, а затем пошагово исполнять строку за строкой, наблюдая за значениями переменных и состоянием ячеек в книге. Чтобы установить точку останова, нажмите F9 - и на строке, где находится курсор,

появится такая отметка (можно установить и кликом мышью по области слева, где красный кружок):

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Теперь как только поток исполнения доберётся до этой строки, он будет приостановлен, а Вы увидите желтый указатель, указывающий на следующую инструкцию:

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Многие не знают, что этот указатель можно перемещать на любой statement в пределах функции, просто перетащив мышью желтую стрелку!


Когда Вы приостановили исполнение кода, можете продолжить его исполнение пошагово, с помощью команд step-into, step-over и step-out:

* step-into (F8) - шаг вперёд. Будет выполнен код на строке под указателем, а сам указатель перемещён на следующую строку. Однако если в текущей строке есть вызов другой функции, указатель переместится внутрь этой функции

* step-over (Shift-F8) - полностью аналогичен step-into, за исключением того, что эта команда позволяет не "проваливаться" в вызов вложенных функций

* step-out (Ctrl-Shift-F8) - текущая функция будет исполнена до конца, а исполнение приостановлено в вызывающей функции. Полезна, если Вы случайно провалились в длинную функцию командой step-into


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

2. В нижней части экрана VBE (Visual basic editor) Вы можете увидеть три окна:


* Immediate (терминал для выполнения кода и вывода текстовой информации)

* Locals (локальные переменные)

* Wathes (наблюдатели)

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Если Вы не видите эти окна, включите их отображение в меню View

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Разберём подробнее, какие возможности открывают нам эти инструменты.


Окно Immediate представляет собой REPL-терминал, в котором можно ввести выражение и запустить его нажатием Enter. Чтобы вывести результат в этот же терминал, передайте результат выражения в функцию Debug.Print (кстати, эту функцию можно вызвать и в основном коде макроса - результат так же будет выведен в консоль Immediate). В самой консоли можно воспользоваться и сокращённой версией этой команды - знаком вопроса:

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Обратите внимание, что с помощью сочетаний Ctrl+Space и Ctrl+J можно вызывать окно IntelliSense , также как и в основном редакторе кода.


Окно Locals позволяет увидеть значения переменных, доступных в текущем контексте:

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

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

dict.Items("key1")

Это можно сделать при помощи "наблюдателя" - watch expression

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Выделите выражение и выполните команду Add watch... в контекстном меню (доступном с помощью правой кнопки мыши), или введите нужно выражение вручную в окне добавления наблюдателя:

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

Осторожно: выражение d.Item("key1") при вычислении может добавить элемент к словарю, если такой ключ в словаре отсутствует. Поэтому не забудьте удалить наблюдатель, когда он станет ненужным, иначе в дальнейшем можно провести немало времени, выясняя откуда взялся лишний элемент в словаре.

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


Вероятно, Вы обратили внимание, что в меню добавления наблюдателя помимо стандартной опции "Watch Expression" есть ещё две: "Break When Value Is True" и "Break When Value Changes". Они позволяют добиться приостановки исполнения макроса при наступлении соответствующего условия (выражение истинно, либо значение выражения изменилось). Эта возможность может оказаться особенно полезной, например, при отладке циклов. Если Вы знаете, что макрос, который в цикле обрабатывает строки на листе Excel, сталкивается с ошибкой, скажем, на строке 42, то можно избежать ручного "пролистывания" 41 предшествующей итерации, добавив выражения вида i=42 с опцией Break When Value Is True, где i - это переменная счётчика цикла.

4. Напоследок хотел бы немного рассказать об обработке ошибок в VBA.

В настройках VBE Вы можете найти выбор из трёх опций (меню Tools -> Options, вкладка General):

Break on All Errors

Break in Class Module

Break on Unhandled Errors

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

По умолчанию, как правило, установлена последняя опция, которая позволяет программисту обрабатывать ошибки с помощью директив On Error Goto (показать сообщение об ошибке, "прибраться за собой" - закрыть книгу или соединение) и On Error Resume Next (игнорировать ошибку и продолжать исполнение). Однако, когда Вы столкнётесь с ошибкой вида "Извините, что-то пошло не так =(", которая отображается из обработчика в On Error Goto, будет трудно определить место, где произошла ошибка. В этом случае пригодится опция Break on All Errors, которая позволит проигнорировать директивы On Error и перейти к тому месту в коде, где и произошла ошибка. В этот момент может пригодиться и окно Locals, где Вы сможете увидеть, например, текущее значение счётчика цикла, и понять, при обработке какой строки на листе Excel возникла ошибка.


Указанные опции обработки ошибок устанавливаются на уровне настроек пользователя, и не сохраняются в VBA-проекте.

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


Минутка рекламы.

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

https://github.com/navferty/NavfertyExcelAddIn

Приёмы отладки VBA-кода (на примере MS Excel) Excel, Vba, Длиннопост

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


Это мой первый пост на Пикабу, буду рад конструктивной критике в комментариях и ЛС.

Показать полностью 8
9

Отсутствует работа формул и функций в Excel

Прошу помощи сообщества Excel.

Суть вопроса.

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

Как можно решить проблему? Буду рад любым советам, так как работа с выписками в экселе очень облегчает работу для упрощенцев при формировании и сдаче отчётов, формировании книги доходов\расходов.

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

Заранее всем спб за помощь.

ПЫСЫ : образец проблемного файла можно скачать на  https://dropmefiles.com/IAFQe

39

Калькулятор неопределенности в Эксель

Доброго дня всем, пишу первый раз, строго не судите.

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

В книге 3 листа:

1) сам калькулятор;

2) список измерительных приборов;

3) список определяемых параметров.

Калькулятор неопределенности в Эксель Excel, Калькулятор

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

В данном файле макросы не использовались. Скачать с яндекс диска https://yadi.sk/i/eTvuGZPUUAJEoQ

138

OfficeTab

А для удобства работы в MS Office есть замечательная программка OfficeTab (ссылок кидать не буду, есть замечательный сайт rsload.net - там и найдете). Позволяет открывать каждый документ в новой вкладке (прямо, как в браузере). Упрощает работу в разы)


P.S. Авторам сообщества: А почему бы не накидать сюда список быстрых клавиш для работы в офисе? Думаю, многим пригодится! С праздниками и отличного настроения всем!

OfficeTab Ms Office, Microsoft Word, Excel, Powerpoint
247

Секционные диаграммы

Приветствую всех! Это последний пост про Excel в этом году, в новом году будет много других полезных и интересных постов)


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

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Копируем названия стран из столбца А1 в любое свободное место вставив как «Значения», затем на вкладке «Данные» нажимаем на кнопку «Удалить дубликаты», в результате все пустые ячейки удалятся.

Далее копируем и вставляем названия в ячейку С1 выбрав «Транспонировать».

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Следующий шаг разбиваем цифры по странам в лестничном порядке

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Теперь выделяем всю таблицу и на вкладке «Вставка» выбираем «Гистограмму с накоплением», в результате получаем разноцветную, разбитую по странам диаграмму

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Также на вкладке «Конструктор» можно изменить тип диаграммы на «Линейчатую с накоплением»

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

В результате получим удобную и понятную диаграмму

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Узловые диаграммы


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


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

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Кликаем правой клавишей мыши по диаграмме и выбираем «Выбрать данные», в открывшемся окне нажимаем «Изменить»

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

Далее для ввода адреса в строку выделяем содержимое диапазона столбцов А и В и нажимаем ОК

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост

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

Секционные диаграммы Excel, Диаграмма, Таблица, Полезное, На заметку, Длиннопост
Показать полностью 8
Мои подписки
Подписывайтесь на интересные вам теги, сообщества,
пользователей — и читайте персональное «Горячее».
Чтобы добавить подписку, нужно авторизоваться.
Отличная работа, все прочитано!