Создание каталогов в Excel (электронная библиотека) I

Создание каталогов Excel (электронная библиотека)Создание каталогов в Excel (электронная библиотека) I часть.

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

Что это, например, может быть? Давайте подумаем, чего на компьютере может храниться очень много, и требует систематизации? У меня, например, это — электронные книги, фотографии, аудио и видео записи.

Что нам даст создание списка и его систематизация? Облегчение поиска необходимого в дальнейшем. Допустим, нам захотелось послушать спокойную музыку. Если у нас есть список, то мы просто отфильтровываем его по признаку «Спокойная музыка«, и получаем то, что требуется.

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

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

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

Итак, приступим.

Продумывание формы каталога

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

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

Будем создавать каталог электронных книг. Не знаю, как у вас, а у меня таких скопилось великое множество. И хранятся они, признаюсь, в совершенном беспорядке. Единственное, что более-менее упорядочено, это электронная библиотека книг в формате FB2. Я уже и не скажу, где именно искать ту или иную книгу.

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

Все эти признаки будут у нас колонками таблицы-каталога:

  1. ФИО автора — сортировка по автору используется чаще всего;
  2. Название книги — для поиска конкретной книги;
  3. Год написания — иногда бывает необходимо;
  4. Год издания;
  5. Издатель;
  6. Жанр — по нему будем сортировать, когда есть желание почитать из определенного жанра;
  7. Формат (аудио/текст) — иногда хочется не читать, а слушать;

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

Создание таблицы

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

Чем меньше данных мы будем вводить вручную с клавиатуры, тем лучше. Поэтому создадим отдельный лист, на котором будем вводить списки, из которых настроим выбор значений. Пока это колонки «Жанр» и «Формат (аудио/текст)». Однако по жанру сделаем две ступени группировки, иначе список получится слишком большим и им неудобно будет пользоваться.

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

Создание каталогов в Excel (электронная библиотека) II

Создание каталогов Excel (электронная библиотека)Создание каталогов в Excel (электронная библиотека) II часть.

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

Осталось совсем немного — настроить выбор из списка в колонках «Жанр» и «Формат«, и наполнить таблицу содержанием.

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

Начнем со списков.

Создание списков

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

Обратите внимание, записи из списка «Группа1» являются заголовками трех остальных списков (по количеству записей в первой группе). Каждый из списков вставьте в таблицу и дайте им в точности те же имена, что написаны в заголовках.

Впоследствии, если список «Группа1» у вас будет дополняться, то каждой новой записи должна соответствовать отдельная таблица с точно таким же именем.

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

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

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

Заполнение каталога и настройка ссылок

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

В открывшемся окне должен быть активирован в колонке «Связать с:» пункт «файлом, веб-страницей«. Ближе к правому краю находим иконку открытой желтой папки (Поиск файла) и жмем ее. Во втором открывшемся окне, во-первых,  находим кнопку выбора отображаемых форматов файлов, и выбираем пункт «Все файлы«. Находим файл электронной книги и жмем ее. Нажимаем «ОК«.

Можно проверить — работает ли гиперссылка. Проделываем аналогичную операцию со всеми остальными книгами.

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

С помощью поиска (кнопку «Найти и выделить» в правой части закладки меню «Главная«) можно отыскать необходимую запись по названию или автору.

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

Календарь в Excel

Календарь в ExcelРазличных офисных приложений с календарями немало.

Но, возможно, кому-то придется по вкусу нехитрый календарь в Excel, дополненный полезным функционалом. Скачать файл можно здесь.

Опишу, что он умеет:

Как видно из рисунка, календарь охватывает три месяца – прошлый, текущий и следующий.

Смена месяцев происходит автоматически. Также автоматически происходит и смена года. То есть 1 января 2016 года заголовок сменится на число 2016.

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

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

Все числа, соответствующие субботе и воскресению окрашены красным цветом.

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

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

Оба списка на листе «Памятные» расширяются автоматически при добавлении записей.

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

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

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

Пользуйтесь на здоровье :-)

Защита от ошибочного ввода данных

Защита от ошибочного ввода данных ExcelЗащита от ошибочного ввода данных в Excel.

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

То букву вместо цифры занесут, то пробелов понаставят.

А дело было очень ответственное — все-таки таблицы расчета заработных плат сотрудников. Сами понимаете — малейшая ошибка, и демотивированный сотрудник нам обеспечен. Excel позволяет максимально снизить вероятность подобных ошибок. И, поверьте моему печальному опыту, этим лучше не пренебрегать.

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

Для начала, необходимо ограничить ввод числовых значений. Другими словами — сделать так, чтобы в ячейки для чисел невозможно было ввести буквы или слова. Кроме того, не помешает, если мы запретим вносить числа больше 24. Если руководитель напишет число 41, то, очевидно, что это опечатка.

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

В поле «Тип данных» выбираем пункт «Действительное«, если допускается внесение дробных значений, или «Целое число«, если дробные значения не допускаются.В поле «Значение» выбираем «Между«. В поле «Минимум» вносим «0«, в поле «Максимум» вносим «24«.

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

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

Теперь идем на страницу табеля, выделяем интересующие нас ячейки, и снова выбираем пункт «Проверка данных«. В поле «Тип данных» выбираем пункт «Список«. В поле «Источник» вносим после знака «=» имя нашего списка.

Если вы отмените галочку в пункте «Игнорировать пустые ячейки«, то программа не даст оставлять ячейки пустыми. Этого нам не надо. А вот в пункте «Список допустимых значений» нам галочка ни к чему. Если мы ее оставим, то в ячейке появиться кнопка выпадающего списка, из которого необходимо будет выбирать необходимое значение. Поскольку список у нас очень большой, то выбор из списка будет неудобным. Кроме того наши ячейки очень узкие, и кнопка выпадающего меню будет только мешаться (см. картинку справа).

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

Так настраиваем всю таблицу.

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

Но предварительно необходимо отметить ячейки, изменения в которых мы хотим разрешить. Выделяем все эти ячейки, и нажимаем правую кнопку мыши. Откроется контекстное меню. Выбираем пункт «Формат ячеек«. Открываем закладку «Защита«.

По умолчанию, во всех ячейках стоит галочка напротив пункта «Защищаемая ячейка«. Эту галочку мы отжимаем, разрешив, таким образом, изменения в выделенных ячейках. Нажимаем «ОК«. Теперь можно заняться защитой листа.

После выбора команды «Защитить лист» открывается окошко, позволяющее выбрать параметры защиты:

Назначить пароль я рекомендую, а прочие пункты можно оставить в том виде, в котором они заданы. Нажимаем «ОК«, и проверяем. Попробуйте внести какую-нибудь запись вне того блока, который мы выделяли. Должно появиться окошко, как на рисунке. Если появилось, значит все проделано верно.

Excel для начинающих II

Excel для начинающихExcel для начинающих II часть.

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

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

Использование формул и ссылок

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

  • формула вносится в ту ячейку, в которой ожидается увидеть результат вычислений по этой формуле;
  • формула всегда начинается со знака «=»;
  • в формулах используются обычные арифметические операторы.

Если, например, мы внесем в ячейку формулу =(6+2)*12, то после нажатия клавиши Enter, увидим в этой ячейке не формулу, а конечный результат вычислений — 132. При этом, обратите внимание, если вы установите табличный курсор на это значение, то в строке формул отобразится не результат вычислений, а формула, которую мы внесли.

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

Кроме того, бывает удобно дублировать значение в одной ячейке на другую ячейку. Это тоже осуществляется с помощью знака «=» и указания адреса ячейки, значение которой мы хотим дублировать. Теперь, если там, куда ведет ссылка, значение будет изменено, то и в этой ячейке оно изменится тоже.

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

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

Использование функций

Для облегчения сложных расчетов, Excel предлагает целую библиотеку встроенных функций.

Они сгруппированы по типам:

  • Математические;
  • Финансовые;
  • Текстовые;
  • Операции с датами;
  • Логические;
  • Операции со ссылками и массивами;
  • Прочие.

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

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

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

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

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

Excel для начинающих I

Excel для начинающихExcel для начинающих I часть.

Тем, кто только начал изучение этой удивительной программы, можно только позавидовать.

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

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

Но, чтобы начать использовать все эти возможности, необходимо научиться хотя бы самым азам работы в Excel. Этому и посвящена статья.

Файл MS Excel имеет расширение xlsx (в версиях до 2007 года файлы MS Excel имели расширение xls). Эти файлы называют рабочая книга, которая, в свою очередь, состоит из рабочих листов. Каждый рабочий лист, это разбитая на колонки и строки таблица, в каждую ячейку которой можно вносить тексты, числа и формулы. Количество ячеек на каждом листе более 17 триллионов, чего вполне достаточно для самых объемных таблиц.

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

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

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

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

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

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

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

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

Нередко в Excel приходится оперировать не отдельными ячейками, а целыми диапазонами — выделенной группой ячеек. Адрес выделенного диапазона указывается путем указания адреса верхней левой его ячейки, и, через двоеточие адрес нижней правой ячейки этого диапазона. Например Е2:D5.

