Как растянуть даты в экселе в гугл таблице
Данные в Google Таблицах можно форматировать разными способами.
Форматирование ячеек: числа, даты, валюта
Чтобы отформатировать ячейку как число, дату или сумму в определенной валюте, выполните следующие действия:
- Откройте файл в Google Таблицах на компьютере.
- Выберите ячейку или диапазон данных.
- Нажмите ФорматЧисла.
- Выберите нужный формат.
Специальное форматирование чисел, дат и валют
Вы можете создавать собственные форматы чисел, дат и валюты. Это можно сделать прямо в меню "Числа", если вам не подошел ни один из предложенных в нем вариантов. Ниже вы найдете подробную инструкцию.
Чтобы изменить формат даты и времени, выполните следующие действия:
- Откройте файл в Google Таблицах на компьютере.
- Выберите ячейку или диапазон данных.
- Нажмите ФорматЧисла.
- Выберите Пользовательские дата и время.
- В открывшемся меню выберите нужный формат или добавьте свой, используя текстовое поле.
- Нажмите Применить.
По умолчанию формат даты и времени задается на основе региональных настроек таблицы.
Хотите изменить параметры форматирования (например, указать часы или минуты)? Нажмите на стрелку вниз в углу текстового поля и выберите дополнительное значение. Для более точной настройки воспользуйтесь стрелками. Чтобы удалить значение, нажмите на него и выберите Удалить.
Чтобы изменить формат валюты, выполните следующие действия:
- Откройте файл в Google Таблицах на компьютере.
- Выберите ячейку или диапазон данных.
- Нажмите ФорматЧисла.
- Нажмите Валюта.
- В открывшемся меню выберите нужный формат или добавьте свой, используя текстовое поле.
- Нажмите Применить.
Чтобы изменить параметры форматирования (например, добавить больше знаков после запятой), нажмите на раскрывающееся меню в углу поля и выберите нужный вариант.
Чтобы изменить формат числа, выполните следующие действия:
- Откройте файл в Google Таблицах на компьютере.
- Выберите ячейку или диапазон данных.
- Нажмите ФорматЧисла.
- Выберите Другие форматы чисел.
- В открывшемся меню выберите нужный формат или добавьте свой, используя текстовое поле.
- Нажмите Применить.
При создании собственного формата помните, что он может содержать максимум четыре части, разделенные точками с запятой: положительная;отрицательная;ноль;нечисловая. Также поддерживается финансовый формат.
Кроме того, вы можете использовать цвета (например, чтобы различать положительные и отрицательные числа). Для этого нужно указать цвет на английском языке в квадратных скобках в любой части формата (например, [Red] для красного). Поддерживаются следующие цвета:
Ниже вы найдете список наиболее распространенных символов, которые можно использовать при создании форматов чисел.
Показывает текст, содержащийся в ячейке.
Повторяет следующий символ, чтобы заполнить свободное пространство ячейки.
В этой заметке описаны основные текстовые функции Google Таблиц. На скриншоте — четыре столбца:
- Название функции;
- Исходный текст;
- Текст после применения функции (ячейка с формулой);
- Текст самой формулы.
Ниже — описание этих функций.
В скобках указаны названия функций-аналогов из Excel.
TRIM (СЖПРОБЕЛЫ) удаляет лишние пробелы между словами, а также до первого и после последнего слова в тексте. Остается лишь по одному пробелу между словами. Единственный аргумент — ячейка с текстом (теоретически можно и текст в кавычках указать прямо в самой формуле, но на практике вряд ли такое понадобится)
UPPER (ПРОПИСН) и LOWER (СТРОЧН) меняют регистр текста на верхний и нижний соответственно. У них тоже один аргумент.
Функция LEN (ДЛСТР) определяет длину текста. Учитываются все символы, включая пробелы. Ее можно использовать как формулу массива — см. строку 7 в примере. В таком случае удастся посчитать сумму длин текстов из целого диапазона.
Функция FIND (в Excel 2 аналога — НАЙТИ учитывает регистр, ПОИСК — нет) определяет позицию вхождения слова или символа в текст.
Первый аргумент — текст, который мы ищем, второй — ячейка с текстом, в котором будем искать. Есть третий — необязательный — аргумент: позиция начала поиска. Искать можно не с начала. В примере слово кефир в исходном тексте стоит на 26 позиции.
Функция SUBSTITUTE (ЗАМЕНИТЬ) меняет в тексте одно слово (символ, текст) на другое.
Например, функция ниже:
=SUBSTITUTE("пробежал я давеча марафон за 3:15";"3:15";"2:55″)
выдаст такой приятный результат:
пробежал я давеча марафон за 2:55
Функция TEXT (ТЕКСТ) нужна в тех случаях, когда вы с помощью формул составляете текстовую фразу, в которой используются числовые значения или даты из ячеек вашей таблицы.
В примере:
="сегодня "&B13
выдает дату в неотформатированном виде, т.е. как число:
А функция TEXT позволяет получить красивый результат:
="сегодня "&TEXT(B14;"DD/MM/YYYY")
Функции LEFT (ЛЕВСИМВ) И RIGHT (ПРАВСИМВ) вырезают из текста (первый аргумент) определенное (второй аргумент) количество знаков.
=ЛЕВСИМВ("Слово";3) = "Сло"
Функция MID (ПСТР) вырезает из текста символы, начиная с определенной позиции (второй аргумент):
=MID("Машина";3;2) = "ши"
Функция SPLIT (Аналог в Excel — инструмент "Текст по столбцам", вкладка "Данные" на ленте)
разделяет текст (первый аргумент) по определенному разделителю (второй аргумент).
Разделитель — ибо это текст — нужно указать в кавычках (в примере это пробел):
=SPLIT(B21;" ")
Вы можете добавлять данные в ячейки таблицы, а также изменять и форматировать их.
Как изменять данные в ячейках
- Откройте файл в Google Таблицах.
- Нажмите на пустую ячейку. Если в ячейке содержатся данные, нажмите на нее два раза.
- Введите данные.
- Если нужно перенести текст в ячейке на новую строку, нажмите ⌘ + Ввод на Mac или Ctrl + Ввод на Windows.
- Когда закончите, нажмите Ввод.
Как форматировать данные в одной или нескольких ячейках
- Откройте файл в Google Таблицах.
- Нажмите на ячейку, а затем, не отпуская кнопку мыши, проведите курсором по ячейкам, которые нужно выбрать. Либо выбирайте ячейки, удерживая кнопку ⌘ на Mac или Ctrl на Windows.
- Чтобы отформатировать данные в ячейке, используйте кнопки на панели инструментов вверху экрана.
Как форматировать данные
Ниже перечислены некоторые настройки форматирования ячеек или данных в них. Все они находятся над рабочей областью документа.
- Отменить
- Повторить
- Полужирный
- Курсив
- Зачеркнутый
- Начертание и размер шрифта
- Цвет текста
- Цвет заливки ячейки
- Один цвет
- Чередование цветов
- Цвет границ
- Стиль границ
Чтобы изменить форматирование части содержимого в ячейке, нажмите на нее дважды, выделите фрагмент и задайте нужные параметры.
Как выравнивать объекты и менять их размер
Переместите объект в нужную область или измените его размер. С помощью линий на экране вы можете контролировать положение объекта относительно других, расстояние между объектами, а также создавать объекты одинакового размера.- Выберите ячейку.
- В верхней части экрана нажмите ФорматЧисла.
- В открывшемся меню выберите формат.
- Если нужного формата нет в списке, в нижней части открывшегося меню нажмите "Другие форматы" и выберите подходящий вариант, например "Другие форматы даты и времени" или "Другие форматы чисел".
Сведения о форматировании чисел в таблицах доступны в Справочном центре.
Как добавить тему
С помощью тем можно менять форматирование сразу во всей таблице.
- Откройте файл в Google Таблицах.
- В верхней части страницы нажмите ФорматВыбрать тему.
- Выберите доступную тему или нажмите Настройка, чтобы создать свою.
Примечания
- При создании пользовательской темы будет сохранена самая последняя версия документа.
- Цвета из текущей темы будут доступны в тексте и палитрах цвета заливки.
Что тема меняет в таблице
- Шрифт и цвет текста в ячейках, диаграммах и сводных таблицах.
- Цвет текста гиперссылок.
- Цвет фона диаграмм.
- Цвет серий в диаграммах.
- Фон сводных таблиц.
Примечание. Если вы измените форматирование объекта, оно переопределит настроенное в теме.
Обычно мы пишем про хостинги, в частности про зарубежный shared хостинг в США. Но чтобы писать, нужно иметь аналитические данные под рукой. Вот как раз тут требуется помощь Google Docs, если файл получится предположительно меньше 400 000 строк.
За несколько месяцев работы с таблицами Google пришлось много раз анализировать посредством формул разного рода данные. Как и ожидалось — то, что можно было решить в MS Excel, можно реализовать и в Google таблицах. Но многочисленные попытки решить проблемы с помощью любимого поисковика приводили только к новым вопросам и почти к нулевым ответам.
Посему, было решено облегчить жизни другим и прославить себя.Кратко о главном
- буквенно — цифровое (БУКВА = СТОЛБЕЦ; ЦИФРА = СТРОКА) например «А1».
- стилем R1C1, в системе R1C1 и строки и столбцы обозначаются цифрами.
Рисунок 2
Как видно из Рисунка 3, значения ячеек идут относительно той ячейки, в которой будет написана формула со знаком равно. Для сохранения эстетичного вида формул, в них прописаны символы [0], которые можно и не писать: R[0]C[1] = RC[1].
Рисунок 3
Отличие Рисунка 2 от Рисунка 3 в том, что Рисунок 3 — это универсальная формулировка, не привязанная к строкам и столбцам (смотрите на значения строк и столбцов), чего не скажешь о рисунке 2. Но стиль RC в spreadsheet, в основном, используется для написания скриптов javascript.Типы ссылок (типы адресации)
- Относительные ссылки (пример, A1);
- Абсолютные ссылки (пример, $A$1);
- Смешанные ссылки (пример, $A1 или A$1, они наполовину относительные, наполовину абсолютные).
Относительные ссылки
Относительная ссылка «запоминает», на каком расстоянии (в строках и столбцах) вы щелкнули ОТНОСИТЕЛЬНО положения ячейки, где поставили " /img/image-loader.svg" alt="ok" data-src="https://habrastorage.org/getpro/habr/post_images/419/55a/02d/41955a02d1e46ecf4854c5cd1711bb29.jpg"/>
Рисунок 4
Упростим пример, применив знак $ (Рисунок 5).
Рисунок 5
Но не всегда нужно закреплять все столбцы и строки, иногда используется закрепление только строки или только столбца.(Рисунок 6)Ошибки формул
Если вы неправильно напишете формулу, об этом вас известит комментарий о синтаксической ошибке в формуле (Рисунок 7).
Рисунок 7
Хотя ошибки могут быть не только синтаксические, но и, например, математические, такие как деление на 0 (Рисунок 7) и другие (Рисунок 7.1, 7.2, 7.3). Для того чтобы увидеть примечание, в котором показана какая ошибка произошла, наведите курсор на красный треугольник в правом верхнем углу ошибки.
Рисунок 7.1
Рисунок 7.2
Рисунок 7.3
Для удобства восприятия таблицы все ячейки с формулами будем окрашивать в фиолетовый цвет.
Для того чтобы увидеть формулы «в живую» необходимо нажать горячую клавишу Ctrl + или выбрать в меню сверху Вид (Просмотр) > Все формулы. (Рисунок 8).
Рисунок 8О том, как пишутся формулы
В формулировке формул в справочнике и в формулах, которые используются для работы на данный момент, присутствуют отличия. Они заключаются в том, что вместо «запятой», которая использовалась раньше во многих формулах, уже используется «точка с запятой» (изменения произошли более полугода назад).
Для того чтобы посмотреть, на что ссылается формула на данной странице (Рисунок 9), необходимо щелкнуть мышкой в строке формул справа от надписи Fx (Fx находится под основным меню, слева).
Рисунок 9
ВАЖНО: Для правильного функционирования формул, они должны быть написаны ЛАТИНСКИМИ буквами. Русская (кириллическая) “А” или “С” и латинская “А” или “С” для формулы — это 2 разные буквы.Формулы
Арифметические формулы.
Расписывать, конечно, вечные операции сложения, вычитания и т.д., никто не будет, но они помогут понять сами азы. На нескольких примерах вы поймете, как они работают в этой среде. В документе, ссылка на который дана в конце статьи, приведены все формулы, мы же просто остановимся на скриншотах.
Сложение, вычитание, умножение, деление.
- Описание: формулы сложения, вычитания, умножения и деления.
- Вид формулы: “Ячейка_1+Ячейка_2”, “Ячейка_1-Ячейка_2”, “Ячейка_1*Ячейка_2”, “Ячейка_1/Ячейка_2”
- Сама формула: =E22+F22, =E23-F23, =E24*F24, =E25/F25.
Рисунок 10Прогрессия.
- Описание: формула для увеличения всех последующих ячеек на единицу (нумерация строк и столбцов).
- Вид формулы: =Предыдущая ячейка + 1.
- Сама формула: =D26+1
Рисунок 11Округление.
- Описание: формула для округления числа в ячейке.
- Вид формулы: =ROUND(ячейка с числом); счетчик (сколько цифр надо округлить после запятой).
- Сама формула: =ROUND(E28;2).
Рисунок 12
Округление “ROUND” происходит по математическим законам, если после запятой стоит цифра 5 или больше, то целая часть увеличивается на единицу, если 4 и меньше, то остается неизменной, также округление можно сделать с помощью меню ФОРМАТ — > Числа -> «1000,12» 2 десятичных знака (Рисунок 13). Если же вам необходимо большее количество знаков, то нужно нажать ФОРМАТ — > Числа -> Персонализированные десятичные -> И указать количество знаков.
Рисунок 13Сумма, если ячейки идут не последовательно.
- Описание: суммирование чисел, которые находятся в разных ячейках.
- Вид формулы: =SUM(число_1; число_2;… число_30).
- Сама формула: "=SUM(E30;H30)" пишем через ";" если разные ячейки.
- Описание: суммирование чисел, которые идут друг за другом (последовательно).
- Вид формулы: =SUM(число_1: число_N).
- Сама формула: =SUM (E31:H31)" пишем через ":" если это непрерывный диапазон.
- Имеем начальные данные в диапазоне ячеек E31:H31, а результат в ячейке D31 (Рисунок15).
Среднее арифметическое.
- Описание: суммируется диапазон чисел и делится на количество ячеек в диапазоне.
- Вид формулы: =AVERAGE (ячейка с числом либо число_1; ячейка с числом либо число_2;… ячейка с числом либо число_30).
- Сама формула: =AVERAGE(E32:H32)
Рисунок 16
Конечно, есть и другие, но мы идем дальше.Текстовые формулы.
Из великого количества текстовых формул, с помощью которых можно сделать все, что угодно с текстом, самая востребованная, на мой взгляд — это формула для «склеивания» текстовых значений. Существует несколько вариантов ее исполнения:
Склеивание текстовых значений (формулой).
- Описание: «склеивание» текстовых значений (вариант А).
- Вид формулы: =CONCATENATE(ячейка с числом/текстом либо текст_1; ячейка с числом/текстом либо текст_2; …, ячейка с числом/текстом либо текст_30).
- Сама формула: =CONCATENATE(E36;F36;G36;H36).
Рисунок 17Склеивание числовых значений.
- Описание: “склеивание” текстовых значений руками, без использования специальных функций (вариант B — ручное написание формулы, сложность формулы любая.).
- Вид формулы: =ячейка с числом/текстом 1&" "&ячейка с числом/текстом 2&" "&ячейка с числом/текстом 3&" "& ячейка с числом/текстом 4 (" " — пробел, знак & означает склеивание, все текстовые значения пишутся в кавычках “”).
- Сама формула: =E37&" "&F37&" "&G37&" "&H37.
Рисунок 18Склеивание числовых и текстовых значений.
- Описание:«склеивание» текстовых значений руками, без использования специальных функций (вариант С — смешанный тип, сложность формулы любая).
- Вид формулы: = «текст_1 » &ячейка_1&«текст_2»&ячейка_2&«текст_3»&ячейка_3
- Важно: весь текст, который будет написан в “” будет неизменным для формулы.
- Сама формула: =«Еще 1 » &E38&" использования "&F38&" как НАМ "&G38.
Рисунок 19ЛОГИЧЕСКИЕ И ПРОЧИЕ
Перенос данных из любых листов одного и того же файла.
- Описание: перенос данных из любых листов одного и того же файла (для Excel можно как переносить из листа одной книги в другой лист той же книги, так и из листа одной книги в лист другой книги).
- Вид формулы: = «Название_Листа»! ячейка_1
- Сама формула:=Data!A15 (Data — лист, А15 — ячейка на том листе).
Рисунок 20
Рисунок 20.1Массив формул.
Большинство программ для работы с таблицами содержат два типа формул массива: «для нескольких ячеек» и «для одной ячейки».
Таблицы Google разделяют эти типы на две функции: CONTINUE (ПРОДОЛЖИТЬ) и ARRAYFORMULA.
Формулы массива для нескольких ячеек позволяют формуле возвращать несколько значений. Вы можете использовать их, даже не зная этого, просто вводя формулу, возвращающую несколько значений.
Формулы массива «в одной ячейке» позволяют записывать формулы с помощью ввода массива, а не выходных данных. При заключении формулы в состав функции =ARRAYFORMULA можно передать массивы или диапазоны функциям и операторам, которые, как правило, используют только аргументы, не принадлежащие массивам. Данные функции и операторы будут применяться по одному для каждой записи в массиве, и возвращать новый массив со всеми выходными данными.
Если вы хотите изучить вопрос более детально, вам следует посетить support.google.
Говоря простыми словами, для работы с формулами, которые возвращают массивы данных, во избежание синтаксических ошибок, необходимо заключать их в массив формул.
Суммирование ячеек с условием ЕСЛИ.
- Описание: суммирование ячеек с условием ЕСЛИ (формула SUMIF).
- Вид формулы: = SUMIF(‘Лист’! диапазон; критерии; ‘Лист’! суммарный_диапазон)
Рисунок 21
Имеем начальные данные в листе Data (Рисунок 21), а результат на листе Formula в столбце D (Рисунок 22). В столбцах E, F, G показаны аргументы, применяемые в формуле, а в столбце H общий вид формулы, которая находится в столбце D и высчитывает результат.
Рисунок 22
Пример выше показывает общий вид работы формулы “Сумма Если” с одним условием, но чаще всего используется “Сумма ЕСЛИ” (с множеством условий).Суммирование ячеек ЕСЛИ, множество условий.
- Описание: сумма ЕСЛИ (с множеством условий).
- Вид формулы: = SUMIF(‘Data’! диапазон_1&‘Data’! диапазон_2; критерии_1&критерий_2; ‘Data’! суммарный_диапазон).
- Сама формула:=(ARRAYFORMULA(SUMIF((Data!E:E&Data!F:F);(B53&C53);Data!G:G)))
Рисунок 23
Допустим, что на листе Formula, в ячейке В53 (критерий_1 = Пиво) должно быть название напитка, а ячейка С53 (критерий_2 = 2), это количество друзей, которые принесут Пиво. В итоге в ячейке D53 окажется результат, что нам нужно докупить 15 бутылок пива. (Рисунок 23.1) то есть, формула определит сумму по двум критериям — пиво и количество друзей.
Рисунок 23.1
Если таких позиций будет больше, строки 16 и 21(Рисунок 24), то количество пузырей в колонке G суммируется (Рисунок 24.1).
Рисунок 24
Итого:
Рисунок 24.1Теперь приведем более интересный пример:
Ха… вечеринка продолжается, и вы вспоминаете, что нужен торт, но непростой, а супер – мега торт, с разными специями, которые, как назло, еще и зашифрованы под цифровые обозначения. Задача состоит в том, чтобы купить специи в нужном количестве пакетиков каждой из специи. Нужное количество повар зашифровал в таблицу (Рисунок. 25.1), столбцы A и B (в соседних столбцах делаем наши вычисления).
Каждая специя имеет свой порядковый номер: 1,2,3,4. (Рисунок 25).- Описание: подсчет количества одинаковых цифр в больших массивах при дополнительных условиях.
- Вид формулы: СЧИТАТЬ ЕСЛИ(‘Formula’! диапазон_A55: А61+’Formula’! диапазон_B55:B61; УсловиеА”Специи”+УсловиеБ”число от 1 до 4”; Лист”Formula’! диапазон_B55:B61)/УсловиеБ ”число от 1 до 4”)
- Сама формула: =((ARRAYFORMULA(SUMIF('Formula'!$A$55:$A$61&'Formula'!$B$55:$B$61; $F$55&$E59;'Formula'!$B$55:$B$61)))/$E59)
- Описание: вычисление процента специй.
- Вид формулы: Количество*100%/Общее_количество
- Сама формула: =F58*$G$56/F$56
Подсчет значений в объединенных ячеек.
- Описание: формула для подсчета значений, в которых присутствует символ @.
- Вид формулы: СЧИТАТЬ ЕСЛИ(В столбце F листа “Formula” есть текст с содержимым @).
- Сама формула: =COUNTIF('Formula'!F65:F68; "*@*").
Подсчитывает количество чисел в списке аргументов.
- Описание: подсчет количества ячеек, содержащих цифры без текстовых переменных.
- Вид формулы: COUNT(значение_1; значение_2; … значение_30)
- Сама формула: =COUNT(E45;F45;G45;H45)
Рисунок 27.
Ячейки, содержащие текст и цифры также не считаются.
Рисунок 27.1.Подсчет количества ячеек содержащих цифры с текстовыми переменными.
- Описание: подсчет количества ячеек, содержащих цифры с текстовыми переменными.
- Вид формулы: COUNTA(значение_1; значение_2; … значение_30)
- Сама формула: =COUNTA(E46:H46)
Рисунок 28.
Также, формула считает ячейки, содержащие только знаки препинания, табуляции, но не считает пустые ячейки.
Рисунок 28.1Подстановка значений при условиях.
- Описание: подстановка значений при условиях.
- Вид формулы: "=IF(AND((Условие1);(Условие2)); Результат равен 0, если условие 1 и 2 выполняется; если не выполняется, то результат равен 1)"
- Сама формула: "=IF(AND((F73=5);(H73=5));0;1)"
- Вид формулы:"=COUNTA(Диапазон_А)-COUNTIF(Диапазон_А; «автоответ»)-COUNTIF(Диапазон_А; "-")-COUNTIF(Диапазон_А; «занято»)"
- Сама формула: =COUNTA($E74:$H75)-COUNTIF($E74:$H75; «автоответ»)-COUNTIF($E74:$H75; "-")-COUNTIF($E74:$H75; «занято»)
Рисунок 30
Вот мы и подошли к концу нашего маленького ликбеза по формулам в Google SpreadSheet и у меня большие надежды, что я пролил свет на некоторые аспекты аналитической работы с формулами.
Формулы, честно говоря, были в прямом смысле выстраданы. Каждая из них создавалась в течение долгого времени. Надеюсь, вам понравилась моя статья и примеры, приведенные в ней.
И в завершение, в качестве подарка. И да простят меня разработчики!Формула «УБИЙЦА ДОКУМЕНТА».
Если Вам необходимо скрыть документ от чужих глаз навсегда, то эта формула для Вас.
Сама формула:"=(ARRAYFORMULA(SUMIF($A:$A&$C:$C;$H:$H&F$2; $C:$C)))". $H:$H регулирует распространение формулы. После того как фомлулу запустите (Рисунок 31), ниже в ячейках она начнет размножать следующую функцию CONTINUE(ячейка; строка; столбец).Читайте также: