Тема 1 / Excel_работа_1-2

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

2. умение проводить визуальный анализ данных в Excel;

3. построение отчетов при работе с большими объемами данных.

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

Обратите внимание на значение полей «Discount» и «Subtotal». В одном поле для разделения целой и дробной части используется точка, в другом — запятая. При этом в поле «Data» для отображения формата «data» также используется точка.

В одной книге Excel можно настроить только один вид системных разделителей – точка или запятая.

В настоящее время 80% отчетности компаний выполняется в Excel на основе оперативных данных, хранящихся в корпоративной информационной

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

1. На вкладке Файл выберите Параметры Excel / Дополнительно, найдите пункт, отвечающий за установку разделителей между целой и дробной частями, и выберите «,».

2. Выделите поле «Discount» (щелкните мышью на столбце C).

3. На вкладке Главная / группа Редактирование / Найти и выделить/

Убедитесь, что в числовых полях появился знак «,», при этом «.» сохранилась в первом поле для представления даты продажи.

Один из отчетов, который нужно получить, должен отражать суммарный объем продаж по годам и месяцам. Для этого необходимо создать новые поля «Год» и «Месяц», которые будут заполнены данными при помощи функций «ГОД» и «МЕСЯЦ».

1. Добавьте после поля с датами два новых поля «Год» и «Месяц».

2. Для первой ячейки данных каждого поля поочередно выберите в окне Мастера функций в категории «Дата и время» функцию ГОД (МЕСЯЦ). В качестве параметра укажите ячейку с датой в текущей строке.

3. Измените формат ячейки на числовой с 0 знаков после запятой.

4. Скопируйте содержимое в оставшиеся ячейки соответствующего поля

(например, скопируйте содержимое ячейки В2, выделите ячейку В2156 при нажатой клавише SHIFT и используйте вставку в выделенный диапазон ).

Экономисты практически постоянно сталкиваются с многомерными данными. В нашем примере данными плана продаж являются:

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

Задание 3. Сводная таблица: выборка уникальных значений

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

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

Укажите диапазон исходной таблицы. Обратите внимание, что выделить все таблицу в 2000 строк сложно, поэтому используйте прием:

Выделите начальные 10-20 строк и далее вручную измените диапазон на нужно количество строк: например, диапазон $A$1:$H$ 20 легко заменить на $A$1:$H$ 2160

Расположите данные на новом листе Шаг 3. Параметры сводной таблице

Т.к. нас интересуют компании, то в качестве названия строк укажите CompanyName

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

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

Задание 4. Сводная таблица: получение количественных характеристик уникальных полей

Усложним задачу 3 и определим, сколько раз каждая фирма

Шаг 1. Активизируйте сводную таблицу задания 4, щелкнув мышью внутри таблицы.

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

ЗНАЧЕНИЙ и укажите, что Вас интересует подсчет количества.

Согласно нашему примеру известна принадлежность каждой фирмы стране (поле Country).

В макете сводной таблицы задания 4 в область фильтра отчёта поместите поле Страна . В результате в сводной таблице вверху появится поле Страна .

Задание 6. Самостоятельные задания посводным таблицам.

Обратите внимание, что на данном этапе в книге Excel должно быть два листа: Данные и Сводная таблица

На листе 3 (назовите его 6-А) получите отчет : Распределение прибыли по месяцам , в котором необходимо предусмотреть фильтрацию по стране и фирме. Используя фильтры, запишите годовые цифры продаж для одной страны и одной фирмы (на Ваш выбор).

На листе 6-Б получить максимальные скидки по месяцам каждого года . В качестве фильтра предусмотреть страну.

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

Создайте новый лист с именем ИТОГИ.

Скопируйте данные с листа Данные

(Data) на лист Итоги. Для быстрого выделения всех ячеек листа Данные используйте верхний левый угол.

Данные отсортируйте по трем полям:

Перейдите на вкладку

ДАННЫЕ/ группа СТРУКТУРА/ ПРОМЕЖУТОЧНЫЙ ИТОГ. Укажите, что при каждом изменение в поле COUNTRY рассчитать сумму по полям

Раскройте список операций продаж с компаниями, находящимися в Канаде. Для этого смените знак «+» на «-» в структуре уровня №2, напротив строки с итоговыми данными по Канаде.

Задание 8. Промежуточные итоги многоуровневой структуры

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

требуется дополнительно осуществить группировку уровней 4 и 5 по полям «CompanyName» и «Год» .

Дробные числа хранятся в переменных типа double (от англ. double — двойной). Данный тип так называется, потому что для хранения дробного числа необходимо использовать две ячейки в памяти: для целой и для дробной части.

Формула в экселе разделить

Для связки простых условий можно использовать связки «И» или «ИЛИ». Связка «И» обозначается двумя символами амперсанта && , а связка «ИЛИ» — двумя вертикальными палочками || . В примере выше использована связка «И».

Выполнение деления

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

Способ 1: деление числа на число

Лист Эксель можно использовать как своеобразный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (обратная черта) – «/».

1. Становимся в любую свободную ячейку листа или в строку формул. Ставим знак «равно»(=). Набираем с клавиатуры делимое число. Ставим знак деления (/). Набираем с клавиатуры делитель. В некоторых случаях делителей бывает больше одного. Тогда, перед каждым делителем ставим слеш (/).

После этого Эксель рассчитает формулу и в указанную ячейку выведет результат вычислений.

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

Как известно, деление на 0 является некорректным действием. Поэтому при такой попытке совершить подобный расчет в Экселе в ячейке появится результат «#ДЕЛ/0!».

Способ 2: деление содержимого ячеек

Также в Excel можно делить данные, находящиеся в ячейках.

Выделяем в ячейку, в которую будет выводиться результат вычисления. Ставим в ней знак «=». Далее кликаем по месту, в котором расположено делимое. За этим её адрес появляется в строке формул после знака «равно». Далее с клавиатуры устанавливаем знак «/». Кликаем по ячейке, в которой размещен делитель. Если делителей несколько, так же как и в предыдущем способе, указываем их все, а перед их адресами ставим знак деления.

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

Способ 3: деление столбца на столбец

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

Способ 4: деление столбца на константу

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

Кликаем по кнопке Enter. Результат расчета для первой строки выводится на монитор.

Способ 5: деление столбца на ячейку

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

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

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

Жмем на кнопку Enter, чтобы вывести результаты расчета по первой строке на экран.

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

Способ 6: функция ЧАСТНОЕ

Открывается Мастер функций. В перечне функций, которые он нам предоставляет, ищем элемент «ЧАСТНОЕ». Выделяем его и жмем на кнопку «OK».

После этих действий функция ЧАСТНОЕ производит обработку данных и выдает ответ в ячейку, которая была указана в первом шаге данного способа деления.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Раннее мы рассматривали возможность разделить текст по столбцам на примере деления ФИО на составные части.

Для этого мы использовали инструмент в Excel «Текст по столбцам».

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

Целая, дробная части действительного числа и их свойства

• 1 Пример 1. Делим текст с ФИО по столбцам с помощью формул
• 1.1 Приступаем к делению первой части текста — Фамилии
• 1.2 Приступаем к делению второй части текста — Имя
• 1.3 Приступаем к делению третьей части текста — Отчество
• 2 Пример 2. Как разделить текст по столбцам в Excel с помощью формулы

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

Пользовательский формат числа в Excel

Скачать заметку в формате Word или pdf, скачать примеры в формате Excel

Рис. 2. Вызов окна Формат ячеек с помощью ленты

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

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

1. Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек.
2. Щелкните на вкладке Число и выберите на ней категорию (все форматы).
3. Введите ваш собственный формат в поле Тип.

Тем не менее, не все стандартные форматы имеют один раздел. Например, выбрав формат числовой и четвертый тип (нижний), вы «покрасите» отрицательные числа в красный цвет (рис. 4).

Рис. 4. Формат Числовой, тип четвертый: отрицательные числа – красным цветом

Первый – для положительных чисел, второй – для отрицательных. Символом ˽ я обозначаю пробел.

Рис. 7. Особенности использования текстового раздела формата

Последний пример может вызвать затруднение, но, обратите внимание, что в коде формата (ячейка В4) отсутствует символ @, который определяет место, где должен отразиться введенный в ячейку текст. Т.е. содержимое ячейки С4 будет оставаться постоянным, какой бы текст не был введен в ячейку А4.

Примечание. Символ # является джокерным для числа, т.е., говорит о месте отображения чисел, введенных в ячейку. Аналогично символ @ является джокерным для текста, т.е., говорит о месте отображения текста, введенного в ячейку.

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

Рис. 8. Использование основных кодов отображения числа

Рис. 9. Знаки, которые можно вводить в код формата без кавычек