Для начала, это — все. В следующей статье мы перейдем к сути Excel, а именно — использованию формул и функций.

Использование макроса для автоматизации повторяющихся операций Excel

ExcelИспользование макроса для автоматизации повторяющихся операций Excel.

Это страшное слово «Макрос», на самом деле, не такая уж и сложная штука.

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

Для этого MS Excel имеет очень интересный инструмент, под названием макрорекордер — надстройка, которая включается через меню параметров Excel.

Нажимаем кнопку office в левом верхнем углу и выбираем пункт «Параметры Excel«. В открывшемся окне уже будет активна вкладка «Основные«. Находим пункт «Показывать вкладку «Разработчик» на ленте«, и ставим галочку в окошке слева от него. На ленте меню появится вкладка «Разработчик«. Ею мы и будем пользоваться.

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

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

Итак, мы имеем прайс-лист, который нужно раскидать по листам в соответствии с группой клиентов (см.рисунок. как вы понимаете — компания вымышленная, и даже цены по всей номенклатуре установлены нереальные). Алгоритм действий несложен:

Все просто, но, когда таких листов несколько, а изменения случаются едва ли не каждый день, только тем и занимаешься, что правишь прайсы. Поэтому запишем макрос с помощью макрорекордера. Но, прежде чем мы приступим, необходимо сохранить файл в формате «Книга Excel с поддержкой макросов» через меню «Сохранить как«. В противном случае алгоритм, который мы запишем, не сохранится. Затем приступает к записи:

Макрос готов. Давайте проверим — удалим со всех листов, кроме «Свод» все данные, и нажмем сочетание клавиш Ctrl+п. Как видите — все работает. Теперь, чтобы обновить прайс-листы, не нужно будет нудно выполнять одни и те же операции.

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

Сводные таблицы в Excel

Сводные таблицы в ExcelСводные таблицы Excel.

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

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

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

К примеру:

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

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

Вставка сводной таблицы

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

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

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

Настройка полей сводной таблицы

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

Например, нам необходимо определить, какие суммы затрачиваются на выплату окладной части заработной платы по управлениям. Управления будем размещать в строках. Поэтому захватываем мышкой поле «Управление» и переносим его к области «Названия строк«. Теперь, поскольку нам необходимо вычислить затраты по окладной части, захватываем поле «Оклад«, и переносим его к области «Значения«. Вот что у нас получилось:

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

Правильнее будет, если отделы мы разместим в строках и сгруппируем по управлениям. Захватываем поле «Отдел» и переносим на поле «Названия строк» ниже поля «Управление«. Согласитесь, в таком виде таблица становится намного понятнее.

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

Можно разместить колонки «Оклад» и «Премия» рядом. Если, при добавлении этих полей, они размещаются в одну колонку, посмотрите на область «Названия строк«. У вас там должен появиться пункт «∑ значения«. Переместите его к области «Названия столбцов»

Настройка фильтра сводной таблицы

Предположим, нас не интересуют никакие иные управления, кроме управления маркетинга. Чтобы отфильтровать значения по какому либо из значений поля «Управление«, перенесем это поле к области «Фильтр отчета«. Это поле появится в верхней части сводной таблицы. Нажимаем на кнопку выбора из списка (серый квадратик с черным треугольничком), и выбираем пункт «Управление маркетинга«.

Кстати, если есть желание отфильтровать по нескольким подразделениям, то для этого есть специальная галочка в нижней части диалогового окна. А в этот раз жмем «ОК«, и получаем значения, отфильтрованные по критерию «Управление маркетинга».

Вставка формул в сводную таблицу

Теперь было бы неплохо посчитать сумму выплат оклада и премии. Это можно сделать с помощью формулы в сводной таблице:

  • устанавливаем табличный курсор в любом поле сводной таблицы;
  • жмем вкладку меню «Параметры«;
  • выбираем пункт «формулы«-«вычисляемое поле«.

В открывшемся окне заносим имя нашего поля (я назвал «Общая ЗП»), а в пункте «Формула» заносим операции с интересующими нас полями: =Оклад + Премия. Жмем «ОК». В области значений появляется требуемый результат.

Изменение параметра полей значений и оформление

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

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

Настройки представления таблицы можно осуществить во вкладке меню «Конструктор«. Я, например, выбрал «Макет отчета» — «Показать в сжатой форме» и поставил галочку в пункте «Чередующиеся строки«. Вот такая таблица у меня получилась:

и вот такое у нее размещение полей по областям.

Функции проверки свойств и значений

Функции проверки свойств и значений ExcelФункции проверки свойств и значений в Excel.

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

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

