Category: GoogleSpreadSheet

Counting the number of days in a month by combining the DAY and EOMONTH functions (spreadsheet)
To count the days of a month using a combination of functions DAYin Google Sheets , follow these steps:EOMONTHtable of contents Using the EOMONTH function Using the EOMONTH function : EOMONTHThe function returns the date of the last day of a particular month. This function takes as arguments a specific date (e.g. “20240201”) and a number to add or…

[FILTER function] How to perform a “fuzzy search” in combination with the FIND function (Google Spreadsheet)
This is a method for fuzzy search (extraction) with the FILTER function. What is the FILTER function? You can use the FILTER function to extract the result of a specific condition from within a range. By using the FILTER function of Google Sheets, you can extract the result of the specified value from the table.…

Google Spreadsheet Compound Interest Calculation｜How to perform compound interest calculations (simple version)
In this article, we will introduce compound interest and compound interest calculations. What is compound interest? Compound interest (or compound interest) refers to interest calculated using the compound interest method, whereby interest generated by the principal is incorporated into the principal in the next period and interest is also earned on the interest. The gradual…

Google Spreadsheet What is the maximum number of rows? Verification of the maximum number of rows
Maximum number of rows in a Google Spreadsheet workbook How far can the maximum number of rows in a Google Spreadsheet workbook go? I have verified the following. So, let’s try to add 1,000,000 rows as a trial. Yes, … suddenly we have the answer: it seems to be that 5000000 cells is the upper…

Google Spreadsheet How to display sheet names by cell reference [Can also be used in Excel] INDIRECT function
You may often refer to data from other sheets in Google Spreadsheet or Excel. In such cases, here is how to reference the sheet name from the cell value. Normal other sheet references If you want to refer to data on another sheet, you can use input methods such as “‘sheet name’! cell number” or…

[Google Spreadsheet] Create parade charts and ABC analysis graphs from tables  Priority analysis
What is ABC Analysis? “ABC analysis” is the idea that “the top 20% of sales account for 80% of the total”, and is used when formulating strategies based on graphs. This ABC analysis, also known as ” priority analysis “, is a method of analyzing the financial importance of raw materials, products (merchandise), etc. in inventory management.…

[Google Spreadsheet] QUERY function  Query list/Summary
List of APIs that can be used with the QUERY function So far, we have practiced how to use and apply the QUERY function. The QUERY function uses the query language of the Google Visualization API to query the entire data. This time, I will summarize the “Query language of the Google Visualization API” as a…

[Google Spreadsheet] QUERY function  Query list Part 2 (WHERE clause)
You can extract data from a range with the QUERY function The QUERY function “uses the query language of the Google Visualization API to query the entire data”. I’ve introduced it before, but I would like to try it out by actually using it using a query. This time , I would like to extract data…

[Google Spreadsheet] QUERY function  Query list Part 1 (SELECT clause)￼￼
You can extract data from a range with the QUERY function The QUERY function “uses the query language of the Google Visualization API to query the entire data”. I’ve introduced it before, but I would like to try using other queries and actually using them. This time , I would like to extract data from the Tokyo…

CONCAT function”, “CONCATENATE function”, “TEXTJOIN function” functions to combine strings [Spreadsheet].
When you want to join cell data of columns and rows in Google Spreadsheet, you can use “& (Ambasand)” to join them, but this function can be used when you want to join multiple columns together. CONCAT Function The CONCAT function can be used to combine multiple ranges or strings of text. However, with the…

[Spreadsheet] How to dynamically get the last row of a column with data entered
This is a method that can be used when you want to know the last row where data is entered. A simple way is the COUNTA function A simple way is to use the COUNTA function. COUNTA – Google Docs Editors HelpReturns the number of values in the dataset. Usage example COUNTA(A2:A100,B2:B100,4,26) COUNTA(1,2,3,4,5,C6:C20) Syntax COUNTA(value1, )…

[DATEVALUE function] Convenient function to extract serial number from date/time data
You can use the DATEVALUE function to extract the serial number from date/time data. What is the DATEVALUE function? A function that can extract the serial number from date/time data. Example of use: Using the DATEVALUE function For example, you can use it in the following situations. I want to know the serial number from…

What is the AND function? Function to check if all conditions are TRUE / Google Sheets
What is the AND function? The AND function is a function that checks whether all conditions are TRUE . How to use the AND function AND(logical_expression1, [logical_expression2, …]) Description Returns TRUE if all arguments are logically TRUE. Returns FALSE if any argument is logically FALSE. Logical Expression1A formula or cell reference that contains a formula that represents a logical value…

Google Spreadsheet How to calculate total operating hours (working hours) from the displayed time (hh:mm)
This method calculates work hours from the displayed time (hh:mm). Time clocked out – Time clocked in So, let’s subtract the time of leaving work – time of coming to work. I got a result that looks like that. Let’s continue. What is the total operating hours (working hours)…? If we add up the working…

Google Spreadsheet Create parade charts and ABC analysis graphs from tables｜Priority Analysis
What is ABC analysis? ABC analysis is based on the concept that the top 20% of sales account for 80% of total sales, and is used to create strategies based on graphs. This ABC analysis, also known as “priority analysis,” is a method of analyzing the financial importance of raw materials, products (goods), etc. in…

[Google Spreadsheet] A convenient function to extract and display titles from the article list  IMPORTXML function
When you want to get all the page titles from the list of URLs at once, you can use the convenient function “IMPORTXML function” unique to Google Sheets. What is the IMPORTXML function? =IMPORTXML(“URL”, “XPath query”) A function that can extract specific data from a website page using specific conditions (XPath query). It is commonly called ” scraping “. *Scraping is prohibited on…

[Google Spreadsheet] I want to count down the number of days left from a specified date! ｜DAYS function & NOW function
If you use Google Spreadsheet (commonly known as Google Sheet Spreadsheet), how many more days will it take to reach the specified date…? You can easily count down! DAYS function The DAYS function returns the number of days between two dates. Arguments of the DAYS function DAYS(終了日,開始日) [rml_read_more] argument explanation 終了日 Specifies the end of the…

[Google Sheets] List of functions related to dates
We have compiled a list of functions related to “date” in Google Sheets! NETWORKDAYS.INTL =NETWORKDAYS.INTL(start date, end date, [weekend], [holiday]) Returns the net number of workdays between two specified dates , excluding certain weekends and holidays . Only available in the new Google Sheets. function =NETWORKDAYS.INTL(B3,B4) result twenty three value 1 2019/01/01 value 2 2019/01/31 DATE =DATE(year, month,…

[Google Sheets] How to use the Importrange function! Useful import functions
import range function It is useful when you want to pull out and display data from another Google Spreadsheet in Google Spreadsheet ! This is the “Importrange function”. How to use the importrange function =Importrange(“sheet key”,”sheet name!range”) spreadsheet key – Specify the URL of the spreadsheet from which to load data. spreadsheet key The value of must be enclosed…

[Introduction to Google Sheets] What is Google Sheets?
What is Google Sheets Google Sheets is “an online spreadsheet app that allows you to create, format, and collaborate with others at the same time on your spreadsheets.” It is a tool developed by Google, and anyone with a Google account can use it for free. The Biggest Benefits of Google Sheets The biggest advantage…