Excel

Option Explicit

'Отменяем  назначение горячих  клавиш перед закрытием  книги

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.OnKey "^q": Application.OnKey "^w"

End Sub

'Назначаем  горячие клавиши  при открытии книги

Private Sub Workbook_Open()

    Application.OnKey "^q", "My_Copy": Application.OnKey "^w", "My_Paste"

End Sub 

Теперь можно  скопировать нужный диапазон нажатием клавиш Ctrl+q, а вставить его в отфильтрованный - Ctrl-w. 
 

5.7 Как сцепить несколько значений в одну ячейку по критерию?СцепитьЕсли. 

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

Function СцепитьЕсли(ByRef Диапазон As Range, ByVal Критерий As String, ByRef Диапазон_сцепления As Range, Optional Разделитель As String = " ") As String

    Dim rCell As Range, rFndrng As Range, sStr As String

    For Each rCell In Диапазон

        If rCell.Value Like Критерий Then

            If Trim(Диапазон_сцепления.Cells(rCell.Row - Диапазон.Row + 1, 1)) <> "" Then

               sStr = sStr & IIf(sStr <> "", Разделитель, "") & Диапазон_сцепления.Cells(rCell.Row - Диапазон.Row + 1, 1)

        End If

    Next rCell

    СцепитьЕсли = sStr

End Function 

Для применения функции в своем файле достаточно создать стандартный модуль(как  это сделать написано здесь) и  просто вставить приведенный код. После  этого в диспетчере функций появиться  новая категория(если до этого её не было) - Определенные пользователем. В ней эта функция – Сцепить else. 

По принципу работы функция похожа на стандартную  СУММЕСЛИ. Указываете диапазон значений(фамилии), критерий(одна фамилия) и диапазон значений для сцепления. Символ для разделения слов указывать необязательно. 

По умолчанию  Разделитель слов - пробел, но можно  задать любой другой символ/символы.

Диапазон - диапазон с критериями(указывается один столбец)

Критерий - критерий. Может содержать символы подстановки - * и ?. Просматривается Диапазон.

Диапазон-сцепления - из этого диапазона берется значение для сцепления, если значение в аргументе Диапазон совпадает с аргументом Критерий(указывается один столбец) 

Примечание: для  работы функции должны быть разрешены  макросы. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

6 Разное

        6.1 Проверка данных

        6.2 Как сделать лист скрытым?

        6.3 Как сделать лист очень скрытым?

        6.4 Как одновременно просматривать информацию с нескольких листов/диапазонов?

        6.5 Как сделать одинаковые изменения сразу на нескольких листах

        6.6 Как разрешить изменять только выбранные ячейки?

        6.7 Как узнать есть ли формула в ячейке?

        6.8 Как уменьшить размер файла

        6.9 Excel удаляет вместо отфильтрованных строк - все?! Как избежать? 

6.1 Проверка данных 

        Проверка данных является неплохим  инструментом Excel для контроля за вносимыми на листах изменениями, не прибегая к помощи VBA. С её помощью можно ограничить ввод в ячейку, разрешив вводить только даты либо время, либо только числа. Да к тому же еще и задать диапазон дат либо предел чисел(к примеру от 1 до 10).Применений, я думаю, можно придумать массу: для корректной работы многих формул требуются корректные исходные данные. Следовательно, мы можем с помощью Проверки данных разрешить пользователю вводить только тот тип и диапазон данных, который может обработать формула, не возвращая значение ошибки. Разберем поподробней. 

Сей чудесный инструмент находится: Данные - Проверка данных. Должна появиться такая вот формочка(рис.1) 

рис.1 

Для начала надо выделить ячейку(или диапазон ячеек) для которой хотим установить проверку. Затем идем в Данные - Проверка данных. 

Первая вкладка - Параметры 

В поле Тип данных - мы выбираем собственно тип данных, которые планируем хранить в  ячейке. Всего доступно 8 типов: Любое  значение, Целое число, Действительное, Список, Дата, Время, Длина текста, Другой. Любое значение - установлен по умолчанию, проверка не осуществляется. Про пункт Списки и то как их создавать можно посмотреть и почитать в разделе Списки - Выпадающие списки. Поэтому рассмотрим мы в данной статье оставшиеся 6 типов. 

Целое число  - говорит само за себя. После установления такой проверки в ячейку можно будет внести только целое число. Т.е. число, не имеющее дробного остатка(9,1 например). 

Действительное - тоже, что и в предыдущем пункте, но ввести можно любое число - хоть целое, хоть дробное. 