Синтаксис этих функций — простейший. Пишется через знак равно имя, и в скобках указывается ссылка на ячейку, которую необходимо проверить. Основную проблему у новичков вызывает вопрос, как их использовать. Между тем, во многих случаях, без них обойтись бывает сложно.

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

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

Нам, на основании этих данных, необходимо заполнить законодательно установленную форму табеля рабочего времени. Проблема заключается в том, что, в табеле необходимо указать и код рабочего времени, и количество отработанных часов. Как их развести?

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

Пишется эта формула так: =ЕСЛИ(ЕТЕКСТ(C3);C3;»Я»).

Для отображения только числовых значений лучше использовать функцию ЕЧИСЛО: =ЕСЛИ(ЕЧИСЛО(C3);C3;»»). Пустые кавычки вставят пустое значение, если проверяемая ячейка не будет числом.

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

Иногда в формулах необходимо предусмотреть возможность появления ошибки. Например, если есть вероятность деления на 0. Поскольку делить на 0 нельзя, то Excel выдаст значение «#ДЕЛ/0! «. Если на эту ячейку ссылаются еще и другие формулы, то все расчеты выдадут информацию об ошибке, то есть никаких расчетов не будет.

В этом случае можно использовать формулу: =ЕСЛИ(ЕОШИБКА(A1/B1);0;A1/B1). Логика ее такова:

Пример использования функции ЕПУСТО описан у меня в статье «Оформление командировки в Excel«. Мы, с помощью нее, добились отображение пустой ячейке, если в ячейке, на которую ссылались, тоже не было никаких записей. В противном случае, ссылка на пустую ячейку выводила бы цифру 0.

Использование функций ЕЧЁТН и ЕНЕЧЁТ можно привести из области работы с номерами домов. Мы знаем, что, как правило, четные номера домов располагаются по одну сторону улицы, а нечетные — по другую. Допустим, у нас есть список, который необходимо рассортировать по сторонам А и Б.  В колонку А вносим четные номера, в колонку Б — нечетные.

Формулы, соответственно, будут такие:

А — =ЕСЛИ(ЕЧЁТН(C2);C2;»»);

Б — =ЕСЛИ(ЕНЕЧЁТ(C2);C2;»»).

На этом я заканчиваю цикл статей, посвященных начинающим любителям Excel.

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

Логические функции и сложные алгоритмы

Логические функции и сложные алгоритмы ExcelЛогические функции и сложные алгоритмы в Excel.

Логические функции Excel, пожалуй, одни из моих самых любимых.

Ведь с помощью них можно закладывать практически любые алгоритмы расчетов!

Фактически, мы занимаемся программированием, комбинируя различные функции друг с другом.

И это не более сложно, чем писать формулы сложения или умножения, о которых я писал в статье «Excel для начинающих II«.

Мы рассмотрим функции ЕСЛИ, И, ИЛИ.

Начнем с ключевой логической функции ЕСЛИ. Она, фактически, задает алгоритмы, которые многие учились строить на информатике в школе.

Структура ее такова:

Выглядеть это будет так: =ЕСЛИ(A1>10;»Много»;»Мало»).

Внесем эту формулу в ячейку А3. А в ячейке А1 запишем число 5. Должно появиться слово «Мало». Исправим 5 на 50. Появится слово «Много». Все работает!

Очень часто, одного условия бывает недостаточно.

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

Логика очевидна — если разряд 4-й, то платим 4000 руб, если 3-й3000 руб, и т.д. Вот как это выглядит в формуле (для знающих оговорюсь — мне известно, что оптимальнее, в данном случае, использовать ВПР :-)): =ЕСЛИ(C8=A2;B2;ЕСЛИ(C8=A3;B3;ЕСЛИ(C8=A4;B4;ЕСЛИ(C8=A5;B5;0)))). Лично я, для себя, когда начинаю писать нечто подобное, чтобы не сбиться, начинаю про себя проговаривать алгоритм (читаем формулу слева на право):

Таким образом у нас получилось четыре условия ЕСЛИ, вложенных друг в друга.

Эти две функции, как правило, используются как вспомогательные для функции ЕСЛИ. С помощью них нельзя вызвать какое-либо значение, так как они просто проверяют — истинны или ложны условия, которые в них заложены.

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

То есть, только при соблюдении двух условий одновременно.

Напишем формулу: =ЕСЛИ(И(F8=»Да»;G8=»Нет»);5000;0). Пропишем текстом:

Это же условие можно прописать и с помощью функции ИЛИ. Просто мы пойдем от обратного:

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

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