Excel

2.5 Динамические диапазоны 

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

Вызываем Диспетчер  создания имен и в поле Диапазон пишем формулу: =СМЕЩ(Лист2!$A$1;;;СЧЁТЗ(Лист2!$A$1:$A$1000);).  

 

Столбец, конечно  свой указываете, в примере это  столбец А. Если думаете, что у Вас может быть более 1000 значений, то увеличиваете значение $A$1000 на необходимое количество строк. Хотя мне лично страшно представить себе такой выпадающий список. Но динамический диапазон может ведь пригодиться и для других целей. 

Примечание: созданный  таким образом диапазон нельзя использовать в составе функции ДВССЫЛ при  создании зависимых выпадающих списков(про  зависимые выпадающие списки можно  почитать тут). В смысле использовать можно, но результата не будет. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

3 Графики

        3.1 Два в одном - как сделать?

        3.2 Динамическая диаграмма 

3.1 Два в одном - как сделать? 

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

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

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

Итак мы выбрали  тип. На этом самый сложный этап пройден. Теперь просто щелкаем мышкой один раз по одному из рядов данных на диаграмме(выделятся все столбики данного ряда). Правая кнопка мыши: Excel 2003 - Тип диаграммы; Excel 2007 - Изменить тип диаграммы для ряда(рис.1). 

рис.1

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

В результате небольших экспериментов можно получить что-то вроде чудовища на рис.2. 

рис.2 
 
 
 
 
 

3.2 Динамическая диаграмма 

       В диаграммах Excel есть один небольшой минус. Если Вы построили диаграмму на основе данных, которые планируете добавлять со временем, то Вам по мере добавления данных в таблицу, на которой основана диаграмма, придется также менять диапазон данных для диаграммы, чтобы включить их в отображение. Либо сразу указать заведомо больший диапазон, но тогда диаграмма весьма некрасиво выглядит. Это не такая уж и проблема, если Вы добавляете данные один раз в месяц. А если это необходимо делать каждый день? Или несколько раз в день? 

       Итак, у Вас имеется таблица  в столбцах A, B и С - Дата, Количество  посетителей и Количество просмотров  соответственно. Создаем на основании данной таблицы диаграмму. Для того, чтобы создать динамически обновляемую диаграмму, Вам понадобиться создать динамический именованный диапазон из таблицы данных: Ctrl+F3-в поле диапазон пишем формулу =СМЕЩ(Таблица!$A$2:$C$2;;;СЧЁТЗ(Таблица!$A$1:$A$1000);). В поле Имя - желаемое имя для данного диапазона, к примеру - Таблица. 
 

Подробней о  создании динамических именованных  диапазонов см.здесь.  

       Теперь жмём правой кнопкой мыши по диаграмме: для Excel 2007 - Выбрать данные; для Excel 2003 - Исходные данные и в поле Диапазон данных для диаграммы просто вписываем имя динамического диапазона(Таблица). Ок. Все, теперь при добавлении данных в таблицу, данные в диаграмме тоже добавятся. 
 
 
 
 

4 Условное форматирование

        4.1 Выделение текущей даты

        4.2 Выделение различий в ячейках по условию

        4.3 Выделение строк через одну 

4.1 Выделение текущей даты 

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

выделяем столбец  с датами

для Excel 2003 - Формат - Условное форматирование; Excel 2007 - Главная - Условное форматирование - Создать правило

выбираем формула(для  Excel 2007 - Использовать формулу для определения форматируемых ячеек)

вписываем в  поле условие - =$B2=СЕГОДНЯ()

выбираем способ форматирования ячеек(в примере  цвет заливки - красный)

жмем Ок. 

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

4.2 Выделение различий в ячейках по условию 

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

выделяем всю  таблицу

для Excel 2003 - Формат - Условное форматирование; Excel 2007 - Главная - Условное форматирование - Создать правило

выбираем Формула(для  Excel 2007 - Использовать формулу для определения форматируемых ячеек)