Использование знаков из таблицы 9 в коде формата не следует путать с их использованием в ячейке в качестве элемента формулы. В качестве части кода формата эти знаки просто отображаются. На рис. 10 видно, что в ячейку введено значение 25. Всё остальное – это лишь формат отображения.

Рис. 10. Пример знаков кода формата, отображаемых без кавычек

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

Чтобы ввести в код формата один символ, используйте перед ним знак обратной косой черты \. Более того, поскольку кавычки » говорят Excel’ю о начале ввода текста, чтобы отразить в коде формата символ кавычек, предварите их знаком косой черты \ » .

Задать цвет для раздела формата можно одним из двух способов:

• введите название одного из указанных ниже восьми цветов в квадратных скобках: [Черный], [Белый], [Красный], [Зеленый], [Голубой], [Желтый], [Фиолетовый], [Синий];
• введите [ЦВЕТn], где n – число от 1 до 56 и соответствует одному из цветов в палитре; первые восемь цветов соответствуют перечню из предыдущего пункта (в указанном порядке); с остальными цветами палитры можно познакомиться, например, здесь.

Код цвета должен быть первым элементом в разделе. Пример использования цвета будет приведен ниже.

Рис. 12. Использование проверочных условий в формате

Некоторые дополнительные возможности форматирования

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

Рис. 13. Выравнивание положительных и отрицательных чисел, если последние отображаются в скобках

Для заполнения всей ширины ячейки определенным знаком добавьте перед ним звездочку * в числовом формате. Например, для заполнения стрелками пустой области ячейки справа от числа следует указать #*→, для добавления перед числом знаков подчеркивания *­_# (рис. 14).

Рис. 14. Использование заполнителей в числовом разделе формата

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

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

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

Рис. 18. Коды, применяемые при создании пользовательских форматов даты

Рис. 19. Коды, применяемые при создании пользовательских форматов времени

Рис. 20. Комментарии к использованию кодов для форматирования времени (обратите внимание, что первая строка таблицы не является заголовком; это просто неточность Microsoft при оформлении таблицы :))

Завершаем обзор примерами использования пользовательских форматов даты и времени (рис. 21).

Рис. 21. Примеры пользовательских форматов даты и времени

Любопытные примеры пользовательских форматов можно найти также в Инете, например, здесь или здесь.

На листе 3 (назовите его 6-А) получите отчет : Распределение прибыли по месяцам , в котором необходимо предусмотреть фильтрацию по стране и фирме. Используя фильтры, запишите годовые цифры продаж для одной страны и одной фирмы (на Ваш выбор).

Разделитель целой и дробной части

Однако можно поступить проще. Так как ясно, что в языке программирования С/С++ при делении с остатком отрицательных чисел получается остаток, равный разности принятого у математиков остатка и делителя.

Пример №102.

Решить уравнение 2х> = x.

2-й способ (графический). Построим в одной системе координат графики функций , стоящих в левой и правой частях уравнения. Количество решений уравнения при этом равно количеству точек пересечения этих

графиков, а сами решения являются абсциссами точек пересечения графиков. Очевидно, что графики пересекаются в единственной точке — начале координат. Проверкой убеждаемся, что число x = 0 действительно является решением данного уравнения (проверку сделать необходимо, поскольку графический способ решения, вообще говоря, неточный).

Системным Разделителем Дробной и Целой Части Числа в Excel Является Варианты Ответа

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

Мнение эксперта

Витальева Анжела, консультант по работе с офисными программами

Со всеми вопросами обращайтесь ко мне!

Задать вопрос эксперту

Смотрим первую формулу выделения Фамилии и смотрим где здесь встречается H2 или I2 и меняем их на формулы в этих ячейках, аналогично с Именем и Фамилией. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!

Обратите внимание, что знак «равно» в условии цикла if состоит из двух символов равно == . А знак «не равно» состоит из восклицательного знака ! , который в языке С/С++ обозначает отрицание и символа равно: != .

Функция для определения разделителя целой и дробной части

Для того, чтобы вычислить целую часть положительного числа в языке программирования С/С++, необходимо применить конвертацию переменной из типа double в тип int . Чтобы сконвертировать переменную типа double в тип int , необходимо просто поставить перед ней int и взять её в круглые скобки:

Полезные сведения → Как объединить ячейки → Как вставить значения → Аргументы функции → Работа с форматами → Функция ЕСЛИ → Как удалить пробелы → Функция впр vlookup→ Работа с таблицами