СОДЕРЖАНИЕ
Общая структура ExcelНастройки - установка чешского языка
Принцип совместной работы
Знакомство с закладкой Domů
Знакомство с листами
Строки и столбцы. Решетка, строка формул. Отмена действий
Ячейка (buňka). Вид ячеек. Базовая харатеристика ячейки (формат-значение)
Копирование, форматы ячеек
Ссылки. Относительная и абсолютная ссылка. Протягивание. Протягивание с автозаполнением. Создание своего списка заполнения
Добавление коментария / примечания
Фильтрация и сортировка данных
Условное форматирование
Базовые формулы - СУММА, СРЕДНЕЕ, МАКС, МИН
Сводная (контингентная) табличка. Выделение больших данных
Подготовка итоговых данных. Область печати - масштабирование. Печать в пдф
Транспонирование данных, специальные вставки
Линия тренда
Круговая диаграмма
График - карта
Поиск/замена значений в таблице
Удаление дубликатов в таблице
Группировка строк (столбцов). Итоговая таблица
Работа с формулами для дат, времени и текста
Дополнительные формулы - A(), NEBO(), MOD(), SUMMIF(), POČETIF(), RANDBETWEEN(), SVYHLEDAT()
Вставка специального символа. Вставка формул. Вставка объектов
Установка пароля на открытие листа
praktikum_vzor
Введение
Друзья! Перед вами - программа Microsoft Excel. Для кого-то эта программа новая, для кого-то, возможно, давно знакомая. Факт остается фактом в том, что эту программу используют все, кто так или иначе работает с цифрами или данными. И не важно, используете ли вы расчёты для своих, домашних целей, или в своей работе. Сегодня Excel - это база и основа компьютерной грамотности.Наша цель - познакомить вас с самыми базовыми функциями программы и научить вас быстро и эффективно работать в ней. Наши занятия практические - то есть всё, что необходимо знать, вы сразу же тренируете с преподавателем на практических уроках. В данном уроке - лишь базовая информация, которая показывается на уроках. Здесь - своего рода, конспект с самым необходимым для того, чтобы можно было быстро вспомнить теорию на уроке.
Курс расчитан на самую широкую аудиторию, а именно на людей, которые никогда не работали, либо работали в Экселе, но хотели бы освежить свои знания, либо узнать что-то новое о программе - то, что позволит чувствовать себя более уверенно при работе с программой.
Поскольку главная задача языковой школы - обучения чешскому языку, особенностью именно нашего курса является не просто знакомство с программой, как таковой, а конкретно с её чешской версией. Таким образом, вы не просто учитесь работать в экселе, но учитесь это делать в Чехии!
Курс рассчитан примерно на 4 урока (5 часов) для среднего темпа обучения. Но мы понимаем, что темп обучения и усвоения материала у каждого студента может быть разный, итоговое количество практических часов может отличаться.
Желаем вам удачи в освоении программы Excel!
Общая структура Excel
Если откроем стандартный лист Excel то увидим примерно следующую структуру:- область сохранения, отмены, возврата действия (uložení, zrušení, vrácení)
- область поиска (vyhledání)
- закладки (záložky)
- рабочая панель (pracovní lišta)
- область формул (oblast vzorců)
- рабочее пространство (pracovní oblast listu)
- закладки листов (záložky listů)
- области прокрутки - горизонтальной и вертикальной (vodorovné a svislé listování)
- область масшатбирования (oblast měřítka)
Настройки - установка чешского языка
Для начала сделаем следующее - установим чешский язык (если не установлен). Делается это так - Файл (File) - Настройки (Settings) - Язык (Language). Далее выбираем язык пользовательской среды. На первое место пермещаем чешский. Если его нет в списке - то устанавливаем - Устанавливаем язык Office. Не забываем в конце нажать ОК. Если язык не поменялся, можно перезагрузить Excel.
Принцип совместной работы
Для совместной работы с преподавателем переходим по ссылке, которую вам предварительно пошлет преподаватель. Откроется онлайн-версия экселя. Далее нажимаем на кнопку Переключить на классическую (десктопную) версию Excel.
Знакомство с закладкой Domů
Закладка Домой (domů) является базовой, первой в выборе закладок
Закладка состоит из раличных областей, необходимых для изменения/редактирования стиля отображения ячеек и других базовых функций при работе.
Основные области:
- область копирования / выделения / вырезания / вставки
- область настроек шрифта и отображения ячеек
- область объединения
- область выбора формата значений
- область стилей форматирования
- область работы с ячейкой
- область изменения данных - поиск, фильтрация и тд.
- область чувствительности, дополнения данных и создания pdf)
Знакомство с листами
Лист - это как страница в книге. Это то место, где и совершаются все вычисления. С листами мы (как и со страницами в книге) можем совершать различные операции - вложить новый лист, удалить, переименовать, поменять порядок, закрыть на пароль, сделать другой цвет закладки листа, скрыть (потом можно и отобразить через функцию отобразить). Можем выбрать все листы, привязать лист - то есть сгенирировать ссылку именно на этот лист в книге. Функция создания вынесено отдельно в плюсик справа. Также по листам мы можем свободно перемещаться нажимая соответствующие закладки.
Строки и столбцы. Решетка, строка формул. Отмена действий
Рабочее пространство листа создается строками и столбцами. Строки и столбцы мы можем выделять (как 1 так и несколько) и совершать над ними различные операции. Выделение происходит нажатием на номер строки или букву столбца (номер или буква загораются зеленым цветом при наведении). Выделить несколько строк (столбцов) мы можем нажав на первый и протянув вверх/вниз (вправо/влево) при нажатой левой кнопки мыши.
Главные операции над строками и столбцами мы можем выполнять при выделенных строках/столбцах нажатием правой клавиши. Далее у вас появляется выбор из следующих операций:
вложение строки / столбца
вложение строки / столбца
удаление содержимого строки / столбца
формат всех ячеек строки / столбца
установка высоты строки / ширины столбца
скрытие строки / столбца
отображение строки / столбца
При выделении нескольких строк / столбцов операции будут выполняться по отношению ко всем ним.
Также при желании мы можем скрыть или отобразить решетку ячеек по умолчанию - для этого проверяем галочку на Решётку (mříška) в поле Отображение (Zobrazení). Таким же образом можем поступить и с полем для формул (řádek vzorců) если оно не нужно.
Любое действие в экселе мы можем отменить, нажатием ctrl + Z (должна быть активной английская или чешская раскладка клавиатуры). или же стрелочками в левом верхнем углу окна. Левая стрелка (против часовой стрелки) - отмена последнего действия, правая стрелка (по часовой стрелке) - возврат отменённого действия.
Ячейка (buňka). Вид ячеек. Базовая харатеристика ячейки (формат-значение)
На пересечении строки (строк) и столбца (столбцов) возникает базовая единица Экселя - ЯЧЕЙКА (buňka).Ячейка может быть образована одной или несколькими строками и столбцами. Составную ячейку (из более одной строки и столбца) можно сделать объединив несколько ячеек через команду "Объёдинить и выровнять по центру" (sloučit a zarovnat na střed).
Каждая ячейка в Экселе имеет свой адрес, образованный пересечением соответствующей строки и столбца. Узнать адрес активной ячейки можно в поле слева от области формул. Адрес и свойства составной ячейки характеризуется левой верхней ячейкой.
Ячейка характеризуется 2мя главными свойствами - форматом и значением.
Формат определяет внешний вид ячейки и ее содержимого
Значение определяет тот формат данных и его значение (величина), которую содержит ячейка.
Копирование, форматы ячеек
Ячейки можно копировать и вставлять в другие ячейки. Это можно совершать через сочетание клавиш ctrl + C (копирование) и ctrl + V (вставка) либо протягиванием ячейки за правый нижний угол в любую сторону (при наведении появляется плюсик). В этом случае соседняя ячейка получит формат и значение протягиваемой.При желании мы можем вставить только формат или только значение, если это необходимо. Для этого необходимо совершить вставку через правую клавишу мыши - вставка формата (символ с кисточкой) или вставка значения (символ с 123)
Формат ячейки включает в себя:
цвет содержимого и цвет фона
размер и стили (шрифт, подчеркивание и тд.) содержимого
выравнивание содержимого по вертикали и горизонтали
стиль границ ячейки
Форматировать можем как отдельную ячейку, так и выделенный диапазон из ячеек. Лайфхак: Можно также выделить несколько диапазонов через клавишу ctrl! После выделения необходимых ячеек форматировать стиль ячейки можно либо нажатием правой клавиши из дополнительного контекстного меню либо на рабочей панели в закладке Домой (domů) в левой части:
Формат значения можно также устанавливать вручную. Существуют следующие форматы:
Общий (obecný) (без конкретного формата - по умолчанию)
Числовой (číslo) - можно делать отрицательные значения красными без минуса по умолчанию. Также можно указывать количество десятичных мест после запятой и отделитель 000 пробелами по умолчанию
Валютный (měna) - с выбором значка валюты
Бухгалтерский (účetnícký) - похожий на валютный формат, но с некоторыми различиями, а именно:
Дата (datum) - возможна настройка отображения дня, месяца, года через собственный формат (vlastní):
d - день без нуля в начале, dd- день с нулем в начале, ddd - сокращённый день недели (po, út, st...), dddd - полный день недели (pondělí, úterý и тд.)
m - месяц без нуля в начале, mm - месяц с нулём в начале, mmm - римское отображение номера месяца ( VI, IX и тд.), mmmm - полное название месяца (leden, únor и тд.)
rr - отображение года в сокращённом формате - 25, 19 и тд, rrrr - полное отображение года, 2025, 2019 и тд.
В остальных случая есть возможность выбора из стандартных форматов отображения дат:
Время (čas) - возможность отображения с указанием времени суток или без (AM - PM)
Проценты (procenta) - добавление значка процента %.Можно добавить значок процента вручную. Тогда ячейка автоматически приобретает процентный формат. Для перевода числового формата в процентный - то есть, например 15 в 15% число нужно сначала разделить на 100!
Дроби (zlomky) - (со слэшем в виде знака дроби) - если необходимо сохранение знака дроби
Математический (matematický) - используется для отображения больших (или маленьких чисел). Например, число
123456789 будет отображаться как 1,23E+08а число 0,0000123 → 1,23E-05. Использует экспоненциальную запись (E означает "умножить на 10 в степени"). Удобен для научных и инженерных расчетов.
Текстовый (text) - любые символы будут восприниматься как текст, даже если там будут числа! Лайфхак: чтобы преобразовать "текстовое" число в числовое число - можно умножить текст на 1! Еще один лайфхак: чтобы перенести текст на следующую строку, нужно нажать клавижу Enter при нажатом alt!
Специальный (например для индекса PSČ) - бывает удобно для автоматического отделения пробелом. В Чехии почтовый индекс (poštovní směrovací číslo, PSČ) обозначается в формате XXX XXX.
Ссылки. Относительная и абсолютная ссылка. Протягивание. Протягивание с автозаполнением. Создание своего списка заполнения
Помимо указанного выше метода копирования через ctrl + c - ctrl + v можем копировать через протягивание (protahování).Для этого нужно навести на нижний правый угол ячейки, нажать левую кнопку мыши, и удерживая, протянуть в нужную сторону - туда, где хотим видеть новые ячейки. При протягивании копируется как значение, так и формат исходной ячеки.
Если в исходной ячейке содержится значение, то Эксель просто скопирует это значение вместе с форматом и поместит в новую ячейку (или ячейки). Протягивать можно в любую сторону - вниз, вверх, вправо, влево.
Если источником является диапазон значений - Эксель будет искать зависимость между значениями диапазона и вставлять новое значение, связанное данной зависимостью. Такой зависимостью может быть числовая последовательность, продолжение названий дней недели, названий месяцев и так далее. Если такой зависимости по умолчанию нет, Эксель будет повторять протягиваемые значения, но зависимость можно создать через редактирование списков. Для этого выбираем Файл (Soubor) - возможности (možnosti) - уточнить (upřesnit) и далее Редактировать собственные списки (upravit vlastní seznamy). Далее в окне мы можем увидеть список всех списков, и возможность добавления собственного списка (Nový seznam). Новый список можно импортировать (importovat) через выделение из диапазона, предварительно создав его в рабочей области.
После сохранения нового списка, Эксель будет понимать новую зависимость и сможет автоматически заполнять данные при протягивании. Новые области, возникшие в процессепротягивания, отображены коричневым цветом - см. рисунок ниже.
Помимо значений - числовых или текстовых - ячейки могут содержать ссылки на другие ячейки. Для того, чтобы сослаться на другую ячейку, нужно ввести знак равно (se rovná) = и далее нажать на источник (zdroj). В нашей ячейке после знака равно мы увидем адрес источника (D70). При нажатии на Enter мы увидем то же значение, что и в источнике. При этом формат источника будет свой, а ссылки - свой.
Но что, если проятнуть ссылку? В этом случае в протянутой ячейке возникнет следующая ссылка, которая будет ссылаться на ячейку в направлении протягивания относительно источника. Такая новая ссылка называется относительной. Если же мы хотим, чтобы при протягивании и обычном копировании, мы всегда ссылались только на ячейку-источник, то протягиваемая (копируемая) ссылка должна содержать адрес источника со знаками $ (доллара). Лайфхак: для установки долларов - в строке формул поставьте курсор на адрес ссылки и нажмите клавишу F4 (в самом верхнем ряду раскладки на клавиатуре) или при зажатой клавише fn если работаете с ноутбука.
При работе избегайте циклических ссылок!
Это ситуация когда ссылка ссылается на источник, а потом вы вдруг источник назначите ссылкой на ссылку! Эксель вас предупредит о циклической ссылке! Далее вам синими стрелочками отобразится место, где возникает циклическая ссылка. Помните! Если возникает циклическая ссылка, значит вы что-то делаете не так и следует проверить источники и ссылки внимательно еще раз!
Добавление коментария / примечания
В каждой ячейке можно создавать примечания (poznámka) и коментарии (komentář). Сделать это можно через нажатие правой клавиши мыши и выбрав соответствующую опцию
Основные отличия коментария от примечания - см сравнительную табличку:
Фильтрация и сортировка данных
Из ячеек состоят таблицы. Таблица содержит столбцы, которые должны содержать названия. Строки представляют категории и их данные:
Самая простая таблица - как пример. Сколько яблок есть у Маши, Пети и Паши:
Здесь левый столбец - категории имени. Правый столбец - количество яблок. Первая строчка содержит название для столбца (jméno, počet).
Самые базовые операции с таблицей - сортировка и фильтрация.
Допустим, мы хотим отсортировать записи о количестве яблок начиная с самого большого.
Первое, что мы должны сделать - выделить всю таблицу. Далее открываем закладку Данные (Data) и и примерно в середине панели находим сортировать (seřadit). Далее выбираем критерий по которому будет происходить сортировка (počet).
Если столбец содержит числа - то вам будет предложена порядок (pořadí) сортировки - от наименьшего к наибольшему (od nejmenšího k největšímu) и наоборот. Нас интересует порядок от наибольшего к наименьшему. Выбираем - ОК.
Полученная отсортированная таблица будет начинаться с записи с самым большим значением искомого столбца (počet). Затем - по убыванию значения.
Если столбец содержит текст - то сортировка может быть от A до Z - и наоборот.
Теперь допустим, что мы хотим отфильтровать таблицу по какому-либо критерию. К примеру, у нас есть таблица, где у Маши есть несколько записей с яблоками:
Наша задача - сделать так, чтобы в этой таблице остались только записи с Машей. Для этого используем инструмент Фильтр (Filtr) в закладке Данные (Data)
Сначала выделяем всю таблицу и выбираем Фильтр. В шапке таблицы в нижнем правом углу у нас появятся стрелочки. Нажав на стрелочку с именами, мы можем выбрать только те записи, которые будут касаться искомого имени (Marie), оставляем галочку только напротив этого имени
Далее нажимаем ОК и получаем отфильтрованную таблицу. Можно заметить, что в фильтруемой колонке в шапке таблицы у нас появился значок фильтра.
Отметим, что остальные записи никуда не делись, просто они нам не отображаются. Их можно легко вернуть двумя способами. Первый - отжав кнопку фильтра в рабочей панели, и второй - убрав фильтр, нажав на кнопку фильтра в самой таблице (zrušit filtr z "jméno").
Таким образом мы можем фильтровать таблицы по любому имени, значению и так далее. К значениям, можно подобным образом применять фильтры для операций "равно", "больше", "меньше" и так далее. Напрмер, мы можем отфильтровать записи по критерию "больше 6 яблок". Для этого выбираем в поле фильтр уже в поле počet. Далее выбираем возможность фильтра чисел (filtr čísel) и появившемся меню сбоку выбираем "больший чем" (větší než) и устанавливаем необходимое значение. Можно дополнительно настроить ограничение на наибольшее значение и так далее. Настраивать фильтр можно практически по любому критерию.
После нажатия ОК появляется итоговый результат
Условное форматирование
Помимо обычной сортировки и фильтрации мы можем совершать дополнительные операции с таблицами. Одна из таких полезных функций - это автоматическое отображение записей определенным образом по какому-либо критерию. Каким образом отображать и по какому критерию - зависит только от наших потребностей или фантазии. Такая функция в экселе называется Условное форматирование (podmíněné formatování). Допустим, у нас есть таблица с данными, сколько отработал каждый работник часов на каком-нибудь проекте.
Допустим, мы хотим выделить те значения часов, которые превышают границу, например 15 часов. Конечно, мы можем либо отфильтровать, либо отсортировать часы и таким образом будет видны люди их часы, удовлетвряющие данному критерию. Но что, если мы не хотим, либо не можем фильтровать или сортировать значения. К тому же, бывает полезно видеть часы остальных людей, но, если мы отфильтруем, то других людей не увидим в таблице. Для этих целей существует условное форматирование (podmíněné formatování).
Выделяем числовые значения в нашей таблице - нажимаем на кнопку Условное форматирование - и выбираем критерий - например "более чем". Можно выбрать и "менее чем", тогда нам отобразятся значения, меньше указанной границы.
После этого задаем критерий и стиль для отображения. Стандартный - светло-красный цвет фона и чёрный цвет текста.
После этого видим нашу таблицу с выделенными значениями выше критерия (в 15 часов).
Также легко мы можем и убрать форматирование с выбранных ячеек. Для этого выделяем все числовые значения в таблице, опять нажимаем на условное формтаирование - удалить правило (vymazat pravídla) - удалить правила с выбранных ячеек (vymazat pravidla z vybraných buněk).
Условное форматирование, конечно же на этом не заканчивается. Возможны различные варианты отображения - можно задать правила для отображения наивысшего или наинизшего значения, правила для первых 10 значений (лучших или худших), первых или последних 10% значений, диапазон выше или ниже среднего. Можно создавать шкалы данных с различными правилами и расцветками - например от красного к синему в градиенте в зависимости от того, где находится значение от минимума к максимуму и так далее. Возможности условного форматирования поистине безграничны!
Базовые формулы - СУММА, СРЕДНЕЕ, МАКС, МИН
Одной из главных задач программы Excel - это суммаризация данных и нахождение определенного интересующего нас как пользователей, показателя. Таких показателей может быть много, сегодня остановимся на саммых базовых. Для того, чтобы подвести статистику по данным, в экселе используются формулы. Формулы вводятся в ячейки через знак = либо в самой ячейке, либо в области формул.
Формулы могут быть встроенными и пользовательскими. Встроенные формулы обозначаются словом, например суммирование имеет встроенную формулу =SUMA() и так далее. Каждая встроенная формула это своего рода функция, которая имеет (либо иногда не имеет) свои аргументы. Аргументы задаются в скобках по определенным правилам для каждой конкретной формулы.
Еще одним вариантом вызова функции для конкретной ячейки это нажатие на значок fx слева от области формул. После этого появится окошко, где можно выбрать подходящую формулу из всех возможных формул. Здесь формулы сгруппированы по категориям - финансовые, обработка дат/времени, математические, логические и так далее. Либо можно выбрать из последних использованных (naposledy použité) (чтобы долго не искать)
Далее нажимаем ОК и далее вам отобразится диалоговое окно, в котором будут подробно описаны аргументы - то есть значения, над которыми будет работать эта формула. Здесь же можно и задать эти аргументы из рабочей области через значок ↑. Тогда указывать их в скобках уже не нужно.
Далее рассмотрим правила и примеры использования самых частых формул в Экселе.
Лайфхак - для того, чтобы не писать формулу целиком, достаточно написать знак равно и ввести несколько первых символов. Далее эксель вам предложит снизу варианты формул, которые начинаются с этих символов. Когда останется только 1 вариант - просто нажмите кнопку TAB на клавиатуре. Эта команда напишет вам формулу целиком, и даже поставит открывающую скобку. сэкономит вам время на ввод всей формулы
SUMA () - сумма значений
Вернемся к нашей табличке со списком сотрудников и их часов
Допустим нам нужно подсчитать сумму всех часов за всех сотрудников. Воспользуемся функцией =SUMA() = СУММ().
Внизу под таблицей, под колонкой jméno вводим SOUČET HODIN, а под колонкой počet вводим =SUMA(. Далее выбираем диапазон ячеек, которые нам необходимо просуммировать.
Далее нажимем Enter, при этом закрывающую скобку можно не вводить. Далее в ячейке появляется искомая сумма.
Суммирование можно проводить и не через диапазон, а суммировать отдельные ячейки. Для этого выбираем ячейку и в формуле пишем точку с запятой, далее выбирем следующую ячейку и так далее. Выбранные ячейки будут отображаться разным цветом. Такой выбор отдельных значений для анализа будет действовать и для всех других функций, рассматриваемых далее:
PRŮMĚR() - среднее значение
Подобным образом можем подсчитать и среднее значение по часам.
Итог:
MAX() и MIN() - максимальное и минимальное значение
Довольно полезными функциями в экселе могут быть функции нахождения максимума и минимума.
Результат -
POČET() - подсчёт количества значений
Теперь давайте подсчитаем, сколько вообще значений у нас в таблице. Для этого используется функция =POČET().
Результат -
При этом, если мы удалим какое-то значение, то и количество уменьшится:
KDYŽ() - условие "если"
Теперь представим, что нам нужно автоматизировать выбор людей, которые отработали больше или меньше определенного количества часов. Конечно же, мы можем использовать фильтрацию, или сортировку или условное форматирование. Но представьте, что нам нужна ячейка рядом с каждым человеком, содержащая значение - ДА(ANO) или НЕТ (NE), то есть соответствует ли данное конкретное значение критерию. Для этого будем использовать формулу с условием - =KDYŽ() или ЕСЛИ()
Давайте добавим колонку рядом с количеством часов počet, где возле каждой записи с часами отметим, соответствует ли данное количество критерию, допустим больше 20 часов. Если человек отработал больше 20 часов - будет ANO, иначе NE.
В общем виде формула KDYŽ() принимает следующие аргументы:
=KDYŽ(podmínka;ano;ne)
podmínka - это условие, которое мы будем проверять (>20)ano - что должно выполняться или отображаться в ячейке, если условие выполняется. В нашем случае должна появится запись ANO.
ne - что должно выполняться или отображаться в ячейке, если условие не выполняется. В нашем случае должна появится запись NE.
Давайте создадим формулу для первого человека -
Результат для значение 15 ожидаем, что должен быть NE, поскольку условие 15>20 не выполняется.
Далее мы должны применить формулу для всех остальных сотрудников. Естественно, мы не будем писать условие KDYŽ для всех строк. Всё что необходимо сделать - это протянуть ячейку с уже созданной формулой вниз до конца таблицы. Поскольку ссылка в формуле у нас относительная, она так же двигаться вниз вместе с протягиваемой ячейкой.
Таким образом мы можем устанавливать любой критерий и отображать любые значения в случае выполнения и не выполнения критерия
Сводная (контингентная) табличка. Выделение больших данных
Теперь давайте немного усложним задачу. Представим, что каждый работник работал на разных проектах и еще на протяжении нескольких месяцев и нам нужно подвести итоги, сколько каждый работник отработал на каждом проекте за каждый месяц. Конечно, можно сделать фильтр по месяцам и работникам и потом делать суммирование по каждому человеку и месяцу. Но это заняло бы очень долго времени и ресурсов. Гораздо проще можно это сделать с помощью сводной таблицы (kontingenční tabulka)Допустим, у нас есть массив данных по часам работников:
Первое, что мы должны сделать - это выделить всю таблицу, включая шапку.
Кстати, хороший лайфхак, как можно сделать это очень быстро не только с этой таблицей, а таблицей любого размера в течение буквально одной секунды! Ставим курсор на верхнюю левую ячейку таблицы. Далее зажимаем клавиши ctrl + shift. И на клавиатуре нажимаем постепенно (при нажатых ctrl+shift) стрелочка вправо и затем стрелочка вниз. Всё, вся таблица выделена!
Далее (не убирая выделения с таблицы) - идем во вкладку Вложение (vložení) - сводная таблица (kontingenční tabulka) - выбрать из таблицы или области (z tabulky nebo oblasti).
После этого появится диалоговое окно, в котором уже в качестве источника данных будет наша табличка (потому что мы ее выделили перед выбором сводной таблицы):
После этого мы можем выбрать возможность поместить нашу табличку или на новый лист, или на существующий. Если помещаете на существующий, тогда нужно выбрать ячейку в рабочей области, где будет левая верхняя часть помещаемой таблицы.
После подтверждения у нас появится пустая сводная таблица.
Далее необходимо из областей, где находятся названия колонок исходной таблицы перетянуть (с зажатой левой клавишей мыши) в соответствующие области новой таблицы - в нужную область итоговой (сводной) таблицы. Всего таких областей 4 - область фильтров, область колонок, область рядов, область значений. В качестве значений у нас будут часы (hodiny). Остальной выбор - зависит от ваших предпочтений, или того, как именно вы хотите видеть сводную таблицу. Если в качестве рядов нам необходимы имена людей, то перетягиваем колонку pracovník в область рядов. Тогда наша таблица на этот момент будет выглядеть так:
Но пока нам показываются люди и сумма их часов за все проекты и месяцы. Далее при дальнейшем перетягивании остальных колонок, наша новая итоговая таблица будет все более и более уточняться и детализироваться. Именно это и будем делать дальше.
Теперь общую сумму часов нам нужно разделить на месяцы и поекты. Делать это можно множеством способов. Для начала, можно перетянуть поле месяц в область столбцов. Получим следующие данные
Прекрасно! Теперь мы уже видим, сколько часов отработал каждый человек каждый месяц на всех проектах. Осталось сделать разбивку по проектам. Колонку проекты можно теперь поместить либо в столбцы (под месяц), либо в область рядов (под работника), либо в фильтры - тогда одновременно будет отображаться только 1 или несколько месяцев в сумме. Давайте сделаем все возможные варианты и посмотрим как это будет выглядеть.
Вариант 1 - помещаем проекты в колонки.
Разбивка по проектам будет идти прямо под каждым месяцем. Ровно как в модели справа
Вариант 2 - помещаем проекты в ряды.
В целом уже выглядит более наглядно.
Вариант 3 - помещаем проекты в фильтры.
В этом случае у нас будет возможность выбора 1 или нескольких проектов и сумма или значение по ним будет отображаться в одной таблице.
Какой способ отображения выбрать - зависит только от ваших нужд и потребностей. По итогу вам решать, как именно вы хотите сравнивать данные и какой способ отображение подходит для этого лучше всего.
Еще важно выделить 2 важных момента при работе со сводными таблицами
1. убрать из модели сводной таблицы какое-либо поле очень легко - достаточно перетянуть это поле влево за пределы модели с зажатой левой клавишей мыши. При этом на курсоре появится знак крестика. После этого левую кнопку мыши можно отжать. Либо - второй вариант - убрать зеленую галочку в модели сверху. Что для вас удобнее - выбирайте сами.
2. Имейте в виду, если вы меняете данные в исходной таблице в рабочей области, то итоговую таблицу обязательно нужно обновлять! Для этого зайдите в закладку Анализ итоговой таблицы - Analýza kontingenční tabulky - выберите поле Актуализовать (aktualizovat) - и выберите Актуализировать (только эту таблицу) или Актуализировать всё (все таблицы на листе). Либо можно воспользоваться сочетанием клавиш alt+F5.
3. Если вы меняете область данных, например добавляете еще одного человека с его часами к существующей таблице в рабочей области, то для того, чтобы он появился в текущей сводной таблице, необходимо изменить область данных - в этой же вкладке Анализ итоговых таблиц - рядом с полем Актуализировать нажимаем на поле Изменить источник данных (změnit zdroj dat) -
После этого выделяете полностью всю новую таблицу еще раз и нажимаете ОК.
После этого новые данные появятся в вашей сводной таблице. Актуализировать тогда уже не нужно.
На основе сводной таблицы можно построить сводный график - для этого делаем активной итоговую таблицу (курсор должен быть внутри итоговой таблицы) - идём на вкладку Анализ итоговых таблиц - Сводный график (kontingenční graf) - далее выбираем тип графика, который хотим отобразить, например, Столбцовый (Sloupcový) -
После подтверждения нам отобразится график с наглядным видом часов по проектам за каждого работника и их динамика по месяцам.
Прямо здесь же в графике мы можем делать фильтры данных по месяцам, работникам и так далее.
Подготовка итоговых данных. Область печати - масштабирование. Печать в пдф
После того, как мы подготовили данные, нам нужно их как-то из экселя достать и положить в красивом виде на стол начальнику, ну или себе в папку.После того, как данные готовы, графики построены, мы можем сохранить необходимую область нашего листа. Для этого для начала необходимо выделить ту область, которую хотим сохранить или распечатать -
После этого переходим в Отображение страницы (rozložení stránky) и выбираем там Область печати (oblast tisku) - Установить область печати (nastavit oblast tisku)
Если уберем выделение, то заметим, что вокруг него появилась небольшая рамка - это та область, которая отобразится в печати. Если наша страница больше подходит для горизонтального вида, то здесь же перед установкой области печати можем в меню Ориентация (orientace) задать горизонтальное отображение (na šířku)
Далее нажимаем сочетание ctrl + P или выбираем Файл (Soubor) - Печать (Tisk)
После этого нам появится диалоговое окно печати, где мы можем выбирать различные настройки отображения и печати.
Самыми полезными из них являются выбор масштаба - měřítko. Например, мы можем сделать отображение чуть больше, потому что у нас слишком много свободных белых областей вокруг графика и данных. Там, где пока установлено Без масштаба (bez měřítka) - выберем Собственный масштаб (vlastní měřítko) и далее установим значение, например на 120%.
После этого нажимаем кнопку Печать (tisk) и наслаждаемся результатом!
Транспонирование данных, специальные вставки
Иногда необходимо скопировать ячейку или ячейки и вставить ее определенным образом - например только значения или только форматы. Но бывают случаи, когда нам необходимо "перевернуть" таблицу. То есть строки стали столбцами, а столбцы - строками. Например, у нас есть следующая таблица с количеством часов, отработанных по проекту для каждого сотрудника:
Но эта таблица не очень удобная, потому что слишком широкая, и теоретически может продолжаться вширь и дальше. Поэтому делать анализ в дальнейшем может быть проблематично. Гораздо удобнее будет данные "перевернуть" так, чтобы даты шли сверху вниз. Как это сделать? Для этого мы воспользуемся таким инструментом, как транспонирование таблицы. Для начала выделим всю таблицу вместе с шапкой и скопируем её (ctrl + C). Далее помещаем курсор в свободное место под таблицей (должно быть свободно большое количество строк) нажимаем правой клавишей - специальная вставка (vložit jinak) - и отметить галочку Транспонировать (transponovat)
После этого нам вставится "перевёрнутая" таблица -
Теперь неплохо было бы сделать для этих данных график. Такие данные, отражающие изменения некоторой величины во времени, называются временными рядами. Для них могут подходить либо непрерывный либо точечный , либо сложенный. Давайте построим каждый.
Для того, чтобы вставить график, необходимо выделить всю таблицу и выбрать Вставка (vložení) - Рекомендуемые графики (doporučené grafy). После этого необходимо выбрать подходящий тип графика, или сразу выбрать тип графика в меню графиков справа от кнопки Рекомендуемые графики.
Выбираем Линейный график (Spojnicový) -
Далее график можем форматировать или настраивать различные опции, менять цвета, размеры шрифтов, положение легенд и так далее. Можем делать дополнительную вертикальную ось для отдельных значений, можем менять шаг для каждой из осей.
Можем также и удалять или добавлять данные на сам график. Допустим, на графике не хотим иметь Карла. Кликаем на линию с его данными на графике и удаляем через кнопку Delete.
Для того, чтобы добавить Карла обратно, давайте на графике в свободной области нажмем правой клавишей и выберем Выбрать данные (vybrat data) -
Далее появится диалоговое окно, где сверху у нас есть диапазон. Можем либо заново выделить весь диапазон, то есть всю начальную таблицу, либо добавить ряд через кнопку Добавить ряд (Přidat). В этом случае нужно будет выделить только тот ряд (включая заголовок), который был добавлен (столбик с Карлом).
Дополнительно нужно задать значения ряда для горизонтальной оси (popisky osy), то есть даты через кнопку upravit в правой части диалогового окна
После этого нам добавится новый, или в нашем случае старый новый ряд данных.
Чтобы добавить новый график, можно сделать опять все то, же самое, но есть 1 лайфах - копируете старый график полностью весь и вставляете копию.
После этого в свободной области графика нажимаете правой клавишей и меняете тип графика - это избавит вас от необходимости опять подстраивать шрифты, оси и т.д.
Другой формой отображения динамики во времени является групповая столбцовая диаграмма (skupinový sloupcový diagram) -
Еще одним типом графиков, который хорошо описывает динамику суммы отработанных часов за каждый день, но при этом и отображает примерную долю каждого работника является сложенный столбиковый (skladaný sloupcový)
Если нам нужно отслеживать именно динамику точных долей вклада каждого работника в проект - то для этих целей есть график 100% сложенный столбиковый (100% skladaný sloupcový)
Выбираете необходимый график, подстраиваете его вид и объем данных под ваши нужды - и готово!
Линия тренда
Представим, что у нас есть динамика изменения некоторой величины. С помощью экселя мы можем спрогнозировать, как данная динамика будет развиваться в будущем на некоторое количество периодов с помощью стандартных математических инструментов прогнозирования.Представим, что у нас есть данные по выручке фирмы за 10 лет. Для наглядности, сразу построим график. Прямо на график мы можем добавить линию тренда, который не покажет вам точного значения будущих периодов, но покажет общую тенденцию (тренд), которая скорее всего ожидается в динамике данной величины.
Для того, чтобы добавить линию тренда, выделим значения на графике нажатием на любую точку одним нажатием левой клавиши. Далее вызываем контекстное меню правой клавишей мыши - и выбираем Добавить линию тренда
После этого вам отобразится меню построения тренда.
В этом меню с правой стороны экрана можно выбрать математическую модель, по которой будет строится тренд. Лучше всего при выборе модели ориентироваться на показатель R^2 / Р-квадрат (r na druhou), который отображает разброс значений вокруг линии тренда. Он измеряется от 0 до 1. Чем ближе он к 1, тем лучше тренд описывает тенденцию. Поэтому обязательно отобразите на графике R^2, нажав на соответствующую галочку. Перебирая различные модели, вы найдете ту, где R^2 будет максимальным, при этом график будет разумнее всего "ложиться" на точки и на прогноз. Например, для нашей выручки хорошо подходит полиномиальная функция 3го порядка с показателем Р-квадрат равным 0,8565. Это не идеальный тренд, но лучший из того, что может сделать математика.
Если же вы хотите более конкретный прогноз с цифрами, то для этих целей в экселе есть инструмент Лист прогноза (List prognózy). Давайте построим прогноз с его помощью.
Опять выделяем нашу табличку с данными.
Теперь выбираем вкладку Данные (data) - Лист прогноза (list prognózy).
Далее отобразится диалоговое окно, в котором появится график с прогнозом и параметрами прогнозирования. Жирная коричневая линия - наиболее вероятный прогноз. Тонкие коричневые линии снизу и сверху ограничивают ту область, в которую с 95% точностью попадут значения прогноза. Внизу находятся параметры - конец прогноза (konec prognózy), Интервал надёжности (тех самых 95%). Также там можете указать наличие сезонности в ваших данных (если она есть).
Нажав на кнопку Создать (vytvořit) вы поместите график и данные, на основе которых он строился на новый лист.
Помните, что прогноз не даёт вам 100% точности при предсказании динамики в будущем, а лишь позволяет разумно спрогнозировать наиболее вероятный разброс значений при сохранении прочих равных условий. От форс-мажора в этой жизни никто не застрахован!
Круговая диаграмма
Теперь еще важный тип графиков - это круговая диаграмма. Данный тип диаграмм отображает доли составных показателей в общем объёме (сумме).Допустим у нас есть данные по выручке для отдельных сотрудников за месяц.
Первое, что желательно сделать, чтобы в дальнейшем данные были более наглядными - отсортировать таблицу по убыванию числового показателя (выручки). Давайте это сделаем. Новый вид таблицы будет:
Далее выделяем нашу таблицу и выбираем Вложение - круговая диаграмма (výsečový diagram)
После этого у нас появится диаграмма следующего вида:
Диаграмма не совсем наглядная, желательно убрать легенду и поместить названия рядов (имена сотрудников) рядом с самой долей. При этом желательно отобразить их доли в процентах и в абсолютных величинах. Давайте это сделаем. Убираем название диаграмы (если надо) и легенду снизу.
Через правую клавишу мыши в свободной области диаграммы выбираем формат отображения данных:
и в меню справа ставим галочки на Название категории (název kategorie), Значение (hodnota), Процент (procento), Отобразить линии соединения (zobrazit vodicí čáry), для размещения описания внизу выбираем опцию Приспособить (přizpůsobit). Цвет шрифта на цветном фоне лучше выбрать белый, кроме того значения, которое не помещается прямо на диаграму, именно для этого значения выбираем цвет по умолчанию (черный). И вот что в итоге у нас получилось:
Далее можно сделать настройку фона, цветов толщину пробелов между секторами, можно диаграму даже сделать в виде 3D перспективы. Но это оставляем на ваше усмотрение и на вашу фантазию.
График - карта
Еще одной полезной функцией при отображении данных является возможность построения географической карты с цветовым обозначением стран в зависимости от какого-либо показателя. Например, у нас есть данные по стоимости проезда в метро по нескольким странам.
Выделяем табличку. Заходим на панель с графиками и ищем там глобус -
После этого вам на основе ваших данных, эксель будет искать страны из списка и окрашивать их в цвет, соответствующий положению в градиенте между минимальным и максимальным значением из таблицы. В настройках графика можно выбрать тип фона, границы между странами, цвет шкалы и тд.
В итоге мы получаем вот такую карту:
Поиск/замена значений в таблице
Допустим у нас есть следующая таблица с именами сотрудников, где имена могут повторяться.
Предположим, что у девушки Jana Nová поменялись фамилии на Nováková. Как же нам сделать такую замену быстро, чтобы не просматривать каждый ряд и каждую ячейку? Для этого в экселе есть функция Поиск а также Замена. Вызывается команда Поиск через сочетание клавиш Ctrl + F либо из рабочей панели во вкладке Домой - Найти и выбрать (najít a vybrat).
Для начала выделяем нашу таблицу и вызываем команду Поиск/замена (najít-nahradit). Далее пишем в поле Найти тот текст, который мы хотим найти и Заменить (nahradit) - чем именно хотим заменить
Далее можем искать (заменять) по 1 разу за нажатие, либо заменить всё сразу. Эксель нас оповестит, сколько замен было сделано:
ВНИМАНИЕ: если вы не выделите таблицу перед тем, как вызывать замену, то Эксель будет искать этот текст во всём листе, и производить замены во всём листе!
Далее имеем таблицу с новой фамилией коллеги:
Удаление дубликатов в таблице
Теперь представим, что у нас есть таблица, которая содержит следующие данные об имени и возрасте сотрудников:
Как можно заметить, некоторые сотрудники у нас повторяются. Что, если мы должны сконтролировать всю таблицу и удалить повторяющиеся записи, то есть строки, у которых имя и возраст одинаковые. Как это можно сделать? Воспользуемся функцией удаления дубликатов. Находится эта функция в закладке Данные - Устранить дубликаты (odstranit duplicity).
Выделяем нашу таблицу вместе с строкой заголовком и выбираем Устранить дубликаты:
Далее появляется диалог, по которому мы должны выбрать, что будем считать дубликатом. Нас интересуют оба поля - как имя так и возраст. Поэтому оставляем галочки напротив названий этих колонок. Далее нажимаем ОК и Эксель нам выдаст количество записей-дубликатов, которые он удалил (3шт.).
Заметьте, что Эксель нам оставил Tereza Černá, хотя это имя у нас дублировалось. Но, поскольку возраст у обоих записей не совпадал, то программа не посчитала это дубликатом.
Количество критериев, может быть 1 или больше, в зависимости от наших данных.
Группировка строк (столбцов). Итоговая таблица
Теперь представим, что мы получили некоторые данные по количеству отработанных часов за каждый месяц людьм на проекте в таком виде:
Месяцев или людей могло бы быть и больше, и становится не очень удобно ориентироваться или искать некоторые данные по каждому отдельному месяцу.
В этом случае не интересующие нас месяцы мы можем просто "свернуть" в одну подгруппу или, проще говоря, сгруппировать. Для этого в Экселе существует специальная функция - Группировка (seskupit), которая находится во вкладке Данные (Data).
Для начала выбираем те целые строки, которые мы хотим сгруппировать
Далее выбираем команду Группировать и получаем "свёрнутый вид" данного месяца. При этом слева в колонке адресов строк у нас появляется "плюсик", которым мы можем группировку сворачивать или разворачивать.
Если сгруппировать все остальные месяцы, то полностью "свёрнутая" таблица выглядит так:
Если развернуть таблицу, то она примет изначальный вид:
Точно так же мы можем проводить группировкуи и по столбцам. Принцип остается полностью тот же самый, с той лишь разницей, что выделять будем не строки, а столбцы.
Если нам необходимо убрать группировку в таблице, мы так же легко можем её убрать, выбрав команду Разгруппировать (oddělit), которая находится здесь же, рядом с группировкой. Выделив строки, которые мы хотим разгруппировать и выбрав данную команду, вы возвращаем таблицу в начальный, негруппированный вид.
Теперь допустим, что мы имем данные по отработанным часам в следующем виде:
Представим, что перед нами стоит задача подвести итог по отработанным часам за каждый месяц. Конечно же, мы можем воспользоваться Итоговой таблицей, но есть способ еще проще - создать Итог (souhrn), который находится рядом с кнопкой Разгруппировать (oddělit) -
Итак, выделяем нашу таблицу и выбираем Итог. Далее мы должны указать по изменению какого критерия мы будем заканчивать делать Итог. В нашем случае это месяц. И что именно мы хотим делать в Итоге - а именно суммировать поле Часы. Выбираем сумма (součet) и поле (hodiny)
После этого нажимаем ОК и получаем Итоговую таблицу.
Итоговая таблица выглядит следующим образом:
Работа с формулами для дат, времени и текста
Довольно часто в Экселе мы встречаемся не только с числовыми данными - валютами, процентами или просто абстрактными цифрами, но и с данными о дате или времени. Давайте посмотрим, какие формулы нам в этом могут помочь. Мы помним, что в начале курса мы разбирали способы отображения дат через формат даты в настройках ячеек. Напрмер ДД ММ ГГГГ и тд. Но что, если мы хотим, чтобы в ячейках содержалась актуальная дата, или только актуальный месяц, день недели и так далее? Давайте постепенно рассмотрим каждую из формул:
=NYNÍ () - =СЕЙЧАС(), отображает дату и время в полном формате. Не имеет аргументов
=DNES() - =СЕГОДНЯ(), отображает сегодняшнюю полную дату ДД-ММ-ГГГГ. Не имеет аргументов
=ROK() - =ГОД(), отображает текущий год ГГГГ. Как аргумент, может принимать дату СЕГОДНЯ()
=MĚSÍC() - =МЕСЯЦ(), отображает текущий месяц ММ. Как аргумент, может принимать дату СЕГОДНЯ()
=DEN() - =ДЕНЬ(), отображает текущий день ДД. Как аргумент, может принимать дату СЕГОДНЯ()
Далее из полученных года, месяца и дня мы можем составить дату через формулу DATUM, которая в качестве аргументов принимает именно "год", "месяц" и "день".
Таким же образом мы можем составить любую дату, указав номер месяца и года. Например, 31 декабря текущего года =DATUM(ROK();12;31)
=DAYS(konec, začátek) - =ДНЕЙ(конец, начало) - отображает количество дней между конечной датой и начальной датой. Например, с помощью нее мы можем посчитать количество дней до нового года, взяв в качестве отправной точки - сегодняшнюю дату,ну а дату нового года мы построили в предыдущей строке
Если нас интересует день недели, то мы можем использовать функцию =DENTÝDNE(DATUM();PARAMETR) - ДЕНЬНЕД(ДАТА();ПАРАМЕТР). Она принимает 2 аргумента - дату, для которой хотим узнать день недели, и доп параметр. 1- означает, что неделя начинается с воскресенья и заканчивается субботой (от 1 до 7), 2- начинается с понедельника и по воскресенье (от 1 до 7). И 3 - начинается с понедельника и по воскресенье (понедельник будет 0-й день, а воскресенье 6й). По умолчанию будем использовать 2ку. Если функция возвращает, например 1 - означает что указанная дата - понедельник и тд.
Теперь посмотрим на функции времени.
Самая простая возможность отобразить время - использовать ту же формулу =NYNÍ() и отобразить ее через формат времени. В этом случае время отобразится, например в следующем виде: 2:00 PM
Если же нам нужно отобразить часы, минуты или секунды отдельно, воспользуемся соответствующими формулами:
=HODINA(NYNÍ()) - ЧАС(СЕЙЧАС()) - отобразятся количество часов текущего времени в формате ЧЧ. В качестве аргумента принимает функцию СЕЙЧАС()
=MINUTA(NYNÍ()) - МИНУТА(СЕЙЧАС()) - отобразятся количество минут текущего времени в формате ММ. В качестве аргумента принимает функцию СЕЙЧАС()
=SEKUNDA(NYNÍ()) - СЕКУНДА(СЕЙЧАС()) - отобразятся количество секунд текущего времени в формате СС. В качестве аргумента принимает функцию СЕЙЧАС()
Теперь из полученных часов, минут и секунд мы можем сложить текущее время через формулу
=ČAS(HODINA();MINUTA();SEKUNDA()) - ВРЕМЯ(ЧАС();МИНУТА();СЕКУНДА()).
В итоге мы получим текущее время в следующем отображении - 2:00 odp. что означает 2 часа по полудни.
С текстом мы также можем выполнять некоторые операции, которые нам могут в некоторых случаях пригодится. Допустим, в ячейке Е314 у нас содержится некоторый текст (сине-красный). Рассмотрим некоторые базовые формулы на его примере:
=DÉLKA(TEXT) = ДЛИНА(ТЕКСТ) - показывает общую длину, то есть количество символов в рассматриваемом тексте
=NAJÍT(CO; KDE) =НАЙТИ(ЧТО; ГДЕ) - в рассматриваемом тексте будет искать заданный символ, либо сочетание символов и выдавать позицию, на которой данный символ (или сочетание символов) находится.
=ZLEVA(TEXT;ZNAKY) =СЛЕВА(ТЕКСТ;ЗНАКИ) - рассматриваемый текст сокращает слева до указанного количества знаков. В примере выше вычтен 1 знак из-за самого знака открывающей скобки. В данном месте ее убираем, дальше ее добавляем.
=ZPRAVA(TEXT;ZNAKY) =СПРАВА(ТЕКСТ;ЗНАКИ) - рассматриваемый текст обрезает справа до указанного количества знаков. В примере выше добавлен 1 знак из-за открывающей скобки
=CONCAT(TEXT1;TEXT2...) = СЦЕПИТЬ(ТЕКСТ1;ТЕКСТ2;...) - объединяет текстовые цепочки, которые принимает в качестве аргументов в одну ячейку.
Таким образом, в конце мы получили изначальный текстовый массив.
Дополнительные формулы - A(), NEBO(), MOD(), SUMMIF(), POČETIF(), RANDBETWEEN(), SVYHLEDAT()
Рассмотрим некоторые дополнительные функции, которые нам могут быть полезны при более детальном анализе данныхПеред нами таблица с отработанными часами сотрудника по проектам за каждый день:
Для начала нам необходимо выделить те дни, где сотрудник отработал 3 часа хотя бы на одном проекте. Если данное условие будет выполняться, должно отображаться ANO напротив соответствующего дня, иначе NE. Для этого добавим колонку "práce 3 hod a víc" в которой и будем выводить результат ANO/NE. Далее воспользуемся уже знакомой нам формулой =KDYŽ(), которая нам будет проверять условие. Постепенно нам нужно провеять каждый проект и если хотя бы в 1м или 2м или 3м проекте будет 3 часа, функция должна возвращать выполнение условия. Для проверки условия "или" в экселе используется функция =NEBO() - ИЛИ(), которая будет в качестве аргументов принимать одно или несколько условий и возвращать ИСТИНА, если хотя бы одно условие выполняется. В общем виде это должно работать так:
=KDYŽ(NEBO(PODMÍNKA1;PODMÍNKA2;PODMÍNKA3...);"ANO";"NE")
В качестве условия будем проверять больше или равно 3м из каждого проекта. Итак, проведём вышеописанные действия:
Следующая задача - нам нужно выделить дни, в которые сотрудник работал на проекте А меньше трех часов и одновременно на проекте B больше или равно двум часам. Опять добавим колонку A<3,B>2a víc. Будем действовать подобным образом, но поскольку у нас должно выполняться одновременно 2 условия, будем использовать уже вместо функции =NEBO()-ИЛИ(), функцию A()-И(), которая также может в качестве аргументов принимать 1 или несколько условий, которые должны одновременно выполняться, чтобы функция возвратила ИСТИНА. В общем виде это должно работать так:
=KDYŽ(A(PODMÍNKA1;PODMÍNKA2);"ANO";"NE")
В качестве условия будем проверять для проекта А больше или равно 3м из каждого проекта. Итак, проведём вышеописанные действия:
Решим еще одну задачу. Нам необходимо в нечётные дни добавить к часам по проекту B по 1 часу, а в чётные дни - убрать с проекта С по одному часу. Для начала, нам нужно "вытянуть" из даты порядковый номер дня. Это мы уже умеем сделать, используя формулу =DEN(). Поэтому добавим одну вспомогательную колонку Den. Далее добавим еще одну вспомогательную колонку Sudý den в котором ANO будет для чётных дней, и NE для нечётных. Как же узнать, чётный день или нет? Если при делении на 2 остаток 0 - значит день чётный, если остаток 1 - нечётный. Для того, чтобы узнать остаток от деления, в экселе используется формула =MOD(), которая принимает 2 аргумента - число, которое делим, и делитель, в нашем случае это двойка (2). Итак, давайте проведём это:
Всё, что осталось сделать, это проверить условие чётности и добавить к проекту B по 1 часу и убрать с проекта C уберём 1 час. Для этого добавим новые колонки для проектов B и C.
Ещё одна задача теперь - давайте посчитаем, сколько дней сотрудник не работал на проектах А, B, C. Здесь мы воспользуемся специальной формулой, которая будет подсчитывать значения только в том случае, если соблюдается определенное условие. В нашем случае это 0 отработанных часов. Формула эта называется =COUNTIF() - СЧЁТЕСЛИ(). В качестве аргументов указывается диапазон, который будем проверять - то есть все часы за все дни, и второй аргумент - условие, которое будем проверить (=0). Давайте подсчитаем
Следующую задачу, которую выполним на данном примере - посчитаем, сколько часов в сумме отработал сотрудник при условии, что он работал 3 часа на каждом проекте. Для этих целей воспользуемся второй важной формулой, которая будет в этот раз уже суммировать значения, подходящие под определенное условие. Условие - отработка трёх и более часов на проекте, диапазон - тот же (все часы за все дни). Формула эта называется =SUMMIF() - СУММЕСЛИ(). В качестве аргументов указывается диапазон, который будем проверять - то есть все часы за все дни, и второй аргумент - условие, которое будем проверить (>=3). Давайте подсчитаем.
Теперь представьте себя на месте преподавателя, и вам нужно сгенерировать случайное количество часов для 5 проектов за данный выбранный временной диапазон. Конечно же, можно это делать вручную, но это займет около 2х минут времени. Это же можно сделать прмерно за 15 секунд, воспользовавшись функцией генерации случайных чисел =RANDBETWEEN() =СЛУЧМЕЖДУ(). Данная формула принимает в качестве аргументов - нижнюю и верхнюю границу диапазона, в котором будут генерироваться целые числа. Нас интересует диапазон от 0 до 3х. Далее эту ячейку протянем вправо и вниз на все ячейки диапазона. Давайте это сделаем:
Теперь решим следующую задачу. Нам необходимо из даты ММ.ДД.ГГГГ преобразовать в дни недели - пн, вт, ср, чт... . Как это можно сделать? Мы помним, что у нас есть формула, которая выдает порядковый номер дня в неделе 1- для понедельника, 2- для вторника и тд. Давайте добавим дополнительную колонку den v týdnu
Теперь нам нужно создать отдельную небольшую табличку, где напротив каждого номера напишем название дня недели.
Теперь добавим в начальную табличку колонку Den - název. Теперь по сути нужна формула, которая возьмет номер дня из колонки Den v týdnu пойдет во вспомогательную таблицу, найдет там этот номер скопирует название соответствующего дня и вставит. Такая функция в экселе есть! Она называется =SVYHLEDAT() - ВПР().
=SVYHLEDAT(искомое значение; место для его поиска; номер столбца в диапазоне с возвращаемым значением; возврат приблизительного или точного совпадения — указывается как 1/PRAVDA или 0/NEPRAVDA).
искомое значение - это как раз вычисляемый номер дня в неделе (колонка den v týdnu)место для его поиска - здесь выделяем всю нашу вспомогательную таблицу
номер столбца в диапазоне с возвращаемым значением - второй столбец (именно там содержатся названия дней недели)
нам нужно точное совпадение 1=1 например, поэтом ставим NEPRAVDA(0).
Итак, сделаем все указанные шаги:
Желательно зафиксировать в формуле вспомогательную таблицу через знак доллара, чтобы при протягивании формул, нам не съезжала ссылка на вспомогательную таблицу вниз
В итоге мы из даты 01.01.2022 получили день недели в формате со значением "sobota".
Вставка специального символа. Вставка формул. Вставка объектов
Допустим, нам нужно вставить какой-нибудь символ, который нельзя найти на клавиатуре. Для этого есть специальная таблица, в которой этот символ можно попробовать поискать:Находится эта таблица во вкладке Вставка (vložení) - Символ (Symbol)
Посмотрим какие символы здесь можно найти:
например, символы греческого алфавита, валют, математические знаки и символы:
буквы и символы с диакритикой и так далее:
Теперь, допустим, нам нужно вставить математическую формулу в правильном оформлении (например корень квадратный, степень и так далее).
Делается это через опцию Формула (rovnice) рядом с символом:
Здесь же сразу нажав на стрелочку внизу кнопки можно выбрать преднаставленный образец, если он вам подходит под оформление:
Выбрав подходящую формулу, вы можете ее сразу же редактировать в появившемся прозрачном прямоугольнике (следите, где находится курсор - в степени, в числителе и тд. именно там и будет происходить изменение значения. Дополнительные операции редактирования формул можно проводить в меню Rovnice, которое появляется при выборе формулы в рабочей области.
В итоге получаем ту формулу, которая нам необходима
Далее, допустим, мы хотим прямо в лист Экселя вставить файл, например в формате .pdf или .docx
Сделать это можно через вставку Объект (Objekt)
Далее выбираем тип объекта, который мы хотим вставить в наш лист - например документ .pdf.
Обязательно поставьте галочку напротив опции Изобразить как иконку (zobrazit jako ikonu) иначе вам вставится сразу открытый документ в экселе.
После этого выберите нахождение самого документа и подтвердите ОК.
Документ вам вставится в виде иконки - при двойном клике по иконке вам откроется целый документ.
Установка пароля на открытие листа
Допустим, что вы создали свой файл, но хотите его защитить от несанкционированного доступа. На файл можно установить пароль для открытия.Делается это следующим образом:
Нажимаете Файл (Soubor) - Сохранить как (uložit jako) - Просмотреть (procházet). В появившемся окошке выбирает место, куда хотите сохранить ваш файл. После этого нажимаете на опцию Возможности (možnosti) - выбираете Общие возможности (Obecné možnosti). После этого вы можете задать пароль на открытие или на редактирование.
Если вы зададите пароль на открытие (heslo pro otevření), то без него, вы не сможете открыть файл, пароль на редактирование (heslo pro úpravy) открывает возможность редактирования файла, но открыть вы его сможете и без такого пароля.
Внимание! Особенного способа разблокировать файл без знания пароля не существует, поэтому пароль лучше куда-то для надёжности запишите!