вписываем в  поле условие - =$A1<>$B1

выбираем формат(в  примере это цвет заливки - красный)

жмем Ок. 

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

4.3 Выделение строк через одну 

  Если данных  в таблице много, то для более  удобного просмотра можно выделить  строки через одну - "Zebrой". Т.е. одна строка не закрашена, следующая закрашена, затем опять не закрашенная и т.д. Чтобы сделать это совсем не обязательно писать макросы. Достаточно воспользоваться встроенным в Excel Условным форматированием. Для этого:

1.выделяем необходимый диапазон

2.для Excel 2003 – Формат - Условное форматирование; Excel 2007 – Главная - Условное форматирование - Создать правило

3.выбираем формула(для  Excel 2007 - Использовать формулу для определения форматируемых ячеек)

4.вписываем в  поле условие - =ОСТАТ(СТРОКА();2)

5.выбираем способ  форматирования ячеек(в примере  цвет заливки - голубой) 

6.жмем Ок. 

5 Макросы(VBA процедуры)

        5.1 Выделение сделанных на листе изменений

        5.2 Выделение строк "зеброй"

        5.3 Ведение журнала сделанных в книге изменений

        5.4 Запись изменений на листе в примечания

        5.5 Как собрать данные с нескольких листов или книг?

5.6 Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки

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

5.1 Выделение сделанных изменений 

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

Option Explicit

Dim vValue

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Count > 1 Then Exit Sub

   If Target <> vValue Then Target.Interior.Color = vbRed

End Sub 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   If Target.Count = 1 Then vValue = Target

End Sub 

Вставляем код: Правый щелчок мыши по ярлычку листа(изменения  в котором Вы хотите отследить) - Исходный текст - Вставляем приведенный код. Подробнее о вставке кода в модули листа см.здесь. 

Но приведенный  код работает только в одном листе(том, в модуле которого размещен код). Если Вы хотите отследить изменения во всех листах книги, то воспользуйтесь следующим кодом:

Option Explicit

Dim vValue

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

   If Target.Count > 1 Then Exit Sub

   If Target <> vValue Then Target.Interior.Color = vbRed

End Sub 

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

   If Target.Count = 1 Then vValue = Target

End Sub 

Правда вставлять  этот код надо уже не в модуль конкретного листа, а в модуль книги. 
 

5.2 Выделение строк Zebrой 

       Допустим у Вас есть большая  таблица с данными. В одном  столбце таблицы находятся данные  по артикулам. В остальных имеется  остальные данные по ним - операции, дата/время, сумма товара и т.д. Просматривать такую таблицу глазами неудобно: каждый раз приходится сверяться - а в той ли строке я смотрю? Да и разбить данные на отдельные блоки по идентичным артикулам не мешало бы. Но как? Как разбить не подскажу, а вот раскрасить можно. Один артикул в один цвет, другой в другой. И так по очереди - то один, то другой. Zebra, одним словом. Чтоб было понятней см. рис.1. 

рис.1  

Для этого надо всего лишь создать стандартный  модуль и вставить в него следующий код: 

Option Explicit

Sub Zebra()

  Dim li As Long, lColor As Long, lColNum As Long, lColEND As Long

  lColor = xlNone

  On Error Resume Next

  lColNum = InputBox("Укажите номер столбца со значениями", "Окно ввода параметра", 1)

  If lColNum = 0 Then Exit Sub

  If Not IsNumeric(lColNum) Then Exit Sub

  On Error GoTo 0

  lColEND = Cells(1, Columns.Count).End(xlToLeft).Column

  Application.ScreenUpdating = False

  For li = 2 To Cells(Rows.Count, lColNum).End(xlUp).Row

    If Cells(li, lColNum) <> Cells(li - 1, lColNum) Then

      If lColor = xlNone Then lColor = vbGreen Else lColor = xlNone

    End If

    Range(Cells(li, 1), Cells(li, lColEND)).Interior.Color = lColor