Примечание: применив данные типы проверок, внести в ячейку можно только число. Занести текст  Excel уже не разрешит. Однако стоит помнить, что даты и время Excel воспринимает и хранит именно как ЧИСЛОВЫЕ значения, поэтому ввод даты и времени тоже будет разрешен, если он не противоречит остальным условиям проверки(см.ниже). Но для проверки Целое число разрешен будет ввод только даты, т.к. время Excel хранит как дробное значение(кроме 24:00:00 и 00:00:00, которые Excel воспринимает как 1 и 0 соответственно). Чтобы лучше понять, можете ввести значения времени - допустим 1:43:56 в ячейку и через Формат ячеек(правая кнопка мыши - Формат ячеек) присвоить ячейке формат Общий. Ячейка поменяет значение на 0,0721759259259259. Однако на вычисления это не влияет, т.к. Excel видит дату и время именно в виде чисел, а все остальное только для нашего восприятия. 

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

Время - тоже думаю не нуждается в лишних комментариях. 

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

Я уже упоминал про остальные условия и если посмотреть на картинку, то можно увидеть еще два поля: Значение, Минимум и Максимум. Так вот. Поле Значение имеет так же несколько пунктов: Между, Вне, Равно, Больше, Меньше, Больше или равно, Меньше или равно. 

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

А теперь самый  интересный тип проверки - Другой. Здесь  нам предоставляется возможность  более широко контролировать ввод данных. Для заполнения есть только одно поле - Формула. В него необходимо записать формулу и при каждом вводе значений в ячейку, Excel проверит введенное выражение на ИСТИНУ. Если значение в ячейке является ИСТИНОЙ, то оно введется, а если ЛОЖЬ, то будет выдано окно с сообщением об ошибке(рис.2). 

рис.2 

Приведу простой  пример. В ячейку А1 введите число 1. Выделяете ячейку В1-назначаете проверку данных - Другой. В поле Формула вписываете: =B1=A1. Теперь в ячейку В1 можно вписать только значение, которое полностью идентично значению в ячейке А1. 

Вкладка Сообщение  для ввода 

Здесь можно  написать текст, который будет отображаться при активации ячейки с проверкой данных(рис.3). 

рис.3 

Вкладка Сообщение  об ошибке  

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

Останов, Сообщение - можно ввести только разрешенные проверкой значения. Различается только вид сообщения(пример сообщения Останов приведен на рис.2). 

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

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

6.2 Как сделать лист скрытым? 

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

рис.1 

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

Лист скрыли. А вдруг он нам понадобится? Тогда  проделываем те же действия - правая кнопка мыши по ярлычку листа, но теперь у нас есть пункт Отобразить. Его  и выбираем. Появляется диалоговое окно отображения листов(рис.2).  

рис.2 

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

6.3 Как сделать лист полностью скрытым 

        Если Вы хотите скрыть лист  со всем его содержимым от  посторонних любопытных глаз, то  Вы можете скрыть его из  контекстного меню листа(подробнее  об этом здесь). Но при использовании  данного метода необходимо защитить  книгу, иначе отобразить скрытые листы сможет каждый. А что делать, если книгу не надо защищать? Что если необходимо оставить пользователям возможность работы со структурой книги, например, добавлять листы? А лист скрыть надо. Здесь на помощь придет VBA. Притом не обязательно иметь какие-то глубокие познания в программировании. Просто заходим в редактор VBA(Alt+F11) и щелкаем по листу в окне объектов(если не отображается то - Ctrl+R или View-Project Explorer). Подробнее про модули листов и как их найти читайте здесь. После того как выбрали необходимый лист в Окне свойств - Properties Window(если не отображается:F4 или View-Properties Window) находим свойство Visible и назначаем ему значение xlSheetVeryHidden.

 

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

6.4 Как одновременно просматривать информацию с нескольких листов/диапазонов? 

     Возможно  это и не пригодиться никому, а возможно для кого-то будет весьма актуально. К примеру: у Вас есть книга Excel с, скажем тремя, листами. На каждом листе свои данные. На "Лист1" в диапазоне A4:A10 занесены данные. На "Лист2" в диапазоне А1:А10 ведутся расчеты, результат которых зависит от данных на "Лист1". На "Лист3" тоже расчеты, но зависящие от данных в диапазоне А1:А10 на "Лист2". И напоследок в диапазоне T100:T102 на "Лист1" ведутся расчеты, зависящие от данных и на "Лист1" и на "Лист2" и на "Лист3". Так вот, если Вы хотите видеть результаты вычислений во всех этих диапазонах, то Вам придется либо переходить с одного листа на другой, либо воспользоваться предлагаемым мной методом.